Date: Fri, 25 Jan 2008 00:38:13 -0800
To: Ryan Wallace <rywall@interchange.ubc.ca>
From: Steve Midgley <public@misuse.org>
Subject: Re: improvements to query with hierarchical elements
Cc: pgsql-sql@postgresql.org
In-Reply-To: <001501c85d6f$88b55f80$9a201e80$@ubc.ca>
References: <001501c85d6f$88b55f80$9a201e80$@ubc.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
Message-Id: <20080125083821.46B8C2E021E@postgresql.org>


At 07:24 PM 1/22/2008, you wrote:
>Hi all,
>
>I have created a little test database to help illustrate my situation.
>
>CREATE TABLE categories (
>     id integer NOT NULL,
>     name character varying(255) NOT NULL,
>     description character varying(255),
>     vocabulary_id integer,
>     derived boolean
>);
>
>CREATE TABLE category_descendants (
>     id integer NOT NULL,
>     ancestor_id integer,
>     descendant_id integer,
>     distance integer,
>     derived boolean
>);
>
>CREATE TABLE category_links (
>     id integer NOT NULL,
>     parent_id integer,
>     child_id integer,
>     derived boolean
>);
>[snip..]
>As stated in my last post, any help you can give on how to improve 
>queries of this type would be very much appreciated.
>
>Thanks!
>Ryan
>

Hi Ryan,

I've been toying with your sample data for a bit and I apologize but 
your query has me baffled. Not that it's wrong - it actually looks very 
sophisticated, but it seems super complex to me - kind of like how I 
usually feel reading perl.. :)

I'm sure real sql-heads would get it right away but I'm not able to.

If you're looking to optimize the use-case you provided in your first 
email, the best thing I can suggest from what I understand would make 
an assumption:

Are the data in your tables are slowly changing? So could you build 
some analytic/pre-calculated data into these tables or related 
supporting ones to guide your searches/queries?

For example, if you want to find only records which are immediate 
children of other records, why not make a table which stores just that 
information? Your current tables are fully hierarchical which is great, 
but you want to look things up quickly based on a specific 
relationship: records who are direct children of a particular record..

So if you made a calculated table that stores this information, you 
could keep it up to date either by running the calculation script 
periodically or by attaching updates to relevant triggers / rules.

I'm sorry I'm not able to get into the SQL / example you sent further. 
I got lost in the code, which I'm a little embarrassed to admit but 
there you are.

If you're interested in this idea of precalculating values to optimize 
your search, I'd be happy to discuss further. Also, Ralph Kimball's 
Data Warehousing books are excellent on this subject (one of the few 
authors who truly changed the way I think about data).

Steve