Skip site navigation (1) Skip section navigation (2)

Re: Optimising queries involving unions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sam Mason <sam(at)samason(dot)me(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimising queries involving unions
Date: 2005-05-26 16:53:54
Message-ID: 3339.1117126434@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> 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.

I'm afraid we're a long way away from being able to do that; the
parse/plan representation of UNION wasn't chosen with an eye to
being able to optimize it at all :-(.  We can push restriction
clauses down into a union, but we can't do much with join clauses,
because they necessarily refer to tables that don't even exist
within the sub-query formed by the UNION.

It'd be nice to fix this someday, but don't hold your breath ...

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Sam MasonDate: 2005-05-26 17:42:03
Subject: Re: Optimising queries involving unions
Previous:From: Brad MightDate: 2005-05-26 15:36:51
Subject: Specific query performance problem help requested - postgresql 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group