[postgis-users] OT Understanding slow queries

[fork]

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)