Re: need some help understanding sloq query

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;

That allows Tom's magic query engine to do its work optimizing.
Assuming, of course, that there *are* indexes on update_id and
datasets.set_id ...

I heartily recommend that you read Celko's "SQL for Smarties" (book
reviews: http://techdocs.postgresql.org/bookreviews.php ). You seem to
have fallen into the trap of using complex queries to answer simple
questions, and your database performance is suffering because of it.

-Josh Berkus