Skip site navigation (1) Skip section navigation (2)

outer joins complexity

From: Jeff Anto <antojf2001(at)yahoo(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: outer joins complexity
Date: 2002-01-29 20:11:53
Message-ID: 20020129201153.1808.qmail@web20909.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2002-01-29 20:27:28
Subject: Re: Upgrade 7.0.3 -> 7.1.3 problems!
Previous:From: David LinkDate: 2002-01-29 20:00:44
Subject: Re: Upgrade 7.0.3 -> 7.1.3 problems!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group