From: Joe <dev@freedomcircle.net>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Repetitive code
Date: Fri, 16 Jun 2006 09:29:44 -0400
Message-ID: <4492B248.7040406@freedomcircle.net> (raw)
In-Reply-To: <bf05e51c0606151926n18555a65x28a0156f8bd217cc@mail.gmail.com>
References: <44920AA7.7070709@freedomcircle.net>
<bf05e51c0606151926n18555a65x28a0156f8bd217cc@mail.gmail.com>
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
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: dev@freedomcircle.net, postgresql@aranya.com
Subject: Re: Repetitive code
In-Reply-To: <4492B248.7040406@freedomcircle.net>
* 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