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 t.id = u.id   AND
t.foo = 'bar'
    SELECT id, txt FROM largetable2,smalltable t WHERE t.id = u.id   AND
t.foo = '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 
desc LOOP

                IF counter =1 THEN
                  select INTO  sql 'SELECT '||mviews.this_day||' AS plainday, 
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;
                END IF;

            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 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
>     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!

Weitersagen: GMX DSL-Flatrates mit Tempo-Garantie!
Ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Reply via email to