Re: [PERFORM] Timestamp index not used in some cases
On Tuesday 12 May 2009 12:55:14 Scott Marlowe wrote: > On Tue, May 12, 2009 at 3:00 AM, Евгений Василев > > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Timestamp index not used in some cases
On Tue, May 12, 2009 at 3:00 AM, Евгений Василев 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Timestamp index not used in some cases
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 ? Best regards, Evgeni Vasilev JAR Computers IT Department jabber id: evasi...@jabber.jarcomputers.com
