pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProlem wiht long query?
2+ messages / 2 participants
[nested] [flat]
* Prolem wiht long query?
@ 1999-07-14 14:31 Jérome Knöbl <jknobl@mandanet.ch>
0 siblings, 1 reply; 2+ messages in thread
From: Jérome Knöbl @ 1999-07-14 14:31 UTC (permalink / raw)
To: pgsql-sql@hub.org
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_rubrique as 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_rubrique as 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
and lar3.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_rubrique as lar2,
liaison_adresse_rubrique as lar3,
liaison_adresse_rubrique as lar4,
liaison_adresse_rubrique as lar5,
liaison_adresse_rubrique as lar6
where
lar0.refadresse=adresses.ref
and lar0.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
and lar6.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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Prolem wiht long query?
@ 1999-07-14 15:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jérome Knöbl <jknobl@mandanet.ch>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 1999-07-14 15:20 UTC (permalink / raw)
To: Jérome Knöbl <jknobl@mandanet.ch>; +Cc: pgsql-sql@hub.org
=?iso-8859-1?Q?J=E9rome=20Kn=F6bl?= <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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-07-14 15:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-14 14:31 Prolem wiht long query? Jérome Knöbl <jknobl@mandanet.ch>
1999-07-14 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox