Why is index disregarded when querying a timestamp?

From: "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Why is index disregarded when querying a timestamp?
Date: 2002-07-03 13:39:35
Message-ID: 2266D0630E43BB4290742247C8910575014CE2C9@dozer.computec.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Markus Wollny 2002-07-03 14:00:27 Re: One source of constant annoyance identified
Previous Message Markus Wollny 2002-07-03 13:23:39 Re: One source of constant annoyance identified