Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Denis" <denis@next2me.com>, pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Tue, 8 Apr 2003 14:52:40 -0700
User-Agent: KMail/1.4.3
References: <b6v9mv$1h8q$1@news.hub.org>
In-Reply-To: <b6v9mv$1h8q$1@news.hub.org>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304081452.40424.josh@agliodbs.com>

Dennis,

> I'm running into a quite puzzling simple example where the index I've
> created on a fairly big table (465K entries) is not used, against all com=
mon
> sense expectations:
> The query I am trying to do (fast) is:
>=20
> select count(*) from addresses;

PostgreSQL is currently unable to use indexes on aggregate queries.   This =
is=20
because of two factors:
1) MVCC means that the number of rows must be recalculated for each=20
connection's current transaction, and cannot be "cached" anywhere by the=20
database system;
2) Our extensible model of user-defined aggregates means that each aggregat=
e=20
is a "black box" whose internal operations are invisible to the planner.

This is a known performance issue for Postgres, and I believe that a couple=
 of=20
people on Hackers are looking at modifying aggregate implementation for 8.0=
=20
to use appropriate available indexes, at least for MIN, MAX and COUNT.  Unt=
il=20
then, you will need to either put up with the delay, or create a=20
trigger-driven aggregates caching table.

If you are trying to do a correlated count, like "SELECT type, count(*) fro=
m=20
aggregates GROUP BY type", Tom Lane has already added a hash-aggregates=20
structure in the 7.4 source that will speed this type of query up=20
considerably for systems with lots of RAM.

(PS: in the future, please stick to posting questions to one list at a time=
,=20
thanks)

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco