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: Select max(foo) and select count(*) optimization
Date: Mon, 05 Jan 2004 20:46:00 -0500
Message-ID: <m31xqdetnr.fsf@wolfe.cbbrowne.com> (raw)
References: <BC1F1F20.71370%siracusa@mindspring.com>
	<m31xqef8go.fsf@wolfe.cbbrowne.com>
	<1073348666.19994.3.camel@localhost.localdomain>

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.

> I'm guessing the payoff as well as real-world-utility of a max(xxx)
> optimization are much higher than a count(*) optimization tho

That's probably so.

In many cases, approximations, such as page counts, may be good
enough, and pray consider, that ("an approximation") is probably all
you were getting from the database systems that had an "optimization"
to store the count in a counter.
-- 
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"No, you  misunderstand. Microsoft asked  some hackers how  they could
make their system secure - the hackers replied "Turn it off.". So they
did." -- Anthony Ord



view thread (19+ messages)  latest in thread

Message-ID: <m31xqdetnr.fsf@wolfe.cbbrowne.com>
Permalink:  ../m31xqdetnr.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/m31xqdetnr.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: Select max(foo) and select count(*) optimization
  In-Reply-To: <m31xqdetnr.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