From: John Siracusa <siracusa@mindspring.com>
To: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Select max(foo) and select count(*) optimization
Date: Mon, 05 Jan 2004 14:03:12 -0500
Message-ID: <BC1F1F20.71370%siracusa@mindspring.com> (raw)
Speaking of special cases (well, I was on the admin list) there are two
kinds that would really benefit from some attention.
1. The query "select max(foo) from bar" where the column foo has an index.
Aren't indexes ordered? If not, an "ordered index" would be useful in this
situation so that this query, rather than doing a sequential scan of the
whole table, would just "ask the index" for the max value and return nearly
instantly.
2. The query "select count(*) from bar" Surely the total number of rows in
a table is kept somewhere convenient. If not, it would be nice if it could
be :) Again, rather than doing a sequential scan of the entire table, this
type of query could return instantly.
I believe MySQL does both of these optimizations (which are probably a lot
easier in that product, given its data storage system). These were the
first areas where I noticed a big performance difference between MySQL and
Postgres.
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.
-John
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: siracusa@mindspring.com
Subject: Re: Select max(foo) and select count(*) optimization
In-Reply-To: <BC1F1F20.71370%siracusa@mindspring.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