snowflaking

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