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

-----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,
namelythose 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-----