Re: Strange Index behavior

Hi Tom,
may be it was misunderstandable...

I meant that there is no rows with tstamp='2004.12.22 00:00'. Certainly
there are rows matching the range (00:00 - 23:59); from 13:00 to 23:00, but
not before 13:00.

The only difference betwen the two queries is this value. If the begining of
the range is a non existent value, the index is not used.
The point is that there are cases where a primary key index is not used -
even if the condition is formaly good. I was wonder what can be the reason
of this. What can I do in a different way to avoid this.

Thank you.

bye,
-- Csaba

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Wednesday, December 22, 2004 5:47 PM
To: Együd Csaba
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Strange Index behavior

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud(at)vnet(dot)hu> writes:
> # 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"

Why do you say there are no such rows, when the explain output clearly shows
there are 14523 of them? The plan shift looks fairly reasonable to me given
the large number of rows to be retrieved.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
No virus found in this incoming 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 incoming 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.

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