
Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans - mgartner
https://github.com/mgartner/pg_flame
======
londons_explore
I don't like the way postgres doesn't have the ability to switch query plans
mid query.

Frequently a query plan turns out to perform nowhere near as well as the
planner expects (for example, because the data distribution is poor, or a key
being filtered for doesn't exist). In those cases, flipping the query plan
around could turn a 1 hour query into a 1 millisecond query. Yet postgres
doesn't have the ability to do that.

Sure a human can sometimes use domain knowledge to sometimes be able to
rearrange the query to force a plan that works well, but in the general case,
the database shouldn't have performance drop by a factor of 1 million because
of semi-arbitrary planner decisions. Being able to try multiple possible plans
would be a massive start in solving the issue.

~~~
londons_explore
For anyone interested in implementing this, here is what I found last time...

Postgres queries are _streamed_ to the client - ie. some results are delivered
before the query is done running. That functionality is necessary for really
big resultsets.

That makes it difficult to change plans mid query, because your new plan might
return results in a different order, and you need to filter any already-
returned results, but you can't afford to keep all of the already-returned
results in RAM. Even if that weren't an issue, I'm not even sure that it's
always valid to do this.

To add even more complexity... The postgres protocol allows the client to
_reverse_ the query (ie. midway through getting the results, the client can
say "yo, go back, and return results from earlier again"). It must return the
same results in reverse order. That means if you do switch query plans, when
the client goes backwards, the server needs to un-switch query plans back to
the old plan when going backwards.

These issues are not insurmountable... But they certainly stopped me
implementing it in the day I had set aside for the task...

~~~
btown
I would love to be able to SET multiple_planning; on a cursor to be able to
say “yes I know that reversing would be UB, and I have set a limit so I don’t
care if results aren’t streamed, so please just try everything you can.” In
general I wish there were better ways to tell these systems you know what
you’re doing.

~~~
cormacrelf
There could also be a “clean the slate” streaming directive, which means “I
started the query again, please scratch what got delivered already”.

~~~
JelteF
That effictively means the client cannot actually start processing the results
before everything is received. Because you would have to undo everything you
did with the data you received before. So basically this would simply be a non
streaming version.

~~~
Bootvis
I believe the best you can do is to delay streaming. Now you have the problem
of deciding when to start...

------
felixge
A tool like would be a wonderful addition to the PostgreSQL ecosystem!

That being said, this tool won't work well for anything but trivial queries.
PostgreSQL query plans have many quirks around CTEs, Loops, etc. that cause
problems when trying to determine the true inclusive/exclusive time for each
node without forgetting stuff or counting it twice. The only tool that tackles
them fairly in my experiences it the good old
[https://explain.depesz.com/](https://explain.depesz.com/) using it's own Pg--
Explain library [1].

I'm currently working on my own version of a tool like the one presented by OP
(called FlameExplain), and hope to release it soon.

[1] [https://gitlab.com/depesz/Pg--
Explain/blob/master/lib/Pg/Exp...](https://gitlab.com/depesz/Pg--
Explain/blob/master/lib/Pg/Explain/Node.pm#L541-557)

~~~
mgartner
Ya, I ran into problems with CTE InitPlan steps. However, I did do some extra
work to have them display in the most correct way I could think of.

I’ll add a CTE demo with and explanation.

------
bflesch
I love this tool! But generally speaking, the user experience of the
PostgreSQL admin workflow could be improved so much.

The psql client binary should include visualizations like this even in text
mode, so admins don't have to follow a multitude of steps as described in the
repo:

1) run query, store results in .json file

2) scp .json file to your dev machine

3) run visualization tool

If I already have a psql client shell open, why can't this all be done in the
background? I think there is still a lot of potential for improvement.

~~~
miohtama
Could the workflow done so that you just copy-paste output from a PSQL shell
to an online tool?

~~~
ken
Had this been implemented as a web service, half of the crowd would be asking
for it to be an open-source program, so they could run it locally, for speed
and flexibility and security. It _was_ implemented as an open-source program,
so half of the crowd are asking for it to be a web service for usability and
convenience and aesthetics.

What I'm seeing is we've got two big platforms (web, CLI) and they both have
some distinct advantages and some distinct disadvantages, and it's not easy
for either one to cross the chasm and compete with the other one directly. We
desperately need a new platform which combines what we like about both of
these, and discards what we don't.

Until then, we're just going to keep implementing all end-user functionality
twice, because by historical accident developers do most of their work in a
DEC VT100 emulator, and end-users won't tolerate that.

~~~
Xylakant
> It was implemented as an open-source program, so half of the crowd are
> asking for it to be a web service for usability and convenience and
> aesthetics

I’ll prefer that any time of day. At least, this way someone can easily turn
it into a web service. It generates HTML afaics.

~~~
londons_explore
A good number of github projects I find say "Install with npm install foo, or
try it out online [here]"

The online version can host the exact same code from git master. For many
projects, no hosting is even required, because the whole thing can run in a
codepen-like playground.

------
tanelpoder
Nice.

FYI, I once published a similar tool for Oracle, including an explanation of
how to read FlameGraphs in SQL execution context.

[https://blog.tanelpoder.com/posts/visualizing-sql-plan-
execu...](https://blog.tanelpoder.com/posts/visualizing-sql-plan-execution-
time-with-flamegraphs/)

~~~
mgartner
Cool stuff! I've linked to your blog in the pg_flame README.

~~~
tanelpoder
Cool, thanks!

------
redis_mlc
MySQL (regular and NDB Cluster versions) has "show profile" built-in. It's not
graphical, but has similar details. Love it!

[https://dev.mysql.com/doc/refman/5.6/en/show-
profile.html](https://dev.mysql.com/doc/refman/5.6/en/show-profile.html)

------
rehemiau
A really great feature that the Oracle database has is ability to provide
hints to the planner. Is there anything that prevents PostgreSQL from adding
this feature?

edit: Googled it, looks like PostgreSQL maintainers just have an opinion that
hints are bad and the planner is good enough:

[https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion](https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion)

There are "Explicit JOINs" but I'm not sure how useful they are:

[https://www.postgresql.org/docs/current/explicit-
joins.html](https://www.postgresql.org/docs/current/explicit-joins.html)

~~~
ollysb
While it's easy to nod along with their justification for no hints the
practical reality is that if the query planner gets it wrong you can have
query time an order of magnitude slower. This is particular obvious when using
gin indexes with `like`.

On the application I'm currently working on the difference between the two
indexes is night and day, the gin index will respond in 100ms, whilst the
btree index can be 15secs+. We've resorted to having two columns with the same
content, one with a btree index and one with a gin index so that we can
explicitly choose which index to hit.

------
drej
I ported this to JavaScript (apart from the InitPlan shenanigans), so that it
doesn't require the server-side component - it runs directly in the browser.

[https://github.com/kokes/pg_flame.js](https://github.com/kokes/pg_flame.js)

------
nerder92
Wow, i'm struggling with a sql query in psql just right now, this seems to be
just what i was looking for! Thank you so much!

------
ranadeep
Awesome job. This is really useful.

------
nvr219
I thought this was going to be a tool that automatically has Paul graham flame
you lol

