X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 953DD9FA5E6 for ; Thu, 15 Jun 2006 23:27:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 87104-03 for ; Thu, 15 Jun 2006 23:26:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.207]) by postgresql.org (Postfix) with ESMTP id 09DD19FA160 for ; Thu, 15 Jun 2006 23:26:58 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id h30so377581wxd for ; Thu, 15 Jun 2006 19:26:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=AUwzRRXR1tvmJuni2zvBcoH9M80rUfKhg6JdVe7DyP4/KjClM0gD+UsOMkUnkYwcxP9WpjeIaFb5jXZx/87PbJ88Nx8z5DbTmTTms5XcmitiToP4oisIzq8w+5NW/KstXAnuOSNxTSiBZFd8FqNKSeVKviQqhk0SjTK0ckCBBpM= Received: by 10.70.33.8 with SMTP id g8mr3234528wxg; Thu, 15 Jun 2006 19:26:57 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Thu, 15 Jun 2006 19:26:57 -0700 (PDT) Message-ID: Date: Thu, 15 Jun 2006 21:26:57 -0500 From: "Aaron Bono" To: Joe Subject: Re: Repetitive code Cc: pgsql-sql@postgresql.org In-Reply-To: <44920AA7.7070709@freedomcircle.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_52005_23379706.1150424817655" References: <44920AA7.7070709@freedomcircle.net> X-Google-Sender-Auth: baf49e346aaca324 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/192 X-Sequence-Number: 25428 ------=_Part_52005_23379706.1150424817655 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 ------=_Part_52005_23379706.1150424817655 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@freedomcircle.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
------=_Part_52005_23379706.1150424817655--