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