Pg_hint_plan: Force PostgreSQL to execute query plans the way you want (github.com)

Glad to see it executed

Isn't this what PREPARE and EXECUTE does?

    INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g

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');

The GitHub repo's wording is broken english currently though which is (probably?) worse.

What's needed, then, is a benchmark suite that tests if those hints still give a better performance than a hintless query

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

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

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.

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.

It is certainly a bad practice, but on very particular occasions it is invaluable.

First because “we get bugs fixed quickly” is true but not true enough (my own experience: 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.

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/

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.

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.

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.

What are the current values of `random_page_cost` and `seq_page_cost`?

    SHOW seq_page_cost;
    SHOW random_page_cost;

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

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.

  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

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.