On Tuesday 12 May 2009 12:55:14 Scott Marlowe wrote: > On Tue, May 12, 2009 at 3:00 AM, Евгений Василев > > <evasilev@xxxxxxxxxxxxxxxx> wrote: > > I have the following table: > > > > CREATE TABLE "temp".tmp_135528 > > ( > > id integer NOT NULL, > > prid integer, > > group_id integer, > > iinv integer, > > oinv integer, > > isum numeric, > > osum numeric, > > idate timestamp without time zone, > > odate timestamp without time zone, > > CONSTRAINT t_135528_pk PRIMARY KEY (id) > > ) > > WITH (OIDS=FALSE); > > > > With index: > > > > CREATE INDEX t_135528 > > ON "temp".tmp_135528 > > USING btree > > (idate, group_id, osum, oinv); > > > > When the following query is executed the index is not used: > > > > EXPLAIN SELECT id, osum > > FROM temp.tmp_135528 > > WHERE idate <= '2007-05-17 00:00:00'::timestamp > > AND group_id = '13' > > AND osum <= '19654.45328' > > AND oinv = -1 > > > > QUERY PLAN > > ------------------------------------------------------------------------- > >------------------------------------------------------------------ Seq > > Scan on tmp_135528 (cost=0.00..7022.36 rows=1166 width=11) > > Filter: ((idate <= '2007-05-17 00:00:00'::timestamp without time zone) > > AND (osum <= 19654.45328) AND (group_id = 13) AND (oinv = (-1))) > > (2 rows) > > > > When > > "idate <= '2007-05-17 00:00:00'::timestamp" > > is changed to > > "idate >= '2007-05-17 00:00:00'::timestamp" > > or > > "idate = '2007-05-17 00:00:00'::timestamp" > > then the index is used: > > > > EXPLAIN SELECT id, osum > > FROM temp.tmp_135528 > > WHERE idate >= '2007-05-17 00:00:00'::timestamp > > AND group_id = '13' > > AND osum <= '19654.45328' > > AND oinv = -1; > > QUERY PLAN > > ------------------------------------------------------------------------- > >---------------------------------------------------------------------- > > Index Scan using t_135528 on tmp_135528 (cost=0.00..462.61 rows=47 > > width=11) Index Cond: ((idate >= '2007-05-17 00:00:00'::timestamp without > > time zone) AND (group_id = 13) AND (osum <= 19654.45328) AND (oinv = > > (-1))) (2 rows) > > > > Why I cannot use the index in <= comparison on timestamp ? > > You can. But in this instance one query is returning 47 rows while > the other is returning 1166 rows (or the planner thinks it is). > There's a switchover point where it's cheaper to seq scan. You can > adjust this point up and down by adjusting various costs parameters. > random_page_cost is commonly lowered to the 1.5 to 2.0 range, and > effective_cache_size is normally set higher, to match the cache in the > kernel plus the shared_buffer size. Thank you this worked like a charm. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
