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@jabber.jarcomputers.com
