Date: Fri, 16 Jun 2006 09:29:44 -0400
From: Joe <dev@freedomcircle.net>
Subject: Re: Repetitive code
In-reply-to: <bf05e51c0606151926n18555a65x28a0156f8bd217cc@mail.gmail.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
Message-id: <4492B248.7040406@freedomcircle.net>
Organization: Freedom Circle, LLC
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <44920AA7.7070709@freedomcircle.net>
	<bf05e51c0606151926n18555a65x28a0156f8bd217cc@mail.gmail.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)

Aaron Bono wrote:
> Each of your queries has the filter xxx >= $dt where the xxx is the 
> first column in each select.  You could simplify the query by turning 
> the unioned selects into a sub-query and then putting the $dt filter in 
> the outer query.

It would probably have to be two subqueries unless I can find a way to 
merge the differences between new and changed rows.

> I don't know if this will cause performance problems though.  If 
> PostgreSQL completes the inner query before filtering by your $dt you 
> may be better off leaving the $dt filters where they are.

The query is only run a few times a week so performance is largely not a 
concern.  I'm trying to simplify it to make adding tables less 
cumbersome (as a separate effort, the schema may be modified to 
normalize it, e.g., topic joins to entry via subject_id and actor_id and 
a subject and actor can also appear in topic_entry's topic_id).

> I know Oracle has materialized views.  Does PostgreSQL also have 
> materialized views?  If so, you could get great performance from your 
> views AND simplify your SQL.

AFAIK PostgreSQL does not support materialized views but it's 
interesting that you mention that because in essence the query is used 
to materialize a view, i.e., it's part of an INSERT / SELECT into a 
table which is then joined back to the other tables to construct a web 
page as well as an RSS feed.

Joe