pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rod Taylor <pg@rbt.ca>
To: siracusa@mindspring.com
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Select max(foo) and select count(*) optimization
Date: Mon, 05 Jan 2004 14:52:06 -0500
Message-ID: <1073332325.8958.8.camel@jester> (raw)
In-Reply-To: <BC1F1F20.71370%siracusa@mindspring.com>
References: <BC1F1F20.71370%siracusa@mindspring.com>

> Especially with very large tables, hearing the disks grind as Postgres scans
> every single row in order to determine the number of rows in a table or the
> max value of a column (even a primary key created from a sequence) is pretty
> painful.  If the implementation is not too horrendous, this is an area where
> an orders-of-magnitude performance increase can  be had.

Actually, it's very painful. For MySQL, they've accepted the concurrancy
hit in order to accomplish it -- PostgreSQL would require a more subtle
approach.

Anyway, with Rules you can force this:

ON INSERT UPDATE counter SET tablecount = tablecount + 1;

ON DELETE UPDATE counter SET tablecount = tablecount - 1;


You need to create a table "counter" with a single row that will keep
track of the number of rows in the table. Just remember, you've now
serialized all writes to the table, but in your situation it may be
worth while.

max(foo) optimizations requires an extension to the aggregates system.
It will likely happen within a few releases. A work around can be
accomplished today through the use of LIMIT and ORDER BY.




view thread (19+ messages)  latest in thread

Message-ID: <1073332325.8958.8.camel@jester>
Permalink:  ../1073332325.8958.8.camel@jester/
Also on:    postgresql.org/message-id/1073332325.8958.8.camel@jester

 · 

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: pg@rbt.ca, siracusa@mindspring.com
  Subject: Re: Select max(foo) and select count(*) optimization
  In-Reply-To: <1073332325.8958.8.camel@jester>

* 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