From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Együd Csaba <csegyud(at)vnet(dot)hu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Strange Index behavior |
Date: | 2004-12-22 17:08:05 |
Message-ID: | 20041222170805.GE29278@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | vinita bansal | 2004-12-22 17:09:26 | Re: default index created for primary key |
Previous Message | Rick Morris | 2004-12-22 17:05:34 | Re: nice work on the new site |