Re: Strange Index behavior

On Wed, Dec 22, 2004 at 03:09:08PM +0100, Együd Csaba wrote:
> Hi,
> Is it a normal behavior that if I give a where clause with an existent index
> key, then postgres uses the index, but if I give it a non existent value
> than it refuses to use the index.

Whether the value exists is irrelevent. What matters is the number of
rows expected to be returned. Notice:

> "Index Scan using measured_1_pkey on measured_1 (cost=0.00..5.34 rows=1
> width=42) (actual time=0.000..111.000 rows=6016 loops=1)"
> " Index Cond: ((tstamp >= '2004-12-22 13:00:00'::timestamp without time
> zone) AND (tstamp <= '2004-12-22 23:59:00'::timestamp without time zone))"
> "Total runtime: 111.000 ms"
> -- This is quite an acceptable result time

> " -> Seq Scan on measured_1 (cost=0.00..2539.59 rows=1505 width=42)
> (actual time=0.000..1292.000 rows=14523 loops=1)"
> " Filter: ((tstamp >= '2004-12-22 00:00:00'::timestamp without time
> zone) AND (tstamp <= '2004-12-22 23:59:00'::timestamp without time zone))"
> "Total runtime: 1802.000 ms"
> -- this is definitely not acceptable.

Note how the first one expected to return only one value, so an index
in a good choice. The second expected 1500 matches, so it decided a seq
scan would be faster.

Maybe you need to review your value for random_page_cost and maybe an
ANALYZE would be good too... Look at the effects of enabling and
disabling index scans.

Hope this helps,
--
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.