[postgis-users] OT Understanding slow queries

[Bergenroth, Brandon]

> there is no way to get a count without seq scanning.  

True for a single table, in the given example, there was a join.

A possible outcome could have been a seq scan on the smaller table and a nested loop index lookup on the bigger table.  The optimizer must have thought this was more expensive than the full scan with hash join.



> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-
> users-bounces at postgis.refractions.net] On Behalf Of fork
> Sent: Thursday, August 25, 2011 1:56 PM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] OT Understanding slow queries
> 
> 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)
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users