Subject: Re: Postgresql 8.1.4 - performance issues for select on
From: Jeff Davis <pgsql@j-davis.com>
To: Ioana Danes <ioanasoftware@yahoo.ca>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com>
References: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com>
Content-Type: text/plain
Date: Wed, 18 Oct 2006 14:07:28 -0700
Message-Id: <1161205648.31645.262.camel@dogma.v10.wvs>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2006-10-18 at 15:51 -0400, Ioana Danes wrote:
> Hi everyone,
> Testing some selects I know we have in the application
> I got into a scenario where my plan does not work
> without doing code change. This scenario is:
> 
> select max(transid) from alltransaction;
> 
> because the planner does not use the existent indexes
> on the 2 new tables: public.transaction and
> archive.transaction
> 

First, the query is expanded into something like (I'm being inexact
here):

SELECT max(transid) FROM (SELECT * FROM public.transaction UNION SELECT
* FROM archive.transaction);

PostgreSQL added a hack to the max() aggregate so that, in the simple
case, it can recognize that what it really wants to do is use the index.
Using the index for an aggregate only works in special cases, like min()
and max(). What PostgreSQL actually does is to transform a query from:

SELECT max(value) FROM sometable;

Into:

SELECT value FROM sometable ORDER BY value DESC LIMIT 1;

In your case, it would need to transform the query into something more
like:

SELECT max(transid) FROM (
  SELECT transid FROM (
    SELECT transid FROM public.transaction ORDER BY transid DESC
      LIMIT 1
  ) t1 
  UNION 
  SELECT transid FROM (
    SELECT transid FROM archive.transaction ORDER BY transid DESC
      LIMIT 1
  ) t2 
) t;

The reason for that is because PostgreSQL (apparently) isn't smart
enough to do a mergesort on the two indexes to sort the result of the
UNION. At least, I can't get PostgreSQL to sort over two UNIONed tables
using an index; perhaps I'm missing it.

Regards,
	Jeff Davis