pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: esgerlist@vesc.nl
To: pgsql-sql@postgresql.org
Subject: Re: need some help understanding sloq query
Date: Tue, 04 Dec 2001 09:09:05 -0800
Message-ID: <web-519931@davinci.ethosmedia.com> (raw)
In-Reply-To: <01112918455100.01788@McIntosh>

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



view thread (5+ messages)  latest in thread

Message-ID: <web-519931@davinci.ethosmedia.com>
Permalink:  ../web-519931@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-519931@davinci.ethosmedia.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: josh@agliodbs.com, esgerlist@vesc.nl
  Subject: Re: need some help understanding sloq query
  In-Reply-To: <web-519931@davinci.ethosmedia.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