Date: Wed, 10 Aug 2005 01:29:58 +0200
From: dracula007@atlas.cz
Reply-To: dracula007@atlas.cz
Message-ID: <196456155.20050810012958@karneval.cz>
To: pgsql-sql@postgresql.org
Subject: Re: **SPAM** Faster count(*)?
In-Reply-To: <000101c59d33$36a4a560$9b00015a@osl.com>
References: <000101c59d33$36a4a560$9b00015a@osl.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I believe running count(*) means fulltable scan, and there's no way
to do it without it. But what about some "intermediate" table, with
the necessary counts?

That means to create a table with values (counts) you need, and on
every insert/delete/update increment or decrement the appropriate
values. This way you won't need the count(*) query anymore, and the
performance should be much better.

t.v.

> Salve.

> I understand from various web searches and so on that PostgreSQL's MVCC
> mechanism makes it very hard to use indices or table metadata to optimise
> count(*).  Is there a better way to guess the "approximate size" of a table?

> I'm trying to write a trigger that fires on insert and performs some
> maintenance (collapsing overlapping boxes into a single large box,
> specifically) as the table grows.  My initial attempt involved count(*) and,
> as the number of pages in the table grew, that trigger bogged down the
> database.

> Any thoughts?