"P. Dwayne Miller" wrote:
>
> I think 4 seconds is way too long to return the results. And NULLs in a
> column should not change the answer. It seems logical that even a sequential
> scan of an index would be much faster than a scan of the table (in this case
> the record size is fairly large).
>
> I'm trying to optimize queries that are being ported from another DBMS, where
> the same query above returns in 10s of milliseconds. 4 secs is simply too
> long. So I'm looking for a way to do it faster.
>
> MS SQL Server docs have optimization hints for such a query and using the
> 'count(requestnumber)' syntax, where requestnumber is an indexed field, was
> suggested.
Could you possibly mean "select(distinct requestnumber)" ?
If the performance of count(xxx) is critical for your app, I suggest
keeping the
counts in a separate table with a trigger. Postgres can not optimise to
use
indexes _only_ , as indexes don't keep commit information - it must be
checked
from data heap.
---------------
Hannu