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 500Limit (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 500Limit (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@svana.org> http://svana.org/kleptog/
Show quoted text
There are 10 kinds of people in the world, those that can do binary
arithmetic and those that can't.
"Markus Wollny" <Markus.Wollny@computec.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