Query planner refuses to use index - Mailing list pgsql-general

Hi there,

I know this subject has come up before many times, but I'm struggling for
hours with the following problem and none of the posts seem to have a
solution. I have a table with a good 13 million entries with

station_data=# \d speed
       Table "public.speed"
   Column   |   Type   | Modifiers
--------------+----------+-----------
 set_id       | smallint | not null
 rec_time   | abstime | not null
 wind_speed | smallint |
Indexes:
    "speed_pkey" primary key, btree (set_id, rec_time)

I use lots of queries of the form SELECT * FROM speed WHERE set_id=xxx AND
rec_time >=yyy where xxx is an integer and yyy is an abstime. At first, I
battled to get the query planner to use an index at all, even when forcing,
but

http://archives.postgresql.org/pgsql-general/2001-09/msg01120.php

suggested I need to use set_id=5::smallint. It works, but why is pg not
intelligent enough to figure out that the literal 5 and smallint are
compatible?

So I thought I had solved my problem, but then it still refused to use the
index, even though sequential scans are prohibitively expensive:

station_data=# EXPLAIN ANALYZE SELECT * FROM speed WHERE set_id=5::smallint
AND rec_time >= '1999/01/01'::abstime;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on speed  (cost=0.00..276640.28 rows=677372 width=8) (actual
time=14024.081..78236.525 rows=652389 loops=1)
   Filter: ((set_id = 5::smallint) AND (rec_time >= '1999-01-01
00:00:00+02'::abstime))
 Total runtime: 80156.330 ms

When I explicitly turn off sequential scans by issuing "SET enable_seqscan TO
OFF;", I get what I want:
station_data=# EXPLAIN ANALYZE SELECT * FROM speed WHERE set_id=5::smallint
AND rec_time >= '1999/01/01'::abstime;
                                                              QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using speed_pkey on speed  (cost=0.00..2009924.87 rows=677372
width=8) (actual time=50.070..5775.698 rows=652389 loops=1)
   Index Cond: ((set_id = 5::smallint) AND (rec_time >= '1999-01-01
00:00:00+02'::abstime))
 Total runtime: 8819.371 ms

which is 10 times faster. We're down to the last recommendation of section
11.8 in the documentation, so I increased the statistics gathered with "SET
default_statistics_target TO 50;", but that makes no difference either.

Am I left to disable seqscans for eternity (in which case may I file a bug) or
is there something else I might be missing?

--
Kilian Hagemann

Climate Systems Analysis Group
University of Cape Town
Republic of South Africa
Tel(w): ++27 21 650 2748