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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
