From: Aaron Bono <postgresql@aranya.com>
To: Joe <dev@freedomcircle.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Repetitive code
Date: Fri, 16 Jun 2006 09:44:42 -0500
Message-ID: <bf05e51c0606160744m5f28243cjf0fd6da11f48404d@mail.gmail.com> (raw)
In-Reply-To: <4492B248.7040406@freedomcircle.net>
References: <44920AA7.7070709@freedomcircle.net>
<bf05e51c0606151926n18555a65x28a0156f8bd217cc@mail.gmail.com>
<4492B248.7040406@freedomcircle.net>
I haven't stared at your query as long as you have so I may have missed
something but it looks like in all the selects you are combining the first
column in the select is the column you filter on. So the the outer query
doesn't have to know wiether it is a new or changed row:
SELECT * FROM (
SELECT created as my_date, topic_id, 0, 0, 0, 0 FROM topic
WHERE page_type IN (1, 2)
UNION
SELECT updated as my_date, topic_id, 1, 0, 0, 0 FROM topic
WHERE date_trunc('day', updated) != created
AND page_type IN (1, 2)
UNION
SELECT e.created as my_date, subject_id, 0, 1, entry_id, subject_type
FROM entry e, topic
WHERE subject_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.created as my_date, actor_id, 0, 1, entry_id, actor_type
FROM entry e, topic
WHERE actor_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.updated as my_date, subject_id, 1, 1, entry_id, subject_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
AND subject_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.updated as my_date, actor_id, 1, 1, entry_id, actor_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
AND actor_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.created as my_date, e.topic_id, 0, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id AND page_type IN (1, 2)
UNION
SELECT e.updated as my_date, e.topic_id, 1, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id
AND date_trunc('day', e.updated) != e.created
AND page_type IN (1, 2)
) my_union
where my_union.my_date >= $dt
I would almost be tempted to create a view for each small query and name
them something meaningful and then another view that does the union. It
would make the queries easier to understand at least (self documented).
-Aaron
On 6/16/06, Joe <dev@freedomcircle.net> wrote:
>
> 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.
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: postgresql@aranya.com, dev@freedomcircle.net
Subject: Re: Repetitive code
In-Reply-To: <bf05e51c0606160744m5f28243cjf0fd6da11f48404d@mail.gmail.com>
* 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