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

Re: Optimising queries involving unions

From: "Marc Mamin" <m(dot)mamin(at)gmx(dot)net>
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-27 07:40:31
Message-ID: (view raw or whole thread)
Lists: pgsql-performance

I'm using a workaround for this kind of issues:


	select A from 

	   (select B from T1 where C 
	    select B from T2 where C 
	    select B from T3 where C 
	    ) foo
	where D
in your case:

SELECT u.txt
  FROM  (
    SELECT id, txt FROM largetable1,smalltable t WHERE =   AND = 'bar'
    SELECT id, txt FROM largetable2,smalltable t WHERE =   AND = 'bar'
    ) u


	select A from foo where C and D

(A, B, C, D being everything you want, C and D may also include "GROUP

The first version will be handled correctly by the optimiser, whereas in the
second version, 
Postgres will first build the UNION and then run the query on it.

I'm having large tables with identical structure, one per day.
Instead of defining a view on all tables, 
I' using functions that "distribute" my query on all tables.

The only issue if that I need to define a type that match the result
structure and each return type needs its own function.

(The first parameter is a schema name, the four next corresponds to A, B, C,

create type T_i2_vc1 as (int_1 int,int_2 int,vc_1 varchar);

vq_T_i2_vc1(varchar,varchar,varchar,varchar,varchar) RETURNS setof T_i2_vc1
AS $$

    result T_i2_vc1%rowtype;
    mviews RECORD;
    sql varchar;
    counter int;
    select into counter 1;
	    -- loop on all daily tables
	    FOR mviews IN SELECT distinct this_day FROM daylist order by plainday
desc LOOP

		IF counter =1 THEN
		  select INTO  sql 'SELECT '||mviews.this_day||' AS plainday, '||$2||'
FROM '||$3||'_'||mviews.plainday||' WHERE '||$4;
		  select INTO  sql sql||' UNION ALL SELECT '||mviews.this_day||' AS
plainday, '||$2||' FROM '||$3||'_'||mviews.plainday||' WHERE '||$4;

	    select into counter counter+1;
	    END LOOP;
	    select INTO  sql 'SELECT  '||$1||' FROM ('||sql||')foo '||$5;
   for result in   EXECUTE (sql) LOOP
     return  NEXT result;   
   end loop;
 return ;

$$ LANGUAGE plpgsql;

Note: in your case the function shoud have a further parameter to join
largetable(n) to smalltable in the "sub queries"



> 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
>     SELECT id, txt FROM largetable2) u
>   WHERE =
>     AND = '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 as lid, as rid,, l.txt
>       FROM largetable1 l, smalltable r
>     SELECT as lid, as rid,, 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!

Weitersagen: GMX DSL-Flatrates mit Tempo-Garantie!
Ab 4,99 Euro/Monat:

In response to

pgsql-performance by date

Next:From: Josh CloseDate: 2005-05-27 12:52:16
Subject: Re: slow queries, possibly disk io
Previous:From: Christopher Kings-LynneDate: 2005-05-27 01:29:52
Subject: Re: slow queries, possibly disk io

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