From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: pgsql-performance@postgresql.org
Subject: Re: Select max(foo) and select count(*) optimization
Date: Tue, 6 Jan 2004 12:01:53 +0530
Message-ID: <200401061201.53978.shridhar_daithankar@persistent.co.in> (raw)
In-Reply-To: <m31xqdetnr.fsf@wolfe.cbbrowne.com>
References: <BC1F1F20.71370%siracusa@mindspring.com>
<1073348666.19994.3.camel@localhost.localdomain>
<m31xqdetnr.fsf@wolfe.cbbrowne.com>
On Tuesday 06 January 2004 07:16, Christopher Browne wrote:
> Martha Stewart called it a Good Thing when paul@tuckfield.com (Paul
Tuckfield) wrote:
> > Not that I'm offering to do the porgramming mind you, :) but . .
> >
> > In the case of select count(*), one optimization is to do a scan of the
> > primary key, not the table itself, if the table has a primary key. In a
> > certain commercial, lesser database, this is called an "index fast full
> > scan". It would be important to scan the index in physical order
> > (sequential physical IO) and not in key order (random physical IO)
>
> The problem is that this "optimization" does not actually work. The
> index does not contain transaction visibility information, so you have
> to go to the pages of tuples in order to determine if any given tuple
> is visible.
It was rejected as an idea to add transaction visibility information to
indexes. The time I proposed, my idea was to vacuum tuples on page level
while postgresql pushes buffers out of shared cache. If indexes had
visibility information, they could be cleaned out of order than heap tuples.
This wouldn't have eliminated vacuum entirely but at least frequently hit data
would be clean.
But it was rejected because of associated overhead.
Just thought worh a mention..
Shridhar
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: shridhar_daithankar@persistent.co.in
Subject: Re: Select max(foo) and select count(*) optimization
In-Reply-To: <200401061201.53978.shridhar_daithankar@persistent.co.in>
* 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