gistfile1.txt

test=# \d t1
                                      Table "public.t1"
    Column    |            Type             |                    Modifiers                    
--------------+-----------------------------+-------------------------------------------------
 id           | integer                     | not null default nextval('t1_id_seq'::regclass)
 type         | character varying           | default ''::character varying
 published_at | timestamp without time zone | default timezone('UTC'::text, now())
Indexes:
    "t1_published_at_idx" btree (published_at)

test=# \d t2
                                      Table "public.t2"
    Column    |            Type             |                    Modifiers                    
--------------+-----------------------------+-------------------------------------------------
 id           | integer                     | not null default nextval('t2_id_seq'::regclass)
 type         | character varying           | default ''::character varying
 published_at | timestamp without time zone | default timezone('UTC'::text, now())
Indexes:
    "t2_type_published_at_idx" btree (type, published_at)

test=# select type, count(*) from t1 group by type;
   type   | count 
----------+-------
 type_new | 10001
 type_old |     1
(2 rows)

test=# select type, count(*) from t2 group by type;
   type   | count 
----------+-------
 type_new | 10001
 type_old |     1
(2 rows)

test=# explain analyze select id from t1 where type = 'type_new' and published_at < '2014-01-05' order by published_at desc limit 2;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.36 rows=2 width=12) (actual time=0.011..0.013 rows=2 loops=1)
   ->  Index Scan Backward using t1_published_at_idx on t1  (cost=0.29..387.32 rows=10001 width=12) (actual time=0.010..0.012 rows=2 loops=1)
         Index Cond: (published_at < '2014-01-05 00:00:00'::timestamp without time zone)
         Filter: ((type)::text = 'type_new'::text)
 Total runtime: 0.030 ms
(5 rows)

test=# explain analyze select id from t1 where type = 'type_old' and published_at < '2014-01-05' order by published_at desc limit 2;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..387.32 rows=1 width=12) (actual time=3.686..3.688 rows=1 loops=1)
   ->  Index Scan Backward using t1_published_at_idx on t1  (cost=0.29..387.32 rows=1 width=12) (actual time=3.685..3.686 rows=1 loops=1)
         Index Cond: (published_at < '2014-01-05 00:00:00'::timestamp without time zone)
         Filter: ((type)::text = 'type_old'::text)
         Rows Removed by Filter: 10001
 Total runtime: 3.706 ms
(6 rows)

test=# explain analyze select id from t2 where type = 'type_new' and published_at < '2014-01-05' order by published_at desc limit 2;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.40 rows=2 width=12) (actual time=0.045..0.047 rows=2 loops=1)
   ->  Index Scan Backward using t2_type_published_at_idx on t2  (cost=0.29..558.10 rows=10000 width=12) (actual time=0.044..0.044 rows=2 loops=1)
         Index Cond: (((type)::text = 'type_new'::text) AND (published_at < '2014-01-05 00:00:00'::timestamp without time zone))
 Total runtime: 0.066 ms
(4 rows)

test=# explain analyze select id from t2 where type = 'type_old' and published_at < '2014-01-05' order by published_at desc limit 2;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..8.30 rows=1 width=12) (actual time=0.036..0.037 rows=1 loops=1)
   ->  Index Scan Backward using t2_type_published_at_idx on t2  (cost=0.29..8.30 rows=1 width=12) (actual time=0.035..0.036 rows=1 loops=1)
         Index Cond: (((type)::text = 'type_old'::text) AND (published_at < '2014-01-05 00:00:00'::timestamp without time zone))
 Total runtime: 0.056 ms
(4 rows)