Why is index disregarded when querying a timestamp?

Hi!

I would like to know why this query here doesn't use the index on
ct_com_board_message....

analyze select MESSAGE.BOARD_ID
, MESSAGE.THREAD_ID
, MESSAGE.MESSAGE_ID
, MESSAGE.TITLE
, MESSAGE.USER_ID
, MESSAGE.USER_LOGIN
as LOGIN
, MESSAGE.USER_STATUS
as STATUS
, MESSAGE.USER_RIGHTS
as RIGHTS
, to_char(MESSAGE.CREATED,'DD.MM.YY
hh24:mi') as DATUM
, MESSAGE.COUNT_REPLY

, '0' as TFUID


from CT_COM_BOARD_MESSAGE MESSAGE
where (0=0)
and (MESSAGE.CREATED >= CURRENT_TIMESTAMP-1)
LIMIT 500

Limit (cost=0.00..248.93 rows=500 width=134) (actual
time=311.82..19709.48 rows=500 loops=1)
-> Seq Scan on ct_com_board_message message (cost=0.00..60122.07
rows=120761 width=134) (actual time=311.81..19707.81 rows=501 loops=1)
Total runtime: 19710.88 msec

whereas this one here does:

analyze select MESSAGE.BOARD_ID
, MESSAGE.THREAD_ID
, MESSAGE.MESSAGE_ID
, MESSAGE.TITLE
, MESSAGE.USER_ID
, MESSAGE.USER_LOGIN
as LOGIN
, MESSAGE.USER_STATUS
as STATUS
, MESSAGE.USER_RIGHTS
as RIGHTS
, to_char(MESSAGE.CREATED,'DD.MM.YY
hh24:mi') as DATUM
, MESSAGE.COUNT_REPLY

, '0' as TFUID


from CT_COM_BOARD_MESSAGE MESSAGE
where (0=0)
order by message.created desc
LIMIT 500

Limit (cost=0.00..1630.99 rows=500 width=134) (actual time=0.81..35.28
rows=500 loops=1)
-> Index Scan Backward using idx_bm_created on ct_com_board_message
message (cost=0.00..1181759.65 rows=362283 width=134) (actual
time=0.80..33.83 rows=501 loops=1)
Total runtime: 41.69 msec

It seems like if I compare timestamp in the query, it wouldn't use the
index - why is that so?

Regards,

Markus

On Wed, Jul 03, 2002 at 03:39:35PM +0200, Markus Wollny wrote:
> where (0=0)
> and (MESSAGE.CREATED >= CURRENT_TIMESTAMP-1)
> LIMIT 500
>
> Limit (cost=0.00..248.93 rows=500 width=134) (actual
> time=311.82..19709.48 rows=500 loops=1)
> -> Seq Scan on ct_com_board_message message (cost=0.00..60122.07
> rows=120761 width=134) (actual time=311.81..19707.81 rows=501 loops=1)
> Total runtime: 19710.88 msec

Note that the estimator has wildly overestimated the number of rows that
would be returned by your where condition by a factor of 250 or so. Have you
run ANALYSE over the table recently. If so, could you post the statistics
gathered for that column.

That, and the fact that the query below is sorted by message.created, which
really encourages the use of the index (index scan much cheaper than table
sort).

> from CT_COM_BOARD_MESSAGE MESSAGE
> where (0=0)
> order by message.created desc
> LIMIT 500
>
> Limit (cost=0.00..1630.99 rows=500 width=134) (actual time=0.81..35.28
> rows=500 loops=1)
> -> Index Scan Backward using idx_bm_created on ct_com_board_message
> message (cost=0.00..1181759.65 rows=362283 width=134) (actual
> time=0.80..33.83 rows=501 loops=1)
> Total runtime: 41.69 msec

HTH,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

"Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de> writes:
> I would like to know why this query here doesn't use the index on
> ct_com_board_message....

> from CT_COM_BOARD_MESSAGE MESSAGE
> where (0=0)
> and (MESSAGE.CREATED >= CURRENT_TIMESTAMP-1)
> LIMIT 500

That's not considered an indexable WHERE clause in current releases
(7.3 will fix this). See past discussions about how to hide the
current_timestamp call in a function marked "isCachable".

regards, tom lane