Why is index disregarded when querying a timestamp?
От | Markus Wollny |
---|---|
Тема | Why is index disregarded when querying a timestamp? |
Дата | |
Msg-id | 2266D0630E43BB4290742247C8910575014CE2C9@dozer.computec.de обсуждение исходный текст |
Ответы |
Re: Why is index disregarded when querying a timestamp?
Re: Why is index disregarded when querying a timestamp? |
Список | 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
В списке pgsql-general по дате отправления: