3 ms·

> This is powered by a modified version of the Postgres planner that runs as part of the pganalyze app. This modified planner can generate EXPLAIN-like data fro

> This is powered by a modified version of the Postgres planner that runs as part of the pganalyze app. This modified planner can generate EXPLAIN-like data from just a query and schema information - and most importantly, that means we can take query statistics data from pg_stat_statements and generate a generic query plan from it. You can read more about that in our blog post "How we deconstructed the Postgres planner". Having something like this available as a library inside of Postgres seems really beneficial for tool authors. I wonder what the odds of getting it upstreamed are?

lfittl 3y ago
Author here - what we call "pg_plan" internally, is essentially very similar in spirit to what we do with pg_query [0], but the difference is that we pull in a lot more code from Postgres, and have more overrides in places that are not needed for the use case (e.g. MVCC handling, etc). My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim. Mainly from there being a lot of entanglement with reading files on disk, memory management, etc - I suspect one of the pushbacks would be it would complicate development work for Postgres itself, to the point its not worth the benefits. For the pg_query library on the other hand I have hopes that we can upstream this eventually - there are enough third-party users out there to clearly show the need, and its much more contained (i.e. raw parser + AST structs). Hopefully something we can spend a bit of time on next year. [0]: https://github.com/pganalyze/libpg_query https://github.com/pganalyze/libpg_query
- anarazel 3y ago
  > My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim. Yea. The parser alone would be doable and not even that hard. But once you get to parse analysis and planning, you need to access the catalogs (for parse analysis to look up object names and do permission checks, for planning to access operator definitions, statistics etc). Which in turn needs a lot of the catalog / relation cache infrastructure. By that point you've pulled in a lot of postgres. Of course you could try to introduce a "data provider" layer between parse analysis and catalogs, but that'd be a lot of work. And it'd be quite hard to get right in places - e.g. doing name lookups without acquiring heavyweight locks on objects, before having done permission checks, in a concurrency safe way, relies on a bunch of subsystems working together.

lfittl 3y ago

Author here - what we call "pg_plan" internally, is essentially very similar in spirit to what we do with pg_query [0], but the difference is that we pull in a lot more code from Postgres, and have more overrides in places that are not needed for the use case (e.g. MVCC handling, etc). My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim. Mainly from there being a lot of entanglement with reading files on disk, memory management, etc - I suspect one of the pushbacks would be it would complicate development work for Postgres itself, to the point its not worth the benefits. For the pg_query library on the other hand I have hopes that we can upstream this eventually - there are enough third-party users out there to clearly show the need, and its much more contained (i.e. raw parser + AST structs). Hopefully something we can spend a bit of time on next year. [0]: https://github.com/pganalyze/libpg_query https://github.com/pganalyze/libpg_query

anarazel 3y ago
> My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim. Yea. The parser alone would be doable and not even that hard. But once you get to parse analysis and planning, you need to access the catalogs (for parse analysis to look up object names and do permission checks, for planning to access operator definitions, statistics etc). Which in turn needs a lot of the catalog / relation cache infrastructure. By that point you've pulled in a lot of postgres. Of course you could try to introduce a "data provider" layer between parse analysis and catalogs, but that'd be a lot of work. And it'd be quite hard to get right in places - e.g. doing name lookups without acquiring heavyweight locks on objects, before having done permission checks, in a concurrency safe way, relies on a bunch of subsystems working together.