On Mon, Jun 21, 2010 at 5:19 AM, Thom Brown <thombr...@gmail.com> wrote:
> I can't answer this, but is anyone else able to provide Alexander some
> feedback?
It seems like you can get more or less the same benefit from a
multicolumn btree index. On my system, with the individual btree
indices, the query ran in 7625 ms; with an additional index on (v1,
v2, v3), it ran in 94 ms. I didn't get the same plans as Alexander
did, though, so it may not really be apples to apples. See attached
session trace.
Having said that, I'm very interested in hearing what other ideas
people have for using indices to speed up "ORDER BY" operations.
Currently, we support only "ORDER BY <indexed-value>". KNNGIST will
allow "ORDER BY <indexed-value> <op> <constant>", but why stop there?
In theory, an index might know how to order the data by any arbitrary
expression the user might happen to enter. If the user asks for
"ORDER BY (<indexed-value> <op1> <constan1t>) <op2> <constan2t>", who
is to say that we can't use an index scan to get that ordering
quickly? (As a trivial case, suppose both ops are +, but there could
easily be more interesting ones.) Or what about "ORDER BY
somefunc(<indexed-value>)"? The trouble is that it's hard to think of
a way of teaching the planner about these cases without hard-coding
lots and lots of special-case kludges into the planner. Still, if
someone has a clever idea...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
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=#
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
