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 251CE9FA5C3 for ; Fri, 16 Jun 2006 11:44:50 -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 42445-09 for ; Fri, 16 Jun 2006 11:44:43 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.201]) by postgresql.org (Postfix) with ESMTP id E2A539FA42B for ; Fri, 16 Jun 2006 11:44:43 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id h30so456032wxd for ; Fri, 16 Jun 2006 07:44:42 -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=uEG1+TIjzL+fof1sLKwZWzfAm6qzbs4WCR4OPKuHD8vL7Huac86ODK8tsFQfqYIqvnodAjvwBVFlwuLxjE+zbF87zYXEn6Q0MNnmEYHxV/noe+JNVK3Q5m+MKB1QW+57FUZFd9YIb9d/CaN3Qd+TBuAPCv0TyWwxYOZ7QvETxsQ= Received: by 10.70.99.8 with SMTP id w8mr610557wxb; Fri, 16 Jun 2006 07:44:42 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Fri, 16 Jun 2006 07:44:42 -0700 (PDT) Message-ID: Date: Fri, 16 Jun 2006 09:44:42 -0500 From: "Aaron Bono" To: Joe Subject: Re: Repetitive code Cc: pgsql-sql@postgresql.org In-Reply-To: <4492B248.7040406@freedomcircle.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_61971_28740505.1150469082523" References: <44920AA7.7070709@freedomcircle.net> <4492B248.7040406@freedomcircle.net> X-Google-Sender-Auth: 38af3caba553582e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/213 X-Sequence-Number: 25449 ------=_Part_61971_28740505.1150469082523 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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. ------=_Part_61971_28740505.1150469082523 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.

------=_Part_61971_28740505.1150469082523--