From: Sam Mason <sam@samason.me.uk>
To: pgsql-performance@postgresql.org
Subject: Optimising queries involving unions
Date: Thu, 26 May 2005 16:22:03 +0100
Message-ID: <20050526152203.GI15205@colo.samason.me.uk> (raw)
Hi,
I've got a query that I think the query optimiser should be able
to work it's magic on but it doesn't! I've had a look around and
asked on the IRC channel and found that the current code doesn't
attempt to optimise for what I'm asking it to do at the moment.
Here's a bad example:
SELECT u.txt
FROM smalltable t, (
SELECT id, txt FROM largetable1
UNION ALL
SELECT id, txt FROM largetable2) u
WHERE t.id = u.id
AND t.foo = 'bar';
I was hoping that "smalltable" would get moved up into the union,
but it doesn't at the moment and the database does a LOT of extra
work. In this case, I can manually do quite a couple of transforms
to move things around and it does the right thing:
SELECT txt
FROM (
SELECT l.id as lid, r.id as rid, r.foo, l.txt
FROM largetable1 l, smalltable r
UNION ALL
SELECT l.id as lid, r.id as rid, r.foo, l.txt
FROM largetable1 l, smalltable r)
WHERE foo = 'bar';
AND lid = rid
The optimiser is intelligent enough to move the where clauses up
into the union and end end up with a reasonably optimal query.
Unfortunatly, in real life, the query is much larger and reorganising
everything manually isn't really feasible!
Is this a good place to ask about this or is it more in the realm
of the hackers mailing list?
Thanks,
Sam
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: sam@samason.me.uk
Subject: Re: Optimising queries involving unions
In-Reply-To: <20050526152203.GI15205@colo.samason.me.uk>
* 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