Re: Postgresql 8.1.4 - performance issues for select on - Mailing list pgsql-performance

From Jeff Davis
Subject Re: Postgresql 8.1.4 - performance issues for select on
Date
Msg-id 1161205648.31645.262.camel@dogma.v10.wvs
Whole thread Raw
In response to Postgresql 8.1.4 - performance issues for select on view using max  (Ioana Danes <ioanasoftware@yahoo.ca>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: Ioana Danes
Date:
Subject: Re: Postgresql 8.1.4 - performance issues for select on view using max
Next
From: "jungmin shin"
Date:
Subject: UDF and cache