Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7026D2E2385 for ; Mon, 21 Jan 2008 00:01:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 90037-09 for ; Mon, 21 Jan 2008 00:01:46 -0400 (AST) Received: from mr4.mail-relay.ubc.ca (mr4.mail-relay.ubc.ca [137.82.45.7]) by postgresql.org (Postfix) with ESMTP id 4C9B02E033C for ; Mon, 21 Jan 2008 00:01:45 -0400 (AST) X-Ubc-Received: from mr4.mail-relay.ubc.ca (localhost [127.0.0.1]) by localhost (Postfix) with SMTP id 06F13C8DE for ; Sun, 20 Jan 2008 20:01:44 -0800 (PST) Received: from mta2.interchange.ubc.ca (mta2.interchange.ubc.ca [142.103.145.70]) by mr4.mail-relay.ubc.ca (Postfix) with ESMTP for ; Sun, 20 Jan 2008 20:01:42 -0800 (PST) Received: from MOA131 (S01060015e96723a5.vc.shawcable.net [70.79.10.24]) by smtp.interchange.ubc.ca (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003)) with ESMTPA id <0JUZ00HB676TZY@smtp.interchange.ubc.ca> for pgsql-sql@postgresql.org; Sun, 20 Jan 2008 20:01:42 -0800 (PST) Date: Sun, 20 Jan 2008 20:01:08 -0800 From: Ryan Wallace Subject: improvements to query with hierarchical elements To: pgsql-sql@postgresql.org Message-id: <002601c85be2$410aea30$c320be90$@ubc.ca> MIME-version: 1.0 X-Mailer: Microsoft Office Outlook 12.0 Content-type: multipart/alternative; boundary="----=_NextPart_000_0027_01C85B9F.32E7AA30" Content-language: en-ca Thread-index: Achb4kDAI9efpyQxSGWQ0zIk0yKs4Q== X-UBC-Scanned: Sophos PureMessage 5.4.1.325704, Antispam-Engine: 2.6.0.325393, Antispam-Data: 2008.1.20.194453 X-UBC-Relayed: Relayed through mail-relay.ubc.ca X-PerlMx-Spam: Probability=27%, Report=RELAY_IN_PBL_11 2.5, SXL_IP_DYNAMIC 0.5, HTML_70_90 0.1, RDNS_POOLED 0, RDNS_SUSP 0, RDNS_SUSP_SPECIFIC 0, RELAY_IN_PBL 0, __CP_NAME_BODY 0, __CT 0, __CTYPE_HAS_BOUNDARY 0, __CTYPE_MULTIPART 0, __CTYPE_MULTIPART_ALT 0, __HAS_MSGID 0, __HAS_X_MAILER 0, __HTML_FONT_BLUE 0, __HTML_MSWORD 0, __MIME_HTML 0, __MIME_VERSION 0, __RDNS_POOLED_2 0, __SANE_MSGID 0, __STYLE_RATWARE_2 0, __TAG_EXISTS_HTML 0, __USER_AGENT_MS_GENERIC 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/154 X-Sequence-Number: 30007 This is a multipart message in MIME format. ------=_NextPart_000_0027_01C85B9F.32E7AA30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Greetings, I have a complex query which I am trying to figure out the most efficient way of performing. My database is laid out as follows: items -have_many-> events -have_many-> event_locations -have_many-> locations also rows in the location_links table link two locations together in a parent-child relationship and rows in the location_descendants table provide a full list of the descendants of a particular location. I am trying to find all locations which both are direct children of a given parent location, and are associated with at least one item in a constrained subset of items. (eg. Find all states of the USA in which at least one wooden axe was made. Also find the number of wooden axes made in each state.) I have developed the following query: SELECT locations.*, location_ids.item_count AS item_count FROM locations JOIN (SELECT immediate_descendants.ancestor_id AS id, COUNT(DISTINCT creation_events.item_id) AS item_count FROM event_locations JOIN (SELECT * FROM location_descendants WHERE ancestor_id IN (SELECT child_id FROM location_links WHERE parent_id = *note 1* ) ) AS immediate_descendants ON event_locations.location_id = immediate_descendants.descendant_id JOIN (SELECT * FROM events WHERE item_id IN (*note 2*) AND association = 'creation' ) AS creation_events ON event_locations.event_id = creation_events.id GROUP BY immediate_descendants.ancestor_id ) AS location_ids ON locations.id = location_ids.id *note 1* - the id of the parent location. *note 2* - the query which returns a list of constrained item ids This works but I am looking for any way to improve the performance of the query (including changing the layout of the tables). Any ideas, suggestions or general pointers would be greatly appreciated. Thanks very much, Ryan ------=_NextPart_000_0027_01C85B9F.32E7AA30 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Greetings,

 

I have a complex query which I am trying to figure = out the most efficient way of performing.

 

My database is laid out as follows:

items –have_many-> events = –have_many-> event_locations –have_many-> locations

 

also rows in the location_links table link two = locations together in a parent-child relationship and rows in the = location_descendants table provide a full list of the descendants of a

particular location.

 

I am trying to find all locations which both are = direct children of a given parent location, and are associated with at least one item in = a constrained subset of items.

(eg. Find all states of the USA in which at least = one wooden axe was made. Also find the number of wooden axes made in each = state.)

 

I have developed the following = query:

 

SELECT  locations.*,

        = location_ids.item_count AS item_count

FROM    locations

        = JOIN

         &= nbsp;      (SELECT immediate_descendants.ancestor_id AS id,

         &= nbsp;           &n= bsp;  COUNT(DISTINCT creation_events.item_id) AS item_count

         &= nbsp;      FROM    event_locations

         &= nbsp;             &= nbsp;JOIN

         &= nbsp;           &n= bsp;          (SELECT *

         &= nbsp;           &n= bsp;          FROM    location_descendants

         &= nbsp;           &n= bsp;          WHERE   ancestor_id IN

         &= nbsp;           &n= bsp;           &nb= sp;      (SELECT child_id

         &= nbsp;           &n= bsp;           &nb= sp;      FROM    location_links

         &= nbsp;           &n= bsp;           &nb= sp;      WHERE   parent_id =3D *note 1*

         &= nbsp;           &n= bsp;           &nb= sp;      )

         &= nbsp;           &n= bsp;          ) AS immediate_descendants

         &= nbsp;           &n= bsp;  ON      event_locations.location_id =3D immediate_descendants.descendant_id

         &= nbsp;           &n= bsp;  JOIN

         &= nbsp;           &n= bsp;          (SELECT *

         &= nbsp;           &n= bsp;          FROM    events

         &= nbsp;           &n= bsp;          WHERE   item_id IN (*note 2*) AND association =3D = 'creation'

         &= nbsp;           &n= bsp;          ) AS creation_events

         &= nbsp;           &n= bsp;  ON      event_locations.event_id =3D = creation_events.id

         &= nbsp;      GROUP BY immediate_descendants.ancestor_id

         &= nbsp;      ) AS location_ids ON locations.id =3D location_ids.id

 

*note 1* - the id of the parent = location.

*note 2* - the query which returns a list of constrained item ids

 

This works but I am looking for any way to improve = the performance of the query (including changing the layout of the tables). = Any ideas, suggestions or general pointers would be greatly = appreciated.

 

Thanks very much,

Ryan

------=_NextPart_000_0027_01C85B9F.32E7AA30--