multicolumn-btree-index.txt

rhaas=# create table test (id serial primary key, v1 double precision, v2 double precision, v3 double precision);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
rhaas=# insert into test (v1,v2,v3) (select random()*1000, random()*1000, random()*1000 from generate_series(1,10000000,1));
INSERT 0 10000000
rhaas=# 
rhaas=# select * from test where v1 between 480 and 500 and v2 between 480 and 500 order by v3 limit 10;
   id    |        v1        |        v2        |         v3         
---------+------------------+------------------+--------------------
 7707160 | 499.468160793185 | 497.105565853417 | 0.0262199901044369
 9836934 | 488.509620074183 | 489.591513760388 | 0.0412175431847572
 8299674 | 488.991918507963 | 494.098918512464 |  0.524408183991909
 4962322 | 484.455766621977 | 496.633755043149 |  0.686612911522388
 5253466 | 493.753412738442 | 481.965465471148 |  0.731946900486946
 3642389 |  496.36858003214 | 483.764411881566 |  0.750890467315912
 3332916 | 486.504513770342 | 492.682197596878 |  0.930964481085539
 3063189 | 483.532963320613 | 481.065005529672 |    1.0284804739058
 6368341 | 493.383017368615 | 497.755419462919 |   1.20219821110368
 1587774 |  496.25625833869 | 484.364923555404 |   1.58307375386357
(10 rows)

rhaas=# explain analyze select * from test where v1 between 480 and 500 and v2 between
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=273617.93..273617.96 rows=10 width=28) (actual time=7625.039..7625.042 rows=10 loops=1)
   ->  Sort  (cost=273617.93..273627.92 rows=3995 width=28) (actual time=7625.037..7625.039 rows=10 loops=1)
         Sort Key: v3
         Sort Method:  top-N heapsort  Memory: 25kB
         ->  Seq Scan on test  (cost=0.00..273531.60 rows=3995 width=28) (actual time=5.934..7620.583 rows=3995 loops=1)
               Filter: ((v1 >= 480::double precision) AND (v1 <= 500::double precision) AND (v2 >= 480::double precision) AND (v2 <= 500::double precision))
 Total runtime: 7625.101 ms
(7 rows)

rhaas=# create index x on test(v1,v2,v3);
CREATE INDEX
rhaas=# explain analyze select * from test where v1 between 480 and 500 and v2 between 480 and 500 order by v3 limit 10;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=19892.64..19892.66 rows=10 width=28) (actual time=73.735..73.738 rows=10 loops=1)
   ->  Sort  (cost=19892.64..19902.62 rows=3995 width=28) (actual time=73.733..73.735 rows=10 loops=1)
         Sort Key: v3
         Sort Method:  top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on test  (cost=6850.60..19806.31 rows=3995 width=28) (actual time=34.682..72.238 rows=3995 loops=1)
               Recheck Cond: ((v1 >= 480::double precision) AND (v1 <= 500::double precision) AND (v2 >= 480::double precision) AND (v2 <= 500::double precision))
               ->  Bitmap Index Scan on x  (cost=0.00..6849.60 rows=3995 width=0) (actual time=34.029..34.029 rows=3995 loops=1)
                     Index Cond: ((v1 >= 480::double precision) AND (v1 <= 500::double precision) AND (v2 >= 480::double precision) AND (v2 <= 500::double precision))
 Total runtime: 93.916 ms
(9 rows)

rhaas=#