My 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)
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.
I've not been able to reproduce this behavior. Maybe someone else will have better luck.
create unlogged table foo (id int primary key generated by default as identity, organization_id int default floor(random()*10), created_at timestamptz default timestamp '2010-01-01' + random()*(timestamp '2020-01-01' - timestamp '2010-01-01'));
create index on foo (created_at);
create index on foo (organization_id);
alter table foo add column updated_at timestamptz null default '2010-01-01 00:00:00'::timestamp without time zone + random() * ('2020-01-01 00:00:00'::timestamp without time zone - '2010-01-01 00:00:00'::timestamp without time zone);
create index on foo (updated_at);
insert into foo (id) select generate_series(1, 1e8);
cluster foo using foo_created_at_idx;
vacuum analyze;
explain analyze select * from foo where organization_id = 1 order by updated_at limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..169.50 rows=10 width=24) (actual time=0.088..0.242 rows=10 loops=1)
-> Index Scan using foo_updated_at_idx on foo (cost=0.57..173267047.88 rows=10256672 width=24) (actual time=0.086..0.237 rows=10 loops=1)
Filter: (organization_id = 1)
Rows Removed by Filter: 73
Planning Time: 0.168 ms
Execution Time: 0.269 ms
You reproduced it. See the index being used. foo_updated_at_idx, not foo_organization_id_idx. See a FILTER on Organization_id, not a Cond index. If you create an index using Organization_id and updated_at the plan changes to:
Limit (cost=0.42..3.26 rows=10 width=24) (actual time=0.013..0.024 rows=10 loops=1)
-> Index Scan using foo_organization_id_updated_at_idx on foo (cost=0.42..2011.50 rows=7063 width=24) (actual time=0.012..0.023 rows=10 loops=1)
Index Cond: (organization_id = 1)
Planning Time: 0.154 ms
Execution Time: 0.033 ms
It's really hard to say why this is happening without EXPLAIN ANALYZE (and even then it may not be obvious), but it very much seems like the problem with correlated columns we often have with LIMIT queries.
The optimizer simply assumes the matching rows are "uniformly" distributed, because limit is costed as linear approximation of the startup/total cost of the input node. For example, consider this made up query plan
Limit (cost=0.0..100.0 rows=10)
-> Index scan (cost=0.0...10000000.0 rows=1000000)
Filter: dept_id=10
The table may have many more rows - say, 100x more. But the optimizer assumes the rows with (dept_id=10) are distributed in the index, so it can scan the first 1/100000 if the index to get the 10 rows the limits needs. So it assumes the cost is 10*(0+10000000)/1000000.
But chances are the dept_id=10 rows happen to be at the very end of the index, so the planner actually needs to scan almost the whole index, making the cost wildly incorrect.
You can verify this by looking how far the dept_id rows are in "order by created_id" results. If there are many other rows before the 10 rows you need, it's likely this.
Sadly, the optimizer is not smart enough to realize there's this risk. I agree it's an annoying robustness issue, but I don't have a good idea how to mitigate it ... I wonder what the other databases do.
One thing that I've run across is when doing a select on a large table with serial or indexed timestamps, ordered by that column is that the plans are horrible when the criteria you're using are unexpectedly rare, even if there's an index on that column. e.g. If col foo has low cardinality, with a bunch of common but a few rare entries, the planner will prefer using the ordering index to the column index even for the very low frequency ones.
Partial indexes can be really useful there, as well as order by (id+0). But it's a total hack.
> For organization 10, the plane is goood, for organization 11, the plane is bad...
that's probably because for 11, there will be more results (say 10k), and optimizer decided that it is faster to scan already sorted index with filter, than sort 10k rows.
Logic of optimizer is usually reasonable, my problem with it is that it relies on cost config parameters, which I think by default are inadequate for modern servers, it is not clear how to set it correctly, and actual optimizer rules are not documented, but one need to look at PgSQL code to understand them.
That sounds like the pg stats are messed up somehow. Try running explain analyze foo to see if it changes things.
Postgres doesn’t automatically create indexes - I bet the others have some implicit index on organization_id, created_at. Otherwise they would be giving bad results in other cases. I don’t see why you can’t just make that index in postgres.
I’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.
Just 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.
I 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
There 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
What 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.
- 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.
> 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.
That’s a very unhelpful stance when I’m having an incident in production because PG decided to use a stupid query plan. Waiting months - years for a bugfix (which might not even be backported to my PG version) is not a solution.
I agree that hints are a very dangerous thing, but they’re invaluable as an emergency measure
I like it as an emergency measure, but I often see them used when there's a shallow understanding of operating the db.
Before using a hint or rewriting a query to force a specific plan, I try and push the team to do these things:
1. Run `vacuum analyze` and tune the auto vacuum settings. This fixes issues surprisingly often.
2. Increase statistics on the table.
3. Tweak planner settings globally or just for the query. Stuff like `set local join_collapse_limit=1` can fix a bad plan. This is pretty similar to hinting, so not a huge argument that this is better beyond not requiring an extension.
There are other ways to achieve that (optimizer features and decisions can be influenced by config settings, or by altering entries in statistics tables), but it's admittedly quite clunky. I agree that a production incident at 3am is definitely not the right time to start grokking these settings. The extension can be a good remedy for such cases.
I have been burned before when a query on a newer db version was "optimized" in a different way that caused performance to drop but I have not yet had a query on the same version vary so drastically as to cause a problem.
Is this a more common occurrence that I just have not encountered before?
> - 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.
It depends what is your priority. Most production environments want to favor predictability over raw performance. I'd rather trade 10% performance degradation in average for a consistent query performance.
Even if statistics or new versions could come with 10% better plans, I prefer that my query's performance is predictable and does not experience high p90s or even worse that you risk experiencing plan flips that turn your 0.2s 10K/qps query into a 40s query.
100%, even if my performance slow degrades as my data changes I would much rather have gradual performance and be able to manually switch to a better plan at some point than have the DB suddenly switch a new plan which is probably better, but if not may cause a huge performance swing that takes my application offline.
It would be very interesting if there was some sort of system that ensured query plans don't change "randomly" but if it noticed that a new plan was expected to be better on most queries it would surface this in some interface that you could then test and approve.
Maybe it could also support gradual rollout, try this on 1% of queries first, then if it does improve performance it can be fully approved.
It would be extra cool if this was automatic. New plans could slowly be slowly rolled out by the DB and rolled back if they aren't actually better.
That's the theory but it does not work this way in practice.
Many of the issues people run into with the Postgres query planner/optimizer are side effects of architectural peculiarities with no straightforward fix. Some of these issues, such as the limitations of Postgres statistics collection, have been unaddressed for decades because they are extremely difficult to address without major architectural surgery that will have its own side effects.
In my opinion, Postgres either needs to allow people to disable/bypass the query planner with hints or whatever, or commit to the difficult and unpleasant work of fixing the architecture so that these issues no longer occur. This persistent state of affairs of neither fixing the core problems nor allowing a mechanism to work around them is not user friendly. I like and use Postgres but this aspect of the project is quite broken.
The alternative is to put a disclaimer in the Postgres documentation that it should not be used for certain types of common workloads or large data models; many of these issues don't show up in force until databases become quite large.
This is a chronic source of pain in large-scale Postgres installations.
I don't know which "architectural peculiarities" you have in mind, but the issues we have are largely independent of the architecture. So I'm not sure what exactly you think we should be fixing architecture-wise ...
The way I see it the issues we have in query planning/optimization are largely independent of the overall architecture. It's simply a consequence of relying on statistics too much. But there's always going to be inaccuracies, because the whole point of stats is that it's a compact/lossy approximation of the data. No matter how much more statistics we might collect there will always be some details missing, until the statistics are way too large to be practical.
There are discussions about considering how "risky" a given plan is - some plan shapes may be more problematic, but I don't think anyone submitted any patch so far. But even with that would not be a perfect solution - no approach relying solely on a priori information can be.
IMHO the only way forward is to work on making the plans more robust to this kind of mistakes. But that's very hard too.
Those points are academically correct. In the real world you can end up with the query planner doing very boneheaded stuff that threatens to literally break your business when there's another approach that's fast it just seems to miss. In these cases hints would be a lot easier than the stuff you end up doing.
What 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.
That'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.
This is the main motivation behind learned "steering" query optimizers: even if a DBA finds the right hint for a query, it is difficult to track that hint through data changes, future DB versions, and even query changes (e.g., if you add another condition to the WHERE clause, should you keep the hints or drop them?). Turns out, with a little bit of systems work, ML models can do a reasonably good job of selecting a good hint for a particular query plan.
More philosophically, the query planner is a finely-tuned piece of engineering. If your mental model disagrees with it ("Why isn't it using the index I want?") then it's highly likely your mental model is wrong.
Your data model might contain obvious mistakes. Statistics can be out of date too, if e.g. a table was bulk loaded and never analyzed. `ANALYZE tablename` done. Sometimes removing unused indexes can improve things. TLDR; it's always something that you need to tune in your own database. When in doubt, the query planner is right and you are wrong. Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints.
Hints are an extreme measure. You're basically saying that you know better than the query planner, for now until eternity, and you choose to optimize by hand. That may be the case but it requires detailed knowledge of your table and access patterns. The vast majority of misbehaving query plans just need updated statistics or a better index.
Queries are complex and the query planner is using heuristics that might just not fit your situation in some cases. The query planner is great for 99.99% of queries but a super small number of edge cases will need tuning at the same time.
Finding out what went wrong in the query plan by looking at optimizer traces is a lot of work. I did so recently and the trace alone was 317MB.
This is not a correct assumption with Postgres. Its statistics collection process has fundamental flaws when tables become large such that the statistics it uses for optimization are in no way representative of the actual data, leading to the situation this thread is about.
Statistics collection is a weak spot in Postgres and query optimization relies on that information to do its job.
> you know better than the query planner, for now until eternity,
Nope, you just have to know it's fixing a real problem today.
Having a query regress in performance below a KPI would be worse than not taking advantage of a further optimization in the future, due to out of date hint.
> Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints
Why is that better?
Luckily we don't have to rely on such grandiose claims. Just try it out. If you find a query that you can tune better than the planner for your data set, then it's a better outcome.
> Hints are an extreme measure. You're basically saying that you know better than the query planner
Absolutely.
A query planner does not analyze the complete and precise solution space, none of them do. The query planner will be extremely wrong sometimes. The only logical solution is to provide a mechanism to guide the planner towards the correct solution.
I've worked with DB2, Oracle and MS SQL Server over the last 3 decades and the story is always the same, the planner is not perfect.
Is 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
There 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.
Using 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.
I'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.
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.
Not 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.
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).
Readit News
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)
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.
Yes. But other databases (SQLlite, etc.) select the correct index, without the need to create an index with both columns.
What are the current values of `random_page_cost` and `seq_page_cost`?
The default is typically 4, and in practice with modern disks you should use a lower value closer to 1.I have many other problems with the planner, this is the most absurd due to the simplicity of the query.
The optimizer simply assumes the matching rows are "uniformly" distributed, because limit is costed as linear approximation of the startup/total cost of the input node. For example, consider this made up query plan
The table may have many more rows - say, 100x more. But the optimizer assumes the rows with (dept_id=10) are distributed in the index, so it can scan the first 1/100000 if the index to get the 10 rows the limits needs. So it assumes the cost is 10*(0+10000000)/1000000.But chances are the dept_id=10 rows happen to be at the very end of the index, so the planner actually needs to scan almost the whole index, making the cost wildly incorrect.
You can verify this by looking how far the dept_id rows are in "order by created_id" results. If there are many other rows before the 10 rows you need, it's likely this.
Sadly, the optimizer is not smart enough to realize there's this risk. I agree it's an annoying robustness issue, but I don't have a good idea how to mitigate it ... I wonder what the other databases do.
Partial indexes can be really useful there, as well as order by (id+0). But it's a total hack.
I can run EXPLAIN and paste it here, but it's just one of the problems with the planner (in a very basic query), I have many other problems with it.
On the other hand, I've never had any other serious problems with Postgres. It's a very good database.
that's probably because for 11, there will be more results (say 10k), and optimizer decided that it is faster to scan already sorted index with filter, than sort 10k rows.
Logic of optimizer is usually reasonable, my problem with it is that it relies on cost config parameters, which I think by default are inadequate for modern servers, it is not clear how to set it correctly, and actual optimizer rules are not documented, but one need to look at PgSQL code to understand them.
I agree. My problem is that SQLite, an embedded database, seems to have a better Planner/Optimizer than Postgres.
Is the development of Planner/Optimizer abandoned?
> my problem with it is that it relies on cost config parameters
And I don't have access to change it, as it's a PASS database...
Postgres doesn’t automatically create indexes - I bet the others have some implicit index on organization_id, created_at. Otherwise they would be giving bad results in other cases. I don’t see why you can’t just make that index in postgres.
I did
> Postgres doesn’t automatically create indexes
Both fields have indexes (created manually). Postgres is incorrectly choosing the index in order by column, not the index in the where clause.
[1] https://github.com/babelfish-for-postgresql/babelfish-for-po...
- 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.
That’s a very unhelpful stance when I’m having an incident in production because PG decided to use a stupid query plan. Waiting months - years for a bugfix (which might not even be backported to my PG version) is not a solution.
I agree that hints are a very dangerous thing, but they’re invaluable as an emergency measure
Before using a hint or rewriting a query to force a specific plan, I try and push the team to do these things:
1. Run `vacuum analyze` and tune the auto vacuum settings. This fixes issues surprisingly often. 2. Increase statistics on the table. 3. Tweak planner settings globally or just for the query. Stuff like `set local join_collapse_limit=1` can fix a bad plan. This is pretty similar to hinting, so not a huge argument that this is better beyond not requiring an extension.
Is this a more common occurrence that I just have not encountered before?
> - 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.
It depends what is your priority. Most production environments want to favor predictability over raw performance. I'd rather trade 10% performance degradation in average for a consistent query performance.
Even if statistics or new versions could come with 10% better plans, I prefer that my query's performance is predictable and does not experience high p90s or even worse that you risk experiencing plan flips that turn your 0.2s 10K/qps query into a 40s query.
It would be very interesting if there was some sort of system that ensured query plans don't change "randomly" but if it noticed that a new plan was expected to be better on most queries it would surface this in some interface that you could then test and approve.
Maybe it could also support gradual rollout, try this on 1% of queries first, then if it does improve performance it can be fully approved.
It would be extra cool if this was automatic. New plans could slowly be slowly rolled out by the DB and rolled back if they aren't actually better.
Many of the issues people run into with the Postgres query planner/optimizer are side effects of architectural peculiarities with no straightforward fix. Some of these issues, such as the limitations of Postgres statistics collection, have been unaddressed for decades because they are extremely difficult to address without major architectural surgery that will have its own side effects.
In my opinion, Postgres either needs to allow people to disable/bypass the query planner with hints or whatever, or commit to the difficult and unpleasant work of fixing the architecture so that these issues no longer occur. This persistent state of affairs of neither fixing the core problems nor allowing a mechanism to work around them is not user friendly. I like and use Postgres but this aspect of the project is quite broken.
The alternative is to put a disclaimer in the Postgres documentation that it should not be used for certain types of common workloads or large data models; many of these issues don't show up in force until databases become quite large.
This is a chronic source of pain in large-scale Postgres installations.
The way I see it the issues we have in query planning/optimization are largely independent of the overall architecture. It's simply a consequence of relying on statistics too much. But there's always going to be inaccuracies, because the whole point of stats is that it's a compact/lossy approximation of the data. No matter how much more statistics we might collect there will always be some details missing, until the statistics are way too large to be practical.
There are discussions about considering how "risky" a given plan is - some plan shapes may be more problematic, but I don't think anyone submitted any patch so far. But even with that would not be a perfect solution - no approach relying solely on a priori information can be.
IMHO the only way forward is to work on making the plans more robust to this kind of mistakes. But that's very hard too.
What's needed, then, is a benchmark suite that tests if those hints still give a better performance than a hintless query
It is certainly a bad practice, but on very particular occasions it is invaluable.
Your data model might contain obvious mistakes. Statistics can be out of date too, if e.g. a table was bulk loaded and never analyzed. `ANALYZE tablename` done. Sometimes removing unused indexes can improve things. TLDR; it's always something that you need to tune in your own database. When in doubt, the query planner is right and you are wrong. Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints.
Hints are an extreme measure. You're basically saying that you know better than the query planner, for now until eternity, and you choose to optimize by hand. That may be the case but it requires detailed knowledge of your table and access patterns. The vast majority of misbehaving query plans just need updated statistics or a better index.
Finding out what went wrong in the query plan by looking at optimizer traces is a lot of work. I did so recently and the trace alone was 317MB.
Statistics collection is a weak spot in Postgres and query optimization relies on that information to do its job.
Nope, you just have to know it's fixing a real problem today.
Having a query regress in performance below a KPI would be worse than not taking advantage of a further optimization in the future, due to out of date hint.
> Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints
Why is that better?
Luckily we don't have to rely on such grandiose claims. Just try it out. If you find a query that you can tune better than the planner for your data set, then it's a better outcome.
While it's great that "good engineering" exists, it often requires a bunch more time and effort than people have for the task right then.
Being inflexible and always demanding that time is taken is an extremely poor approach, and often doesn't lead to a good quality result.
Instead it often leads to the inflexible thing being retired and a more flexible alternative being used from then on.
Absolutely.
A query planner does not analyze the complete and precise solution space, none of them do. The query planner will be extremely wrong sometimes. The only logical solution is to provide a mechanism to guide the planner towards the correct solution.
I've worked with DB2, Oracle and MS SQL Server over the last 3 decades and the story is always the same, the planner is not perfect.
Yes I do know better what data is going to be in the database I am managing that some heuristic mechanism.
Still super useful when you have no other options though.
For older versions, you can do:
Isn't this what PREPARE and EXECUTE does?
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.
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).
https://explain.dalibo.com/