From: Jens-Wolfhard Schicke <drahflow@gmx.de>
To: pgsql-performance@postgresql.org
Subject: Re: Unfortunate expansion of composite types in union
Date: Sun, 04 Nov 2007 17:59:43 +0100
Message-ID: <472DFA7F.5040709@gmx.de> (raw)
In-Reply-To: <162867790711020332h7eddf94pbdd44f98e7ce59d7@mail.gmail.com>
References: <472AF02D.10809@gmx.de>
<162867790711020332h7eddf94pbdd44f98e7ce59d7@mail.gmail.com>
-----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-----
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: <472DFA7F.5040709@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