From: Jim C. Nasby <jim@nasby.net>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Dimitri Fontaine <dim@dalibo.com>
Cc: pgsql-performance@postgresql.org, Ioana Danes <ioanasoftware@yahoo.ca>
Subject: Re: Postgresql 8.1.4 - performance issues for select on
Date: Wed, 18 Oct 2006 17:35:51 -0500
Message-ID: <20061018223551.GD56874@nasby.net> (raw)
In-Reply-To: <1161210735.31645.277.camel@dogma.v10.wvs>
References: <20061018210201.56435.qmail@web55907.mail.re3.yahoo.com>
<200610182319.41920.dim@dalibo.com>
<1161207230.31645.266.camel@dogma.v10.wvs>
<20061018221046.GG85041@nasby.net>
<1161210735.31645.277.camel@dogma.v10.wvs>
On Wed, Oct 18, 2006 at 03:32:15PM -0700, Jeff Davis wrote:
> On Wed, 2006-10-18 at 17:10 -0500, Jim C. Nasby wrote:
> > Sorry, don't have the earlier part of this thread, but what about...
> >
> > SELECT greatest(max(a), max(b)) ...
> >
> > ?
>
> To fill you in, we're trying to get the max of a union (a view across
> two physical tables).
UNION or UNION ALL? You definitely don't want to do a plain UNION if you
can possibly avoid it.
> It can be done if you're creative with the query; I suggested a query
> that selected the max of the max()es of the individual tables. Your
> query could work too. However, the trick would be getting postgresql to
> recognize that it can transform "SELECT max(x) FROM foo" into that,
> where foo is a view of a union.
>
> If PostgreSQL could sort the result of a union by merging the results of
> two index scans, I think the problem would be solved. Is there something
> preventing this, or is it just something that needs to be added to the
> planner?
Hrm... it'd be worth trying the old ORDER BY ... LIMIT 1 trick just to
see if that worked in this case, but I don't have much hope for that.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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: jim@nasby.net, pgsql@j-davis.com, dim@dalibo.com, ioanasoftware@yahoo.ca
Subject: Re: Postgresql 8.1.4 - performance issues for select on
In-Reply-To: <20061018223551.GD56874@nasby.net>
* 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