From: | Евгений Василев <evasilev(at)jarcomputers(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Timestamp index not used in some cases |
Date: | 2009-05-12 09:00:08 |
Message-ID: | 200905121200.09337.evasilev@jarcomputers.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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: evasilev(at)jabber(dot)jarcomputers(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Laurent Laborde | 2009-05-12 09:23:46 | Re: What is the most optimal config parameters to keep stable write TPS ?.. |
Previous Message | Dimitri Fontaine | 2009-05-12 08:44:09 | Re: Any better plan for this query?.. |