Re: Query planner refuses to use index

On Thu, Jul 21, 2005 at 05:45:55PM +0200, Kilian Hagemann wrote:
> 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

Firstly, you havn't indicated what version of postgresql you're using.

<snip>

> 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?

Later versions do, which is why the version you are using is relevent.

<snip>

The EXPLAIN ANALYZE below indicates that the system thinks the index
scan will take 10 times longer than the seqscan whereas it doesn't
actually take that long. Please tell us if you've adjusted
random_page_cost at all and what to.

Are the values matching your criteria clustered on disk? Old versions
of Postgres didn't take this into account, although that would be very
old then...

Hope this helps,

> 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
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.