pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUnfortunate expansion of composite types in union
3+ messages / 2 participants
[nested] [flat]
* Unfortunate expansion of composite types in union
@ 2007-11-02 09:38 Jens-Wolfhard Schicke <drahflow@gmx.de>
2007-11-02 10:32 ` Re: Unfortunate expansion of composite types in union Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jens-Wolfhard Schicke @ 2007-11-02 09:38 UTC (permalink / raw)
To: 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=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-----
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unfortunate expansion of composite types in union
2007-11-02 09:38 Unfortunate expansion of composite types in union Jens-Wolfhard Schicke <drahflow@gmx.de>
@ 2007-11-02 10:32 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-04 16:59 ` Re: Unfortunate expansion of composite types in union Jens-Wolfhard Schicke <drahflow@gmx.de>
0 siblings, 1 reply; 3+ messages in thread
From: Pavel Stehule @ 2007-11-02 10:32 UTC (permalink / raw)
To: Jens-Wolfhard Schicke <drahflow@gmx.de>; +Cc: 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=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-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unfortunate expansion of composite types in union
2007-11-02 09:38 Unfortunate expansion of composite types in union Jens-Wolfhard Schicke <drahflow@gmx.de>
2007-11-02 10:32 ` Re: Unfortunate expansion of composite types in union Pavel Stehule <pavel.stehule@gmail.com>
@ 2007-11-04 16:59 ` Jens-Wolfhard Schicke <drahflow@gmx.de>
0 siblings, 0 replies; 3+ messages in thread
From: Jens-Wolfhard Schicke @ 2007-11-04 16:59 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Pavel Stehule wrote:
> 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);
This is a completely different query from my one. (That is, the results are different.)
My problem is that I have a schema like
fastgraph=# \d object
Table "public.object"
Column | Type | Modifiers
- --------+--------+----------------------------------------------
id | bigint | not null default nextval('id_seq'::regclass)
Indexes:
"object_id_idx" UNIQUE, btree (id)
fastgraph=# \d link
Table "public.link"
Column | Type | Modifiers
- -----------+------------------+----------------------------------------------
id | bigint | not null default nextval('id_seq'::regclass)
s | bigint | not null
e | bigint | not null
intensity | double precision | not null
Indexes:
"link_id_idx" UNIQUE, btree (id)
"link_e_idx" btree (e)
"link_s_idx" btree (s)
"link_se_idx" btree (s, e)
Inherits: object
fastgraph=# \d representation
Table "public.representation"
Column | Type | Modifiers
- --------+--------+----------------------------------------------
id | bigint | not null default nextval('id_seq'::regclass)
o | bigint | not null
format | bigint | not null
data | bytea | not null
Indexes:
"representation_id_idx" UNIQUE, btree (id)
"representation_o_idx" btree (o)
"representation_text" hash (data) WHERE format = 1
Inherits: object
now I want those "objects" (with inheritance) which are connected to some other. So I tried the
query in the original post, and found the execution plan to be suboptimal. Today I tried to do it with OUTER JOINs
but failed utterly. So what is the best way to get the results? The original query is exactly what I need, only the plan
is bad. Any Ideas?
Regards,
Jens-Wolfhard Schicke
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHLfp+zhchXT4RR5ARAorgAKDr2grqWnxbvFMYOPiLJuHpjco30ACgswQB
9/qW9rz+ZngkBYdR0RLsils=
=LdBJ
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-11-04 16:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-02 09:38 Unfortunate expansion of composite types in union Jens-Wolfhard Schicke <drahflow@gmx.de>
2007-11-02 10:32 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-04 16:59 ` Jens-Wolfhard Schicke <drahflow@gmx.de>
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