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

From Jens-Wolfhard Schicke
Subject Re: Unfortunate expansion of composite types in union
Date
Msg-id 472DFA7F.5040709@gmx.de
Whole thread Raw
In response to Re: Unfortunate expansion of composite types in union  ("Pavel Stehule" <pavel.stehule@gmail.com>)
List 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-----

pgsql-performance by date:

Previous
From: smiley2211
Date:
Subject: Postgresql.conf Settings
Next
From: "Scott Marlowe"
Date:
Subject: Re: Postgresql.conf Settings