Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB6GP7m71997 for ; Thu, 6 Dec 2001 11:25:07 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 0CF99D5EC; Thu, 6 Dec 2001 08:25:12 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 02ABB5BED; Thu, 6 Dec 2001 08:25:11 -0800 (PST) Date: Thu, 6 Dec 2001 08:25:11 -0800 (PST) From: Stephan Szabo To: Esger Abbink Cc: Josh Berkus , Subject: Re: need some help understanding sloq query In-Reply-To: <200112061334.OAA03446@fikkie.vesc.nl> Message-ID: <20011206081928.G25449-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200112/77 X-Sequence-Number: 5900 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;