pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: count(*) slow on large tables
Date: Fri, 03 Oct 2003 07:37:07 -0400
Message-ID: <m3n0ciy2l8.fsf@wolfe.cbbrowne.com> (raw)
References: <20031002191547.GZ87525@rlx11.zapatec.com>
	<20031002193905.GD18417@wolff.to>
	<3F7C98B8.C892D0E5@nsd.ca>
	<60brszcng5.fsf@dev6.int.libertyrms.info>
	<20031002223313.GE87525@rlx11.zapatec.com>
	<m3vfr7f4z1.fsf@wolfe.cbbrowne.com>
	<20031003042754.GH87525@rlx11.zapatec.com>

Oops! dror@zapatec.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/;



view thread (30+ messages)  latest in thread

Message-ID: <m3n0ciy2l8.fsf@wolfe.cbbrowne.com>
Permalink:  ../m3n0ciy2l8.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/m3n0ciy2l8.fsf@wolfe.cbbrowne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: count(*) slow on large tables
  In-Reply-To: <m3n0ciy2l8.fsf@wolfe.cbbrowne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox