Show HN: pg_flame – flamegraph visualizations of PostgreSQL query plans • Hacker News Board

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

#31

Post by Bootvis » Mon, Oct 28, 2019, 9:13 AM UTC

JelteF wrote: post #30

cormacrelf wrote: post #22

Earlier quoted context omitted.

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.

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

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

#32

Post by rehemiau » Mon, Oct 28, 2019, 9:27 AM UTC

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

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

https://www.postgresql.org/docs/current/explicit-joins.html

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

#33

Post by [deleted] » Mon, Oct 28, 2019, 9:39 AM UTC

rehemiau wrote: post #32

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 There are "Explicit JOINs" but I'm not sure how useful they are: https://…

[deleted]

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

#34

Post by ollysb » Mon, Oct 28, 2019, 9:58 AM UTC

rehemiau wrote: post #32

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 There are "Explicit JOINs" but I'm not sure how useful they are: https://…

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.

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

#35

Post by goatinaboat » Mon, Oct 28, 2019, 10:59 AM UTC

rehemiau wrote: post #32

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 There are "Explicit JOINs" but I'm not sure how useful they are: https://…

I’m reminded of the MySQL developers in the 90s decrying foreign keys, transactions etc as unnecessary overheads, only to belatedly add them later. Hints are one thing I miss coming from Oracle - even being able to hint if you want the first row fastest or the entire result set.

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

#36

Post by nerder92 » Mon, Oct 28, 2019, 12:05 PM UTC

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!

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

#37

Post by daamien » Mon, Oct 28, 2019, 1:44 PM UTC

aeyes wrote: post #15

miohtama wrote: post #4

Earlier quoted context omitted.

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

This is the best tool I know, works pasting only the JSON output: http://tatiyants.com/postgres-query-plan-visualization/ The information density the rendering gives me is way better than the flamegraph.

See PEV2 : https://github.com/dalibo/pev2

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

#38

Post by ants_a » Mon, Oct 28, 2019, 2:52 PM UTC

rehemiau wrote: post #32

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 There are "Explicit JOINs" but I'm not sure how useful they are: https://…

There is one implementation of hints available as an extension: https://github.com/ossc-db/pg_hint_plan

It's unfortunate that it comes off as if maintainers think planner is good enough, because that is demonstrably not true. And from my discussions there is wide agreement that the planner will never be perfect. But agreeing that it's something that needs work is not enough, someone actually has to do the work too. Reality is that implementing any feature to PostgreSQL quality standards is hard work.

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

#39

Post by mgartner » Mon, Oct 28, 2019, 3:25 PM UTC

felixge wrote: post #25

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

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.

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

#40

Post by mgartner » Mon, Oct 28, 2019, 3:28 PM UTC

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.

I think the text display is great too! I built pg_flame mostly to help understand the relative timing of each step. Your brain can compare the size of each bar in a flamegraph virtually instantly, while comparing a bunch of actual time numbers scattered throughout the text output takes some time.

tldr; use both as needed