Timestamp index not used in some cases

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

Responses

Browse pgsql-performance by date

  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?..