pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Esger Abbink <pgsql@bumblebeast.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: need some help understanding sloq query
Date: Thu, 6 Dec 2001 08:25:11 -0800 (PST)
Message-ID: <20011206081928.G25449-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200112061334.OAA03446@fikkie.vesc.nl>

On Thu, 6 Dec 2001, Esger Abbink wrote:

> > Esger,
> >
> > > select some_other_fields from ents e, qtys q where e.set_id =
> > > q.set_id and
> > > e.e_id = q.e_id and e.set_id in (select set_id from datasets
> > > where update_id in (select cur_update_id from current)) and
> > > q.other_field = some_const ;
> > >
> > > this query takes ages :(
> >
> > I'm not surprised.  You're doing a nested subselect with the IN
> > operator; frankly, you couldn't make a relatively simple query any
> > slower than the above.  It looks like the query parser is doing its best
> > to optimize, but you've forced it to compare every row in ents JOIN qtys
> > against the whole datasets table.  What's wrong with:
> >
> > SELECT some_fields
> > FROM ents JOIN qtys USING (set_id)
> > 	JOIN datasets USING (set_id)
> > 	JOIN current ON datasets.update_id = current.cur_update_id
> > WHERE other_field = some_constant;
> >
>
> i figured i wasnt doing things "right" ;)
>
> unfortunately the query above takes down my postgres (7.0.3 tried on 2
> servers). 1 join no problem, 2 joins results in:

If you want to use JOIN, you'll need to upgrade. IIRC, 7.0.x had bugs that
caused this kind of failure that were fixed for 7.1.

I think the above is equivalent to:
select some_fields from
 ents, qtys, datasets, current
 where ents.set_id=qtys.set_id and
       ents.set_id=datasets.set_id and
       datasets.update_id = current.cur_update_id and
       other_field = some_constant;




view thread (5+ messages)

Message-ID: <20011206081928.G25449-100000@megazone23.bigpanda.com>
Permalink:  ../20011206081928.G25449-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20011206081928.G25449-100000@megazone23.bigpanda.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sszabo@megazone23.bigpanda.com, pgsql@bumblebeast.com, josh@agliodbs.com
  Subject: Re: need some help understanding sloq query
  In-Reply-To: <20011206081928.G25449-100000@megazone23.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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