4 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 correl
by pgaddict 3y ago
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.
- zac23or 3y ago> It's really hard to say why this is happening without EXPLAIN ANALYZE 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.
- justinclift 3y ago> I can run EXPLAIN and paste it here ... Might as well, could turn up something useful. :)
- pgaddict 3y agoI'd suggest reporting it to pgsql-performance mailing list [1], and continuing the discussion there. I'd expect more community members to join that discussion there. [1] https://www.postgresql.org/list/pgsql-performance/ https://www.postgresql.org/list/pgsql-performance/
- zac23or 3y agoSee https://news.ycombinator.com/item?id=39730206 https://news.ycombinator.com/item?id=39730206
- wiredfool 3y agoOne 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.