Strange Index behavior - Mailing list pgsql-general

Hi,
Is it a normal behavior that if I give a where clause with an existent index
key, then postgres uses the index, but if I give it a non existent value
than it refuses to use the index.

An example to make it more clear:

CREATE TABLE measured_1
(
  tstamp timestamp(0) NOT NULL,
  meterid int4 NOT NULL,
  pp numeric NOT NULL DEFAULT 0,
  pm numeric NOT NULL DEFAULT 0,
  qp numeric NOT NULL DEFAULT 0,
  qm numeric NOT NULL DEFAULT 0,
  status bit(5),
  CONSTRAINT measured_1_pkey PRIMARY KEY (tstamp, meterid)
) ;

--
-- The table contains rows with tstamp values from '2004.12.22 12:00' so the
00:00 row does not exists!!!
--

# explain analyze select meterid, tstamp, pp, pm, status from measured_1
where tstamp >= '2004.12.22 30:00' and tstamp <= '2004.12.22 23:59' order by
tstamp, meterid;
"Index Scan using measured_1_pkey on measured_1  (cost=0.00..5.34 rows=1
width=42) (actual time=0.000..111.000 rows=6016 loops=1)"
"  Index Cond: ((tstamp >= '2004-12-22 13:00:00'::timestamp without time
zone) AND (tstamp <= '2004-12-22 23:59:00'::timestamp without time zone))"
"Total runtime: 111.000 ms"
-- This is quite an acceptable result time

-- BUT!!!!

# explain analyze select meterid, tstamp, pp, pm, status from measured_1
where tstamp >= '2004.12.22 00:00' and tstamp <= '2004.12.22 23:59' order by
tstamp, meterid;
"Sort  (cost=2619.02..2622.78 rows=1505 width=42) (actual
time=1672.000..1682.000 rows=14523 loops=1)"
"  Sort Key: tstamp, meterid"
"  ->  Seq Scan on measured_1  (cost=0.00..2539.59 rows=1505 width=42)
(actual time=0.000..1292.000 rows=14523 loops=1)"
"        Filter: ((tstamp >= '2004-12-22 00:00:00'::timestamp without time
zone) AND (tstamp <= '2004-12-22 23:59:00'::timestamp without time zone))"
"Total runtime: 1802.000 ms"
-- this is definitely not acceptable.

Is this normal??? Or what do I wrong???

Thanks,

-- Csaba

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.3 - Release Date: 2004.12.21.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.3 - Release Date: 2004.12.21.