Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB48d2m57635 for ; Tue, 4 Dec 2001 03:39:02 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fB48Van81571 for pgsql-sql@postgresql.org; Tue, 4 Dec 2001 03:31:36 -0500 (EST) (envelope-from news) From: Arian Prins X-Newsgroups: comp.databases.postgresql.sql Subject: Re: need some help understanding sloq query Date: Tue, 04 Dec 2001 09:31:30 +0100 Organization: Academic Medical Center, The Netherlands Lines: 77 Message-ID: <3C0C89E2.EC2D9325@zonnet.nl> References: <01112918455100.01788@McIntosh> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.surfnet.nl X-Mailer: Mozilla 4.7 [nl] (WinNT; I) X-Accept-Language: nl Cache-Post-Path: sphere.amc.uva.nl!unknown@amcnip36143.amc.uva.nl X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/) To: pgsql-sql@postgresql.org X-Archive-Number: 200112/68 X-Sequence-Number: 5891 Hello Esger, Esger Abbink schreef: > i have a little performance problem. > > db (simplified): > > table current: > current_update_id, ... > > table datasets: > set_id, update_id, .... > > table ents: > e_id, set_id, ... > > table qtys: > set_id, e_id, ... > > indexes are defined on all set_id's & datasets.update_id. > > an update consists of several sets which in turn consists of several ents, for > a specific ent in a set multiple qtys may exist. > (normal: 1 update - 1 set - few hundred ents - 1 qty per ent) > > now i want to do the following: i want to get some specific qty values for the > ents of the last update only. > > so i do a query like: > > 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 ; [SNIP] If I understand correctly, the table current allways contains 1 row, namely the current_update_id, identifying the update that you want to query on. That would mean you could start out from this table and Inner Join all your tables... this way your result-set would be all data for current_update_id: select some_other_fields from ents e, qtys q, datasets s, current c where e.set_id = q.set_id and e.e_id = q.e_id and s.set_id = e.set_id and s.update_id = c.current_update_id and q.other_field = some_const; If you used primary keys and indexes this query should use indexes allways. Other ideas: 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 = (select max(current_update_id) from current)) Given your first example and queryplan, I think the problem is in the Mergejoin; both from qtys and from ents an enormous amount of data is selected that is only narowed down in the last step, the merge-join. I think using my first query, the optimizer will be much more efficient. I couldn't reproduce your query to test though... too much vagueness.... If this doesn't help then post some sql-definitions of your database. Succes, A. Prins.