From: Steve Midgley <public@misuse.org>
To: pgsql-sql@postgresql.org
Cc: Ryan Wallace <rywall@interchange.ubc.ca>
Subject: Re: improvements to query with hierarchical elements
Date: Mon, 21 Jan 2008 21:21:20 -0800
Message-ID: <20080122052133.65CF22E3881@postgresql.org> (raw)
In-Reply-To: <20080122042522.88EC816DA1C5@mx1.hub.org>
References: <20080122042522.88EC816DA1C5@mx1.hub.org>
>Date: Sun, 20 Jan 2008 20:01:08 -0800
>From: Ryan Wallace <rywall@interchange.ubc.ca>
>To: pgsql-sql@postgresql.org
>Subject: improvements to query with hierarchical elements
>Message-ID: <002601c85be2$410aea30$c320be90$@ubc.ca>
>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
Hi Ryan,
I have built some similar queries so I might be able to help you. But
it's a little hard (for me) to dig into your query without a test set.
Could you please post some create table and insert statements to give
us a little test bed to run your query in? I realize that may be a fair
bit of work for you but it would help me to give you some ideas.
Without seeing a more formal schema and being able to toy with it, I'm
not sure I can give good advice. Others may have different opinions
which I would welcome.
Sincerely,
Steve
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: public@misuse.org, rywall@interchange.ubc.ca
Subject: Re: improvements to query with hierarchical elements
In-Reply-To: <20080122052133.65CF22E3881@postgresql.org>
* 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