From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Indexing UNIONs
Date: Mon, 15 Jul 2002 17:42:51 -0700 (PDT)
Message-ID: <20020715173335.R43416-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200207151731.24567.josh@agliodbs.com>
On Mon, 15 Jul 2002, Josh Berkus wrote:
> Stephan,
>
> > We had a discussion recently on -general about this. Right now the
> > planner won't push the conditions down into the arms of the union because
> > noone's been sure under what conditions the optimization is safe.
>
> So, if performance is horrible with the view, I should use a dummy table to
> hold the Unioned data and index that instead?
Possibly.
> I can understand the difficultyof optimization. However, the example I
> supplied is the simplest of unions, and the two Seq Scans do seem to be
> proceeding against each table seperately. I think for very simple Unions
> (i.e. no grouping, no filtering within subqueries, etc.) that index usage
> would be reasonable to implement.
I don't think it's a difficulty of implementation thing. I'd guess that
alot of the current stuff for shoving down conditions would apply (I
haven't looked, though). It's more a case of making sure the optimization
cannot be a false one that changes the results. What we need is someone
to sit down and analyze the cases in a serious way.
I think that for union all, conditions other than non-stable ones can be
pushed down. For union, I think there might be issues due to the removal
of duplicates in certain cases where the results will change, but that the
results may not be deterministic in such cases anyway (like a case where
two values are not exactly the same but aren't distinct due to collation
or some such and so the system picks an arbitrary one and that arbitrary
one affects the output of query). I have no good idea for EXCEPT and
INTERSECT.
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-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, josh@agliodbs.com
Subject: Re: Indexing UNIONs
In-Reply-To: <20020715173335.R43416-100000@megazone23.bigpanda.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