Hi there,
With a composite index on (effective_on, id)
Query plan
I am trying to optimize a simple query that returns first 100 rows that have been updated since a given timestamp (ordered by timestamp and id desc). If there are several rows with the same timestamp I need to a second condition, that states that I want to return rows having the given timestamp and id > given id.
The obvious query is
SELECT * FROM register_uz_accounting_entities
WHERE effective_on > '2014-07-11' OR (effective_on = '2014-07-11' AND id > 1459)
ORDER BY effective_on, id
LIMIT 100
With a composite index on (effective_on, id)
Query plan
"Limit (cost=4613.70..4613.95 rows=100 width=1250) (actual time=0.122..0.130 rows=22 loops=1)"
" Buffers: shared hit=28"
" -> Sort (cost=4613.70..4617.33 rows=1453 width=1250) (actual time=0.120..0.122 rows=22 loops=1)"
" Sort Key: effective_on, id"
" Sort Method: quicksort Memory: 30kB"
" Buffers: shared hit=28"
" -> Bitmap Heap Scan on register_uz_accounting_entities (cost=35.42..4558.17 rows=1453 width=1250) (actual time=0.036..0.083 rows=22 loops=1)"
" Recheck Cond: ((effective_on > '2014-07-11'::date) OR ((effective_on = '2014-07-11'::date) AND (id > 1459)))"
" Buffers: shared hit=28"
" -> BitmapOr (cost=35.42..35.42 rows=1453 width=0) (actual time=0.026..0.026 rows=0 loops=1)"
" Buffers: shared hit=6"
" -> Bitmap Index Scan on idx2 (cost=0.00..6.49 rows=275 width=0) (actual time=0.017..0.017 rows=15 loops=1)"
" Index Cond: (effective_on > '2014-07-11'::date)"
" Buffers: shared hit=3"
" -> Bitmap Index Scan on idx2 (cost=0.00..28.21 rows=1178 width=0) (actual time=0.007..0.007 rows=7 loops=1)"
" Index Cond: ((effective_on = '2014-07-11'::date) AND (id > 1459))"
" Buffers: shared hit=3"
"Total runtime: 0.204 ms"
Everything works as expected. However if I change the constraint to a timestamp/date more in the past (resulting in far more matching rows) the query slows down drastically.
SELECT * FROM register_uz_accounting_entities
WHERE effective_on > '2014-06-11' OR (effective_on = '2014-06-11' AND id > 1459)
ORDER BY effective_on, id
LIMIT 100
"Limit (cost=0.42..649.46 rows=100 width=1250) (actual time=516.125..516.242 rows=100 loops=1)"
" Buffers: shared hit=576201"
" -> Index Scan using idx2 on register_uz_accounting_entities (cost=0.42..106006.95 rows=16333 width=1250) (actual time=516.122..516.229 rows=100 loops=1)"
" Filter: ((effective_on > '2014-06-11'::date) OR ((effective_on = '2014-06-11'::date) AND (id > 1459)))"
" Rows Removed by Filter: 797708"
" Buffers: shared hit=576201"
"Total runtime: 516.304 ms"
I've tried to optimize this query by pushing down the limit and order by's into explicit subselects.
SELECT * FROM (
SELECT * FROM register_uz_accounting_entities
WHERE effective_on > '2014-06-11'
ORDER BY effective_on, id LIMIT 100
) t1
UNION
SELECT * FROM (
SELECT * FROM register_uz_accounting_entities
WHERE effective_on = '2014-06-11' AND id > 1459
ORDER BY effective_on, id LIMIT 100
) t2
ORDER BY effective_on, id
LIMIT 100
-- query plan
"Limit (cost=684.29..684.54 rows=100 width=1250) (actual time=2.648..2.708 rows=100 loops=1)"
" Buffers: shared hit=203"
" -> Sort (cost=684.29..684.79 rows=200 width=1250) (actual time=2.646..2.672 rows=100 loops=1)"
" Sort Key: register_uz_accounting_entities.effective_on, register_uz_accounting_entities.id"
" Sort Method: quicksort Memory: 79kB"
" Buffers: shared hit=203"
" -> HashAggregate (cost=674.65..676.65 rows=200 width=1250) (actual time=1.738..1.971 rows=200 loops=1)"
" Buffers: shared hit=203"
" -> Append (cost=0.42..661.15 rows=200 width=1250) (actual time=0.054..0.601 rows=200 loops=1)"
" Buffers: shared hit=203"
" -> Limit (cost=0.42..338.62 rows=100 width=1250) (actual time=0.053..0.293 rows=100 loops=1)"
" Buffers: shared hit=101"
" -> Index Scan using idx2 on register_uz_accounting_entities (cost=0.42..22669.36 rows=6703 width=1250) (actual time=0.052..0.260 rows=100 loops=1)"
" Index Cond: (effective_on > '2014-06-11'::date)"
" Buffers: shared hit=101"
" -> Limit (cost=0.42..318.53 rows=100 width=1250) (actual time=0.037..0.228 rows=100 loops=1)"
" Buffers: shared hit=102"
" -> Index Scan using idx2 on register_uz_accounting_entities register_uz_accounting_entities_1 (cost=0.42..30888.88 rows=9710 width=1250) (actual time=0.036..0.187 rows=100 loops=1)"
" Index Cond: ((effective_on = '2014-06-11'::date) AND (id > 1459))"
" Buffers: shared hit=102"
"Total runtime: 3.011 ms"
=> Very fast.
The question is... why is the query planner unable to make this optimization for the slow query? What am I missing?
Queries with syntax highlighting https://gist.github.com/jsuchal/0993fd5a2bfe8e7242d1
Thanks in advance.
