pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Prolem 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>
  1999-07-14 15:20 ` Re: [SQL] Prolem wiht long query? Tom Lane <tgl@sss.pgh.pa.us>
  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 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>
  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