Hi,
I'm having trouble with indexes in PostgreSQL 7.1.3. Here is a
transcript:
==========================================================================
=
announce=# \d foo
Table "foo"
Attribute | Type | Modifier
-----------+--------------------------+----------
code | character varying(4) | not null
date | timestamp with time zone | not null
price | numeric(10,2) | not null
volume | integer | not null
other | boolean | not null
Index: foo_code_date
announce=# \d foo_code_date
Index "foo_code_date"
Attribute | Type
-----------+--------------------------
code | character varying(4)
date | timestamp with time zone
btree
announce=# explain select date from foo where date < '06/08/2001
23:59' and code = 'FOO' order by code, date limit 1;
NOTICE: QUERY PLAN:
Limit (cost=0.00..3.78 rows=1 width=20)
-> Index Scan using foo_code_date on foo
(cost=0.00..23996.55 rows=6355 width=20)
==========================================================================
=
So far, so good. The index is properly used, and the query is
fast. However, if I want to sort *descending* (which is really
what I want to do, to find the closest row with a date less than
a given date), then a full table scan is done:
==========================================================================
=
announce=# explain select date from foo where date < '06/08/2001
23:59' and code = 'FOO' order by code, date DESC limit 1;
NOTICE: QUERY PLAN:
Limit (cost=24397.98..24397.98 rows=1 width=20)
-> Sort (cost=24397.98..24397.98 rows=6355 width=20)
-> Index Scan using foo_code_date on foo
(cost=0.00..23996.55 rows=6355 width=20)
==========================================================================
=
What can I do to improve this?
Might it help if I reversed the order of the fields in the
index? There are only about 50 possible values for the 'code'
field. I'm not sure which order is better in this case. The
'date' field is well spread out over a year.
-Ken
