Message-ID: <472DFA7F.5040709@gmx.de>
Date: Sun, 04 Nov 2007 17:59:43 +0100
From: Jens-Wolfhard Schicke <drahflow@gmx.de>
User-Agent: Mozilla-Thunderbird 2.0.0.6 (X11/20071008)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Unfortunate expansion of composite types in union
References: <472AF02D.10809@gmx.de>
	<162867790711020332h7eddf94pbdd44f98e7ce59d7@mail.gmail.com>
In-Reply-To: <162867790711020332h7eddf94pbdd44f98e7ce59d7@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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