gistfile1.sql

create table foo(bar integer not null, baz integer not null, qux text);
insert into foo(bar, baz, qux) select random()*10, random()*10, 'some much much much much much much much longer random text '||g from generate_series(1,10000) g;
create index bar_baz on foo(bar, baz);



postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=120.64..120.65 rows=1 width=32) (actual time=3.051..3.051 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=119 read=30
   ->  Bitmap Heap Scan on stack.foo  (cost=73.59..120.60 rows=17 width=32) (actual time=1.400..1.771 rows=520 loops=1)
         Output: bar, baz, qux
         Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
         Buffers: shared hit=119 read=30
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..73.58 rows=17 width=0) (actual time=1.368..1.368 rows=520 loops=1)
               Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
               Buffers: shared read=30
 Total runtime: 3.096 ms
(11 rows)

postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=120.64..120.65 rows=1 width=32) (actual time=2.960..2.960 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=148
   ->  Bitmap Heap Scan on stack.foo  (cost=73.59..120.60 rows=17 width=32) (actual time=1.319..1.666 rows=520 loops=1)
         Output: bar, baz, qux
         Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
         Buffers: shared hit=148
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..73.58 rows=17 width=0) (actual time=1.289..1.289 rows=520 loops=1)
               Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
               Buffers: shared hit=29
 Total runtime: 3.000 ms
(11 rows)

postgres=> cluster foo using bar_baz;
CLUSTER
postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=120.64..120.65 rows=1 width=32) (actual time=2.930..2.930 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=19 read=30
   ->  Bitmap Heap Scan on stack.foo  (cost=73.59..120.60 rows=17 width=32) (actual time=1.452..1.664 rows=520 loops=1)
         Output: bar, baz, qux
         Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
         Buffers: shared hit=19 read=30
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..73.58 rows=17 width=0) (actual time=1.440..1.440 rows=520 loops=1)
               Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
               Buffers: shared read=30
 Total runtime: 2.976 ms
(11 rows)

postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=120.64..120.65 rows=1 width=32) (actual time=2.798..2.799 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=48
   ->  Bitmap Heap Scan on stack.foo  (cost=73.59..120.60 rows=17 width=32) (actual time=1.314..1.517 rows=520 loops=1)
         Output: bar, baz, qux
         Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
         Buffers: shared hit=48
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..73.58 rows=17 width=0) (actual time=1.303..1.303 rows=520 loops=1)
               Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
               Buffers: shared hit=29
 Total runtime: 2.845 ms
(11 rows)