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

11–20 of 46 posts

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

#11

Post by londons_explore »

Xylakant wrote: post #8
ken wrote: post #7

Earlier quoted context omitted.

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

> 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.

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.

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

#12

Post by londons_explore »

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…

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...

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

#14

Post by mgartner »

ken wrote: post #7
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?

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

I built it as a CLI because of the reasons you mention, but also because I find that workflow most convenient for me and I didn’t want to need an internet connection.

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

#15

Post by aeyes »

miohtama wrote: post #4
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…

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.

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

#16

Post by btown »

londons_explore wrote: post #12
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…

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.

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

#18

Post by rc_mob »

bflesch wrote: post #5
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?

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

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

#19

Post by CodeWriter23 »

mgartner wrote: post #6
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…

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.

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

#20

Post by dosy »

A proliferation of performance assessment tools could be an indication of relatively poor performance.

I guess you can say the more performant systems have performance built in, you don't have to engineer and tweak it as an add-on, because they already are.

Post reply on HN