Thread: Prolem wiht long query?

Hi,

I have some trouble with a very long query.

This Query run well :
----------------

select *   from       adresses, liaison_adresse_rubrique as lar0,       liaison_adresse_rubrique as lar1,
liaison_adresse_rubriqueas lar2   where       lar0.refadresse=adresses.ref   and lar0.refrubrique=73   and
lar1.refadresse=adresses.ref  and lar1.refrubrique=151   and lar2.refadresse=adresses.ref   and lar2.refrubrique=170
 

This Query take a lot of time :
-----------------------

select *   from       adresses, liaison_adresse_rubrique as lar0,       liaison_adresse_rubrique as lar1,
liaison_adresse_rubriqueas lar2,       liaison_adresse_rubrique as lar3,       liaison_adresse_rubrique as lar4   where
     lar0.refadresse=adresses.ref   and lar0.refrubrique=73   and lar1.refadresse=adresses.ref   and
lar1.refrubrique=150  and lar2.refadresse=adresses.ref   and lar2.refrubrique=160   and lar3.refadresse=adresses.ref
andlar3.refrubrique=151   and lar4.refadresse=adresses.ref   and lar4.refrubrique=170
 

This Query never finish, and take all my memory (and I have 384Mb)
---------------------------------------------------
select *   from       adresses, liaison_adresse_rubrique as lar0,       liaison_adresse_rubrique as lar1,
liaison_adresse_rubriqueas lar2,       liaison_adresse_rubrique as lar3,       liaison_adresse_rubrique as lar4,
liaison_adresse_rubriqueas lar5,       liaison_adresse_rubrique as lar6   where       lar0.refadresse=adresses.ref
andlar0.refrubrique=73   and lar1.refadresse=adresses.ref   and lar1.refrubrique=148   and lar2.refadresse=adresses.ref
 and lar2.refrubrique=153   and lar3.refadresse=adresses.ref   and lar3.refrubrique=150   and
lar4.refadresse=adresses.ref  and lar4.refrubrique=160   and lar5.refadresse=adresses.ref   and lar5.refrubrique=151
andlar6.refadresse=adresses.ref   and lar6.refrubrique=170
 



Every think is index using hash table.

Is it a limitation of pg?

Do you know another method to do that?

Is it exist an intersect operator in pg (like in oracle)?

Jérome knobl, Lausanne, Switzerland

Jérome Knöbl <jknobl@mandanet.ch> writes:
> I have some trouble with a very long query.

The amount of time taken by the optimizer goes up exponentially with
the number of tables being joined.  If you are using a pre-6.5
version of Postgres then I'd recommend updating to 6.5; its optimizer
is considerably faster and less memory-hungry than prior releases.

You could also try reducing the GEQO threshold, which is the number
of tables at which the system stops trying to do exhaustive optimization
and changes to a heuristic planning method.
        regards, tom lane