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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
