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