Re: Repetitive code

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.

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.

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.

-Aaron Bono

On 6/15/06, Joe <dev(at)freedomcircle(dot)net> wrote:
>
> Hi,
>
> This is prompted by the previous thread on "SQL Technique Question". I
> have the following query, extracted from a PHP script, where $dt is a
> date provided to the script.
>
> SELECT created, topic_id, 0, 0, 0, 0 FROM topic
> WHERE created >= $dt AND page_type IN (1, 2)
> UNION
> SELECT updated, topic_id, 1, 0, 0, 0 FROM topic
> WHERE date_trunc('day', updated) != created
> AND updated >= $dt AND page_type IN (1, 2)
> UNION
> SELECT e.created, subject_id, 0, 1, entry_id, subject_type
> FROM entry e, topic
> WHERE subject_id = topic_id AND e.created >= $dt
> AND page_type IN (1, 2)
> UNION
> SELECT e.created, actor_id, 0, 1, entry_id, actor_type
> FROM entry e, topic
> WHERE actor_id = topic_id AND e.created >= $dt
> AND page_type IN (1, 2)
> UNION
> SELECT e.updated, 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 e.updated >= $dt
> AND page_type IN (1, 2)
> UNION
> SELECT e.updated, 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 e.updated >= $dt
> AND page_type IN (1, 2)
> UNION
> SELECT e.created, e.topic_id, 0, 1, entry_id, rel_type
> FROM topic_entry e, topic t
> WHERE e.topic_id = t.topic_id AND e.created >= $dt
> AND page_type IN (1, 2)
> UNION
> SELECT e.updated, 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 e.updated >= $dt AND page_type IN (1, 2);
>
> As you can see, there's quite a bit of repetitive code, so the previous
> thread got me to thinking about simplifying it, perhaps through a view,
> perhaps through the use of CASE statements, particularly since I'm about
> to add at least one other table to the mix.
>
> As background, each table has a 'created' date column and an 'updated'
> timestamp column and the purpose of the various selects is to find the
> rows that were created or updated since the given $dt date. The third
> expression in each select list is an indicator of NEW (0) or CHANGED
> (1). The fourth item is a code for row type (topic=0, entry=1, but a
> new code is coming).
>
> I've been trying to figure out if simplifying into a view (one or more)
> is indeed possible. One factoring out that I can see is the "topics of
> interest" restriction (i.e., the join of each secondary table back to
> topic to get only topics whose page_types are 1 or 2). Another
> redundancy is the "date_trunc('day', updated) != created" which is there
> to avoid selecting "changed" records when they're actually new.
> However, although creating these views may simplify the subqueries it
> doesn't seem there is a way to avoid the eight-way UNION, or is there?
>
> TIA
>
> Joe