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 <[EMAIL PROTECTED]> 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.
pgphSPtQkjdZw.pgp
Description: PGP signature
