Hi everybody,
I'm looking for outer join complexity hints. It seems
to me that PostGreSQL's performances dramaticaly
decrease for (more than) three table joins. Of course,
I make intensive use of indices on joining fields...
Is it possible that PostGreSQL fail to use some of
them on a three or four tables outer join ?
Has anybody noticed such a gap yet ?
Please see lower for precise \d/explain outputs.
Thanks,
Jeff.
To be more precise, here's my view definition:
CREATE VIEW view_partie_prepub AS
SELECT *,clef_prepub_prem_fils_de(clef_partie) AS
clef_partie_prem_fils
FROM
(
(SELECT clef AS clef_partie, clef_prepubli_mere,
intitule, format AS
format_partie, num_page, ancre FROM partie_prepub)
titi
NATURAL LEFT OUTER JOIN
inclusion_prepub
)
NATURAL LEFT OUTER JOIN
(SELECT clef_partie, format AS format_fichier,
repertoire,fic_name FROM
fichier_prepub)toto
;
and the explain output:
db=# explain select * from view_partie_prepub where
clef_partie='12';
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..8.04 rows=1 width=108)
-> Nested Loop (cost=0.00..4.04 rows=1 width=68)
-> Index Scan using partie_prepub_pkey on
partie_prepub (cost=0.00..2.01 rows=1 width=56)
-> Index Scan using
inclusion_prepub_clef_partie_id on inclusion_prepub
(cost=0.00..2.01 rows=1 width=12)
-> Subquery Scan toto (cost=0.00..2.89 rows=89
width=40)
-> Seq Scan on fichier_prepub
(cost=0.00..2.89 rows=89 width=40)
EXPLAIN
I did a vacuum analyze before that. Did PostGres see
that fichier_prepub is a small table (89 rows) and
that using indices is a waste of time ?
and, to be REALLY exhaustive, the description of the
fichier_prepub_clef_partie, which is the index for
fichier_prepub.clef_partie:
hn=# \d fichier_prepub_clef_partie
Index "fichier_prepub_clef_partie"
Attribute | Type
-------------+---------
clef_partie | integer
btree
___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.fr
