From: Greg Stark <gsstark@mit.edu>
To: Christopher Browne <cbbrowne@libertyrms.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: count(*) slow on large tables
Date: 03 Oct 2003 01:13:08 -0400
Message-ID: <87brsyrjiz.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <60brszcng5.fsf@dev6.int.libertyrms.info>
References: <20031002191547.GZ87525@rlx11.zapatec.com>
<20031002193905.GD18417@wolff.to>
<3F7C98B8.C892D0E5@nsd.ca>
<60brszcng5.fsf@dev6.int.libertyrms.info>
Christopher Browne <cbbrowne@libertyrms.info> writes:
> It would be very hairy to implement it correctly, and all this would
> cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;"
>
> If you had a single WHERE clause attached, you would have to revert to
> walking through the tuples looking for the ones that are live and
> committed, which is true for any DBMS.
Well it would be handy for a few other cases as well.
1 It would be useful for the case where you have a partial index with a
matching where clause. The optimizer already considers using such indexes
but it has to pay the cost of the tuple lookup, which is substantial.
2 It would be useful for the very common queries of the form
WHERE x IN (select id from foo where some_indexed_expression)
(Or the various equivalent forms including outer joins that test to see if
the matching record was found and don't retrieve any other columns in the
select list.)
3 It would be useful for many-many relationships where the intermediate table
has only the two primary key columns being joined. If you create a
multi-column index on the two columns it shouldn't need to look up the
tuple. This would be effectively be nearly equivalent to an "index organized
table".
4 It would be useful for just about all the referential integrity queries...
I don't mean to say this is definitely a good thing. The tradeoff in
complexity and time to maintain the index pages would be large. But don't
dismiss it as purely a count(*) optimization hack.
I know Oracle is capable of it and it can speed up your query a lot when you
remove that last unnecessary column from a join table allowing oracle to skip
the step of reading the table.
--
greg
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, cbbrowne@libertyrms.info
Subject: Re: count(*) slow on large tables
In-Reply-To: <87brsyrjiz.fsf@stark.dyndns.tv>
References: <20031002191547.GZ87525@rlx11.zapatec.com> <20031002193905.GD18417@wolff.to> <3F7C98B8.C892D0E5@nsd.ca> <60brszcng5.fsf@dev6.int.libertyrms.info> <87brsyrjiz.fsf@stark.dyndns.tv>
* 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