snowflaking - Mailing list pgsql-novice

Hi all,

I need some help optimizing a snowflaked :( db structure.

I'm using 7.3.4 at the moment

Scenario:
I have only a few tables holding real data:
e.g. txt,flt,tf,datum

these tables only hold 1 column of real data and information what 'virtual' type they are.

I have a table containing meta information about 'virtual' tables, i.e. tables that don't really
exist in postgresql but have to be joined on the fly via the meta information.

At runtime a query is build:
---------------------------------------------------------------------------------------------------
SELECT
    o.id_objekt as id_objekt
   ,kapsel.id2_objekt as id2_kapsel
   ,vater.id2_objekt as id2_vater
   ,anzeige.id_objekt_objekt as id_anzeige
   ,anzeige.id2_objekt  as anzeige
   ,anzeige.id2_objekt as id2_anzeige
   ,anzeige_datum_display_von.id_datum as id_anzeige_datum_display_von
   ,anzeige_datum_display_von.datum_fld  as anzeige_datum_display_von
   ,anzeige_datum_display_bis.id_datum as id_anzeige_datum_display_bis
   ,anzeige_datum_display_bis.datum_fld  as anzeige_datum_display_bis
   ,anzeige_enabled.id_tf as id_anzeige_enabled
   ,anzeige_enabled.tf  as anzeige_enabled
   ,headline.id_objekt_objekt as id_headline
   ,headline.id2_objekt  as headline
[...MANY MANY MORE...]
FROM
                   objekt          o

   JOIN ( SELECT id_objekt, id2_objekt                   FROM objekt_objekt JOIN meta USING (
id_meta ) WHERE id_meta = 91 ) vater  ON (  vater.id_objekt = o.id_objekt )
   JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta USING (
id_meta ) WHERE id_meta = 110 ) Kapsel ON ( kapsel.id_objekt = o.id_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta
USING ( id_meta ) WHERE id_meta=84 ) anzeige ON ( anzeige.id_objekt=o.id_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_datum, datum_fld FROM datum JOIN meta USING ( id_meta )
WHERE id_meta=73 ) anzeige_datum_display_von ON (
anzeige_datum_display_von.id_objekt=anzeige.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_datum, datum_fld FROM datum JOIN meta USING ( id_meta )
WHERE id_meta=74 ) anzeige_datum_display_bis ON (
anzeige_datum_display_bis.id_objekt=anzeige.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_tf, tf FROM tf JOIN meta USING ( id_meta ) WHERE
id_meta=75 ) anzeige_enabled ON ( anzeige_enabled.id_objekt=anzeige.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta
USING ( id_meta ) WHERE id_meta=35 ) headline ON ( headline.id_objekt=o.id_objekt )
[...MANY MANY MORE...]
----------------------------------------------------------------------------------------------------

Execution takes approximately 0.05s
When I EXPLAIN ANALYZE the query I get:

----------------------------------------------------------------------------------------------------
Hash Join  (cost=123.24..212.68 rows=1 width=576) (actual time=11.93..12.50 rows=1 loops=1)
    Hash Cond: ("outer".id_objekt = "inner".id_objekt)
    ->  Hash Join  (cost=118.56..207.99 rows=1 width=552) (actual time=11.24..11.80 rows=1 loops=1)
          Hash Cond: ("outer".id_objekt = "inner".id_objekt)
          ->  Nested Loop  (cost=107.33..196.76 rows=1 width=535) (actual time=10.40..10.94 rows=1
loops=1)
                Join Filter: ("inner".id_objekt = "outer".id2_objekt)
                ->  Nested Loop  (cost=105.41..185.51 rows=1 width=478) (actual time=9.82..10.34
rows=1 loops=1)
                      Join Filter: ("inner".id_objekt = "outer".id2_objekt)
                      ->  Nested Loop  (cost=96.53..174.35 rows=1 width=421) (actual time=9.12..9.63
rows=1 loops=1)
[... MANY MORE COLUMNS LOOKING SIMILAR ...]
  Total runtime: 17.36 msec
(171 rows)
----------------------------------------------------------------------------------------------------

Is there anything I can do to speed it a bit up?
We can assume, that
a) inserts are done very rarely and
b) the number of actual results are very low

--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de