Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why is index disregarded when querying a timestamp?

Why is index disregarded when querying a timestamp?

From
"Markus Wollny"
Date:
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

Re: Why is index disregarded when querying a timestamp?

From
Martijn van Oosterhout
Date:
On Wed, Jul 03, 2002 at 03:39:35PM +0200, Markus Wollny wrote:
>     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

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

HTH,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Re: Why is index disregarded when querying a timestamp?

From
Tom Lane
Date:
"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