Re: need some help understanding sloq query

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;