9 ms·
Pg_hint_plan: Force PostgreSQL to execute query plans the way you want
- justinclift 3y agoThis looks really useful. Came across it due to a comment in another submission: https://news.ycombinator.com/item?id=39712127 https://news.ycombinator.com/item?id=39712127
- __s 3y agoBack when I worked on Citus I saw enough capability for this, & this seemed like the right way to settle demand for hints Glad to see it executed
- IvanVergiliev 3y agoUsing the hint table has been pretty painful, in my experience. Two main difficulties I’ve seen: 1. The hint patterns are whitespace-sensitive. Accidentally put a tab instead of a couple of spaces, and you get a silent match failure. 2. There are different ways to encode query parameters - `?` for literals in the query text, `$n` for query parameters. Psql / pgcli don’t support parameterized queries so you can’t use them to iterate on your hints. Still super useful when you have no other options though.
- IsTom 3y ago> Psql / pgcli don’t support parameterized queries so you can’t use them to iterate on your hints. Isn't this what PREPARE and EXECUTE does?
- feike 3y agoYou should be able nowadays with PG16 INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-BIND https://www.postgresql.org/docs/current/app-psql.html#APP-PS... For older versions, you can do: \set v_x 'first value' \set v_y 'second value' INSERT INTO tbl1 VALUES (:'v_x', :'v_y'); \set v_x 'next value' INSERT INTO tbl1 VALUES (:'v_x', :'v_y');
- th3iedkid 3y agoIsn’t hinting more like asking nicely than forcing the engine to choose the other way?
- pm90 3y agoyou can’t really do the latter.
- FritsHoogland 3y agoIt's a bit of both. What pg_hint_plan does is change the cost to favour the hinted suggestion. If hinted suggestion is impossible, the planner will still perform something else.
- FritsHoogland 3y agoBut if 'force' means changing what it would do otherwise, where 'otherwise' is: a different plan having the lowest cost, the: yes that is exactly what it does.
- justinclift 3y agoYeah, I probably could have used better wording for the title submission. The GitHub repo's wording is broken english currently though which is (probably?) worse.
- ioltas 3y agoI’ll try to do something about that tomorrow, saw your ticket.
- justinclift 3y agoNo worries at all. :)
- dgan 3y agoIs there a way to bypass SQL completely, and somehow give the Postgres the plan to execute? So targeting their intermediate language (i presume, it is not stable?) instead of the front one
- SigmundA 3y agoMSSQL lets you save a query plan as XML then upload to another system as a hint. PG could do this using the json or xml output of EXPLAIN.
- justinclift 3y agoThat's an interesting idea. Might be a good ability for someone to add. :)
- alex-korr 3y agoOracle had it for decades - ie outlines.
- U1F984 3y agoNot sure why this is down voted, I also looked into this at some point but was unable to find a solution.
- __s 3y agoYou could do it as an extension, but otherwise no
- ioltas 3y agoThere are hooks for the planner and the executor, so you could force their hand quite easily with a plan extracted from a previous EXPLAIN. Being able to pass a plan at protocol level would be more efficient, for sure. I’ve worked on Postgres-XC and somewhat Postgres-XL, and that’s something that we wanted to do to generate a plan on one node, and push down the compiled plan down to other nodes to avoid the overhead of the plan computation. This stuff did that for snapshots and a few more things with an extension of the protocol.
- ioltas 3y agoI’ve been doing maintenance and bug fixes for this module for over 18 months now (last commit on HEAD seems to be mine), managing the last two releases. If you have questions and/or feedback, feel free.
- abhishekjha 3y agoI am digging into postgres source code past few weeks. This project seems like a good way to see how plugins integrate. I may reach out later if I have questions
- ioltas 3y agoThere are many Postgres internals that people are usually not aware of, with more than one way to develop your module. I have a repo that’s a set of plugin templates here, that should be handy for your studies: https://github.com/michaelpq/pg_plugins https://github.com/michaelpq/pg_plugins
- RMarcus 3y agoJust wanted to say thank you! This extension was critical to a bunch of my research (and now my lab's research as well). Being able to control fine-grained elements of each plan while letting the PG planner "do the rest" has saved me personally probably 100s of hours of work.
- ioltas 3y agoThanks, glad to hear some good feedback.
- adamzochowski 3y agoAren't there two arguments for why this is bad? - db will generate new plans as necessary when row counts and values change. Putting in hints makes the plan rigid likely leading to headaches down the line. - as new Postgres comes out, it's planner will do a better job. Again forcing specific plan might force the planner into optimization that no longer is optimal. In my experience developers almost never comeback to a query with hints to double check if hints are really needed. Famously oracle has query hints that don't do nothing no more, that are ignored, but oracle can't remove them from query language because that would break too many existing queries. I like Postgres stance that if query planner doesn't do a good job, then dba should first update table/column statistics, and if things are truly bad, submit but to Postgres so the query optimizer can be updated itself. Saying all that, hints support through an extension to Postgres is a good compromise. Postgres developers don't need to bother with hints, its a third party feature. And dba/users, if they really need hints, now they have them.
- deniska 3y agoWhy it's good: you won't get a sudden slowdown if postgresql for some reason changes its plan to something much less performant.
- silon42 3y agoYup... parent point 1 is an often a misfeature in production (yes, sometimes the plan is better, but sometimes not).
- btown 3y agoWhat about when column statistics don’t accurately describe a subset of the table that you know from a business perspective is likely to have different dynamics? Allowing the client to essentially A/B test between different advisements and evaluate performance, with full knowledge of that business context, may be meaningful at certain scales.
- phamilton 3y agoThat's valuable, but cranking statistics up to 10000 and/or creating custom statistics can go a very long way to helping the planner understand the dataset more fully.
- staticlibs 3y agoWhat is cool about Postgres extensions ecosystem, is that new functionality can be built upon existing extensions. For example, Babelfish (SQL Server compatibility layer for Postgres) uses pg_hint_plan to implement Transact-SQL hints support [1]. This is really useful, because existing queries, that were optimized for MSSQL, may require tuning when moved to Postgres/Babelfish, and it is great to have additional knobs for tuning. [1] https://github.com/babelfish-for-postgresql/babelfish-for-postgresql/wiki/Using-T%E2%80%90SQL-query-hints-to-improve-Babelfish-query-performance https://github.com/babelfish-for-postgresql/babelfish-for-po...
- nextaccountic 3y agoIs this like MSSQL's WITH(INDEX())? [0] (Does PostgreSQL have this? It seems it doesn't [1]) [0] https://stackoverflow.com/questions/6593765/how-to-use-index-in-select-statement https://stackoverflow.com/questions/6593765/how-to-use-index... [1] https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index https://stackoverflow.com/questions/309786/how-do-i-force-po... - and https://www.2ndquadrant.com/en/blog/hinting_at_postgresql/ https://www.2ndquadrant.com/en/blog/hinting_at_postgresql/ says why Postgres devs think this is a bad feature
- williamdclt 3y agoIt doesn’t exist indeed. I understand the PG maintainers stance, but I can’t agree with it. First because “we get bugs fixed quickly” is true but not true enough (my own experience: https://www.postgresql.org/message-id/17540-7aa1855ad5ec18b4%40postgresql.org https://www.postgresql.org/message-id/17540-7aa1855ad5ec18b4... has not been fixed since reported 1.5y ago) Second because all the knobs available to tune the query planner (listed in your last link) are obscure and require deep understanding of Postgres internals to make sense of them, which means that only a fraction of people understand them whereas query plans are more pedestrian. They’re also often database-scoped or session-scoped rather than query-scoped, it’s a huge risk of making things worse rather than better.
- phamilton 3y agoAll the knobs for query planning and cost estimation can be scoped to the transaction with `set local <key>=<value>`.
- franckpachot 3y agoEven if bugs are fixed instantly, nobody will apply a patch in production withiut previous testing. Changing system-wide behavior to fix a single query may make things worse. Hints are the only way to fix at the scope of one statement with the guarantee that it doesn't break others
- RaftPeople 3y ago> I understand the PG maintainers stance, but I can’t agree with it I don't just not agree, I truly don't understand how they can take that position knowing the nature of the problem. "The problem"=optimizing over a large space in limited time without complete info. It will never be perfect, there will always be exceptions.
- mattrighetti 3y agoNot related to the project but do you know of useful content that explains how to approach query optimization for Postgres? All I was able to find was classic stuff like `explain analyze` etc.
- phamilton 3y agohttps://www.pgmustard.com/ https://www.pgmustard.com/ is a great tool. There are some free alternatives for the actual tool, but their documentation is pretty great. For example, https://www.pgmustard.com/docs/explain/heap-fetches https://www.pgmustard.com/docs/explain/heap-fetches provides clarity that isn't obvious from the official pg docs. Beyond those resources, here are a few useful things I've learned: 1. `explain (analyze, buffers)` is useful. It will tell you about hot vs cold data. One caveat: it doesn't deduplicate the buffer hits, so 1M buffer hits could be only a few thousand unique pages. But I still find it useful especially when comparing query plans. 2. pg_buffercache. Knowing what's in the buffer allows you to optimize the long tail of queries that perform buffer reads. Sometimes rebuilding an index on an unrelated table can create space in the buffer for the data the query needs. 3. Try using dedicated covering partial indexes for high traffic queries. An index-only scan is super cheap and with the right include and where condition you can make it small and efficient. The tips above are especially useful in Aurora, where the shared buffers are huge (there's no page cache so it's the only caching layer).
- iurisilvio 3y agoThe PEV2 is open source and give you a good visualization. I never used this pgmustard to compare. https://explain.dalibo.com/ https://explain.dalibo.com/
- zac23or 3y agoMy biggest problem with Postgres is the Planner/Optimizer. The worst example is a super simple query: SELECT * FROM TABLE FOO WHERE ORGANIZATION_ID = 10 ORDER BY CREATED_AT LIMIT 10 OFFSET 0; Postgres sorts by created_at using an index, but uses the filter on organization_id. This organization has a million rows... without the order, the query runs in ms. In order, in seconds/minutes. Sometimes the plan changes if you change the query offset! For organization 10, the plane is goood, for organization 11, the plane is bad... Change stats, add more stats, fill the void. that doesn't solve anything. The solution is to use a calc on order by , but in the end you need to create an index using Organization_id and created_at to really fix it. I did the same test on SQLServer and SQLite, they executed the query correctly, using the correct index. I've never created an index to fix a bad plan in SQLServer, and I've used SqlServer a lot more than Postgres. And I use a Pass database, it's impossible to install extensions... I'm going to test this extension locally, if it works I'll convince the company to use it (by changing the database provider)
- Izkata 3y agoThis sounds like what I described yesterday: https://news.ycombinator.com/item?id=39713239 https://news.ycombinator.com/item?id=39713239 In addition to what I described there, the way you described it, it sounds like you don't have a single index that covers both columns. That may also help if I understood right.
- zac23or 3y ago> it sounds like you don't have a single index that covers both columns. Yes. But other databases (SQLlite, etc.) select the correct index, without the need to create an index with both columns.
- franckpachot 3y agoIn all databases you will avoid bad plans (and the unpredictable performance related to plan changes) by providing the right index. You have two selective filters: WHERE and LIMIT so the right index have both
- foldr 3y agoI've often wanted (or thought that I wanted) something that works similarly to a SQL database but where you write queries at about the same level of abstraction as the output of EXPLAIN. SQL is great for generating ad-hoc reports, or in other contexts where performance isn't critical. But in a production environment you can't always rely on it to perform as expected.
- whartung 3y agoBack in the day, we were use a SQL db on the PC. The planner wasn’t particularly sophisticated in those days, nor was the data volume crushing. But in the end, we would take all of our queries, and check their plans. We would pad the db with data until the combination of data and statistics and the planner all did what we wanted. Once we were happy with the statistics and the results we’d reset the database with “empty” data for the customer, and never touch the statistics again. We were mostly trying to eliminate table scans on simple queries and joins, and influencing index selection.
- cryptonector 3y agoIMO query plan hinting should be done out of band, using table source names to refer to the parts of the query. In psql this might take the form of a \command that lets one specify the hints for the preceding query.