[postgis-users] OT Understanding slow queries

[Bborie Park]

On 08/25/2011 10:56 AM, fork wrote:
> Ben Madin<lists<at>  remoteinformation.com.au>  writes:
>
>> does this just apply to count(*), or is count(id) just as bad? I was
> originally a MySQL user and count(*)
>> could be very efficient there.
>
> My understanding is that Postgres does not keep record length for any of its
> tables internally, so there is no way to get a count without seq scanning.  The
> trade off is that inserts deletes are faster but a very common query is much
> slower.  I don't know if the planner could benefit in any way from the count
> being available, though.
>
> The lists say to use a trigger on inserts and deletes to update a metadata table
> if you really do need to know how many elements are in it exactly, but that is a
> far less frequent need than you may think (for example an EXISTS can get you an
> answer to "are there any records" more quickly than a count(*)).  I think you
> can do a quick and rough estimate by doing some math with the table size on
> disk, but I never have.
>
> It is unfortunate that the first (rather lame) "benchmark" anyone tries to do
> with a new database is run "select count(*) from x" -- I am sure lots of people
> have been turned off from PG because this particular query is slow compared to
> MySQL.
>
> (MySQL always wins in the more hollywood competitions against PG, but fails in
> the long run, IMHO)
>

I think PostgreSQL 9.2 will have index-only scans that should improve 
the performance of SELECT count(*) queries.

http://rhaas.blogspot.com/2011/08/index-only-scans-now-theres-patch.html

Granted, this is in trunk so it won't help for any production databases.

-bborie

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu