sync_api_query.sql

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

The first query is slow because: Rows Removed by Filter: 797708

I don't know how the index is defined, but for this query, I'd suggest an index on the full order by clause (effective_on, id). Further, I'd also suggest re-phrasing the where clause using row-values (remember my talk?)

 SELECT * FROM register_uz_accounting_entities 
  WHERE (effective_on, id) > ('2014-06-11', 1459)
  ORDER BY effective_on, id 
  LIMIT 100

I didn't look at the other queries in detail, please give the above suggestions a try.

@fatalmind yup, got the same advice from Tom Lane to use row-values. Thanks.

btw there was an index on effective_on, id

I didn't see @fatalmind's talk or hear Tom Lane's advice. What do y'all mean "use row-values"?