From: | Christopher Browne <cbbrowne(at)acm(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: count(*) slow on large tables |
Date: | 2003-10-03 11:37:07 |
Message-ID: | m3n0ciy2l8.fsf@wolfe.cbbrowne.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
Oops! dror(at)zapatec(dot)com (Dror Matalon) was seen spray-painting on a wall:
> I smell a religious war in the aii:-).
> Can you go several days in a row without doing select count(*) on any
> of your tables?
I would be more likely, personally, to run "VACUUM VERBOSE ANALYZE",
which has useful side-effects :-).
> I suspect that this is somewhat a domain specific issue. In some
> areas you don't need to know the total number of rows in your
> tables, in others you do.
"Relationship tables," which don't contain data in their own right,
but which, instead, link together records in other tables, are likely
to have particularly useless COUNT(*)'s.
> I also suspect that you're right, that end user applications don't
> use this information as often as DBAs would. On the other hand, it
> seems whenever you want to optimize your app (something relevant to
> this list), one of the things you do need to know is the number of
> rows in your table.
Ah, but in the case of optimization, there's little need for
"transactionally safe, MVCC-managed, known-to-be-exact" values.
Approximations are plenty good enough to get the right plan.
Furthermore, it's not the number of rows that is most important when
optimizing queries; the number of pages are more relevant to the
matter, as that's what the database is slinging around.
--
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://www3.sympatico.ca/cbbrowne/multiplexor.html
Rules of the Evil Overlord #134. "If I am escaping in a large truck
and the hero is pursuing me in a small Italian sports car, I will not
wait for the hero to pull up along side of me and try to force him off
the road as he attempts to climb aboard. Instead I will slam on the
brakes when he's directly behind me. (A rudimentary knowledge of
physics can prove quite useful.)" <http://www.eviloverlord.com/>
From | Date | Subject | |
---|---|---|---|
Next Message | Max Jacob | 2003-10-03 11:51:59 | calling functions through a "pointer" |
Previous Message | cad0022 | 2003-10-03 11:09:31 | How to avoid users from viewing functions code |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff | 2003-10-03 12:36:42 | Re: count(*) slow on large tables |
Previous Message | Richard Huxton | 2003-10-03 10:10:25 | Re: A Basic Question |