-
-
Save jsuchal/0993fd5a2bfe8e7242d1 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- first query | |
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 | |
-- query plan (slow) | |
"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" | |
-- second query | |
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 (fast) | |
"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" | |
-- different effective_on date (less matching rows) | |
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 | |
-- query plan (fast) | |
"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" | |
-- row values | |
SELECT * FROM register_uz_accounting_entities | |
WHERE (effective_on, id) > ('2014-06-11', 1459) | |
ORDER BY effective_on, id | |
LIMIT 100 | |
"Limit (cost=0.43..338.62 rows=100 width=1250) (actual time=0.038..0.180 rows=100 loops=1)" | |
" Buffers: shared hit=102" | |
" -> Index Scan using idx2 on register_uz_accounting_entities (cost=0.43..22669.36 rows=6703 width=1250) (actual time=0.036..0.154 rows=100 loops=1)" | |
" Index Cond: (ROW(effective_on, id) > ROW('2014-06-11'::date, 1459))" | |
" Buffers: shared hit=102" | |
"Total runtime: 0.247 ms" | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I didn't see @fatalmind's talk or hear Tom Lane's advice. What do y'all mean "use row-values"?