Re: Unfortunate expansion of composite types in union - Mailing list pgsql-performance

Hello

I am not sure, but this query will be evaluated efectivelly, because
all necessary data will be in cache.

PostgreSQL doesn't support Common Table Expressions - you can write
SRF function:

CREATE OR REPLACE FUNCTION c(integer)
RETURNS SETOF something AS $$
DECLARE r RECORD;
  o something;
BEGIN
  FOR r IN SELECT * FROM link WHERE link.s = $1 LOOP
    o := (1, r.id, NULL, NULL, NULL);
    RETURN NEXT o;
    o := (2, r.id, r.s, r.e, r.intensity, NULL);
    RETURN NEXT o;
    o := (3, r.id, r.o, r.format, NULL, r.data);
    RETURN NEXT o;
    RETURN;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM c(8692);

Regards
Pavel Stehule

On 02/11/2007, Jens-Wolfhard Schicke <drahflow@gmx.de> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> I have the following query:
>
> explain analyze
> SELECT
>   (cast((
>     SELECT cast(row(1, o.id, NULL, NULL, NULL, NULL) as something)
>       FROM ONLY object o WHERE o.id = l.e
>     UNION ALL
>       SELECT cast(row(2, l2.id, l2.s, l2.e, l2.intensity, NULL) as something)
>         FROM ONLY link l2  WHERE l2.id = l.e
>     UNION ALL
>       SELECT cast(row(3, r.id, r.o, r.format, NULL, r.data) as something)
>         FROM ONLY representation r WHERE r.id = l.e
>   ) as something)).*,
>   l.id, l.s, l.intensity
> FROM link l
> WHERE l.s = 8692
> ;
>
>
> and the execution plan:
>
>                                                                        QUERY PLAN
> -
---------------------------------------------------------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on link l  (cost=6.33..27932.79 rows=178 width=32) (actual time=0.172..12.366 rows=149 loops=1)
>    Recheck Cond: (s = 8692)
>    ->  Bitmap Index Scan on link_s_idx  (cost=0.00..6.29 rows=178 width=0) (actual time=0.050..0.050 rows=149
loops=1)
>          Index Cond: (s = 8692)
>    SubPlan
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.004..0.011 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.004..0.004
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.003..0.003rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.004..0.011 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.004..0.004
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.003..0.003rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.004..0.011 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.004..0.004
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.002..0.002rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.006..0.013 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.006..0.006
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.003..0.003rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.004..0.011 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.004..0.004
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.003..0.003rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.004..0.011 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.004..0.004
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.002..0.002rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.006..0.013 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.006..0.006
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.003..0.003rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.004..0.011 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.004..0.004
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.003..0.003
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.003..0.003rows=0 loops=149) 
>                  Index Cond: (id = $0)
>      ->  Append  (cost=0.00..25.52 rows=3 width=34) (actual time=0.006..0.015 rows=1 loops=149)
>            ->  Index Scan using object_id_idx on object o  (cost=0.00..8.30 rows=1 width=8) (actual time=0.006..0.006
rows=1loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using link_id_idx on link l2  (cost=0.00..8.86 rows=1 width=32) (actual time=0.004..0.004
rows=0loops=149) 
>                  Index Cond: (id = $0)
>            ->  Index Scan using representation_id_idx on representation r  (cost=0.00..8.32 rows=1 width=34) (actual
time=0.004..0.004rows=0 loops=149) 
>                  Index Cond: (id = $0)
>  Total runtime: 12.594 ms
> (48 rows)
>
> (hope it wasn't mangled)...
>
> The "something" type is:
>
> create type something as (
>   t integer,
>   id bigint,
>   ref1 bigint,
>   ref2 bigint,
>   intensity double precision,
>   data bytea
> );
>
> Problem: It looks as if every column of "something" is fetched seperately. I'd think a plan which only did one
indexscanfor the row on each table and then returns 
> the complete row at once better. (Especially if the dataset is in memory)
>
> What I want to achive is to load a set of somethings of yet unknown type (any of link, object, representation) at
once,namely those objects which reside 
> at the ends of the links. Is there any better way?
>
> My version:
>                                                          version
> -
-------------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.3beta2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.2.3 20071014 (prerelease) (Debian
4.2.2-3)
> (1 row)
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFHKvAszhchXT4RR5ARAniBAJ9bk6noLG6tIb2NKmAS7bk6Fpig9QCeNEzF
> YND1waoDKi46BjjNEKwFMF0=
> =/h36
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>