Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

github.com

Post reply on HN
Print view View original

21–30 of 46 posts

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#21

Post by takeda »

rc_mob wrote: post #18
bflesch wrote: post #5

Earlier quoted context omitted.

Yes, some tools such as the query analyzer by depesz [1] work by copy-pasting the EXPALIN ANALYZE output into your browser. But IMHO the overall friction is still too large for such a central use case as query optimization done by a database admin. The PostgreSQL team has been innovating and improving steadily, so I am confident these kind of workflows will be heavily optimized within the next couple of years. [1] ht…

Mighty god odin bless you for that link. Thank u so much

that tool is a must if you plan improve, depesz (who is here on HN) also has good blog about postgres, he describes new upcoming features.

He also wrote a blog series about understanding explain plans: https://www.depesz.com/tag/unexplainable/ his explain tool + that information is really good starting point when trying to optimize queries.

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#22

Post by cormacrelf »

btown wrote: post #16
londons_explore wrote: post #12

Earlier quoted context omitted.

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…

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.

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

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#23

Post by davidjnelson »

CodeWriter23 wrote: post #19
mgartner wrote: post #6

Earlier quoted context omitted.

If you run the psql client from your dev machine, there’s no need for step 2. Also, in practice I don’t save the output JSON to a file, I just pipe it directly to pg_flame. The README breaks it up into multiple steps, but maybe I could make it clear that it’s not necessary. But in general I do agree that simplifying this type of tooling is a good thing and something to strive for.

Impossible with Aurora Serverless. Only VPC IPs can connect.

Would a bastion host help here?

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#24

Post by ako »

londons_explore wrote: post #10

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 t…

The decisions a database makes aren't semi-arbitrarily: they are completely based on your statistics. Usually wrong decisions are caused by outdated statistics.

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#25

Post by 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/ 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...

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#26

Post by hans_castorp »

bflesch wrote: post #2

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…

I never understood the obsession with the graphical display of execution plans (neither in Postgres nor in Oracle).

I find the text output much more useful and detailed then any graphical display. Especially when generated with "explain (analyze, buffers, timing) ..." and "track_io_timing" turned on.

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#27

Post by londons_explore »

ako wrote: post #24
londons_explore wrote: post #10

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 t…

The decisions a database makes aren't semi-arbitrarily: they are completely based on your statistics. Usually wrong decisions are caused by outdated statistics.

The statistics cant accurately represent most data distributions, and errors stack multiplicativly with deep query plans. Typical query plans, even with fresh statistics, are lucky to be within a factor of 10, or even 100 of the true cost of running the query.

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#28

Post by ldng »

hans_castorp wrote: post #26
bflesch wrote: post #2

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…

I never understood the obsession with the graphical display of execution plans (neither in Postgres nor in Oracle). I find the text output much more useful and detailed then any graphical display. Especially when generated with "explain (analyze, buffers, timing) ..." and "track_io_timing" turned on.

Well different mind work differently. Some are more receptive to visuals. I don't understand why some people refuse to acknowledge that.

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#29

Post by dbetteridge »

davidjnelson wrote: post #23
CodeWriter23 wrote: post #19

Earlier quoted context omitted.

Impossible with Aurora Serverless. Only VPC IPs can connect.

Would a bastion host help here?

Yes, that's how we do things.

Ssh to 3333:dburl bastionhost -> psql to localhost:3333

Re: Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans

#30

Post by JelteF »

cormacrelf wrote: post #22
btown wrote: post #16

Earlier quoted context omitted.

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.

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

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.
Post reply on HN