Greg Stark kirjutas N, 04.12.2003 kell 19:55: > I have an idea for what I think may be a very simple optimization for postgres > to make. I would like to try my hand at implementing it, but the last time I > tried I apparently started off in the wrong direction. > > In the following query, the sort step is completely unnecessary. The order is > already guaranteed by the index: > > > test=# create table test (a integer,b integer); > CREATE TABLE > test=# create index test_i on test(a,b); > CREATE INDEX > test=# explain select * from test where a=1 order by b; > QUERY PLAN > ------------------------------------------------------------------------- > Sort (cost=5.95..5.96 rows=6 width=8) > Sort Key: b > -> Index Scan using test_i on test (cost=0.00..5.87 rows=6 width=8) > Index Cond: (a = 1) > (4 rows) > > At what point in the process would it make sense to check for this?
Why not rewrite it as: test=# explain select * from test where a=1 order by a,b; --------------------------------------------------------------------- Index Scan using test_i on test (cost=0.00..17.07 rows=5 width=8) Index Cond: (a = 1) (2 rows) > Where should I be looking in the code? Try to find where the modified query is tested for. It's probably be inside the optimizer, as index scan + no sort is not always faster than seq scan + sort, as shown by the same query after vacuum analyze (on an empty table) hannu=# vacuum analyze test; VACUUM hannu=# explain select * from test where a=1 order by a,b; QUERY PLAN ----------------------------------------------------------- Sort (cost=0.01..0.02 rows=1 width=8) Sort Key: a, b -> Seq Scan on test (cost=0.00..0.00 rows=1 width=8) Filter: (a = 1) (4 rows) --------------- Hannu ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])