[postgis-users] OT Understanding slow queries

[Stephen Woodbridge]

On 8/25/2011 1:21 PM, Ben Madin wrote:
> Steve,
>
> 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.
>

Best way to evaluate this is with explain. But yes both have the same 
full table scan.

MySQL maintains row count as one of its tables stats so it just pulls 
that from the stats metadata. If you just want an approximate row count, 
there are some status based on the last analyze. If you use pgadmin3 you 
can see in the table properties:

Rows (estimated)	1957620	

This is one of the largest hurdles for MySQL users moving over to 
postgresql because it is so obvious and in your face. But the things I 
can do today with postgresql and with postgis, I could never do in MySQL.

-Steve

[snip history]