Re: Why is index disregarded when querying a timestamp?

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.