Re: Index on timestamp field, and now() - Mailing list pgsql-general

Denis Perchine <dyp@perchine.com> writes:
> webmailstation=> explain select * from queue where send_date > timestamp
> 'now';
> NOTICE:  QUERY PLAN:

> Seq Scan on queue  (cost=0.00..10114.06 rows=80834 width=190)

> EXPLAIN

> Although exact search uses index scan:

> webmailstation=> explain select * from queue where send_date = timestamp
> 'now';
> NOTICE:  QUERY PLAN:

> Index Scan using queue_senddate_key on queue  (cost=0.00..5.95 rows=1
> width=190)

> EXPLAIN

The second case proves that it's not a datatype or not-a-constant
problem.  I'd guess that the failure of the first case indicates you've
never ANALYZEd the table, and so you're getting a default selectivity
estimate for the inequality operator (which is way too high to allow an
indexscan).  If that's not so, what do you get from

    select * from pg_stats where tablename = 'queue';

            regards, tom lane