Re: [SQLObject] PostgreSQL: costly count

[J-P L. <jp...@si...>]

This is a common problem with most db's.  select count(id) is usually 
the prefered way to do it. (Although I haven't tested the speed diff).


Ian Bicking wrote:

> On Dec 4, 2003, at 9:10 AM, Frank Barknecht wrote:
>
>> Hallo,
>>
>> getting at the lenght of a result with SQLResult's count() finction
>> issues a "select count(*) from X". In my PostgreSQL table, this is
>> quite expensive, and I am wondering, why. Analyzing the query shows,
>> that no index is used:
>
>
>> => explain SELECT count(*) from normal;
>> NOTICE:  QUERY PLAN:
>
>
> I don't think an index could be used in this instance.  You aren't 
> finding specific rows, you are finding how many total rows there are.
>
> It's kind of dumb that PostgreSQL can't figure this out more quickly, 
> since it must have internal records of the table's size already 
> calculated.  I know MySQL has specific optimizations for this case 
> ("SELECT count(*) FROM blah").  If you figure something out, be sure 
> to get back to us, maybe there's something that can be added to 
> SQLObject to fix this.