From: Jens-Wolfhard Schicke <drahflow@gmx.de>
To: pgsql-performance@postgresql.org
Subject: Unfortunate expansion of composite types in union
Date: Fri, 02 Nov 2007 10:38:53 +0100
Message-ID: <472AF02D.10809@gmx.de> (raw)
-----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=1 loops=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=0 loops=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.003 rows=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=1 loops=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=0 loops=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.003 rows=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=1 loops=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=0 loops=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.002 rows=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=1 loops=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=0 loops=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.003 rows=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=1 loops=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=0 loops=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.003 rows=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=1 loops=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=0 loops=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.002 rows=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=1 loops=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=0 loops=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.003 rows=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=1 loops=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=0 loops=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.003 rows=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=1 loops=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=0 loops=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.004 rows=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 indexscan for 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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: drahflow@gmx.de
Subject: Re: Unfortunate expansion of composite types in union
In-Reply-To: <472AF02D.10809@gmx.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox