In-Reply-To: <200503080548.17179.ler@lerctr.org>
References: <200503080548.17179.ler@lerctr.org>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <a5c61556de7ac42a8e84e857dcff3988@myrealbox.com>
Content-Transfer-Encoding: 7bit
Cc: pgsql-sql@postgresql.org
From: Michael Glaesemann <grzm@myrealbox.com>
Subject: Re: Query issue/8.0.1/Serendipity
Date: Tue, 8 Mar 2005 21:21:08 +0900
To: Larry Rosenman <ler@lerctr.org>

Larry,

Restating your SQL in a more reader-friendly form:

SELECT timestamp
FROM serendipity_entries e
     , serendipity_category c
     , serendipity_entrycat ec
LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended
     ON (e.id = ep_cache_extended.entryid
         AND ep_cache_extended.property = 'ep_cache_extended')
LEFT OUTER JOIN serendipity_entryproperties ep_cache_body
     ON (e.id = ep_cache_body.entryid
         AND ep_cache_body.property = 'ep_cache_body')
LEFT OUTER JOIN serendipity_entryproperties ep_access
     ON (e.id = ep_access.entryid
         AND ep_access.property = 'ep_access')
LEFT JOIN serendipity_entryproperties ep_sticky
     ON (e.id = ep_sticky.entryid
         AND ep_sticky.property = 'ep_is_sticky')
WHERE e.timestamp >= 1109656800
     AND e.timestamp <= 1112335200
     AND e.timestamp <= 1110241185
     AND e.isdraft = 'false'
     AND ( ep_access.property IS NULL
         OR ep_access.value = 'member'
         OR ep_access.value = 'public'
         OR (ep_access.value = 'private'
             AND e.authorid = 1)
         )
     AND e.id = ec.entryid
     AND c.categoryid = ec.categoryid
     AND c.category_left BETWEEN 3 AND 4


I can see you're using a lot of left joins. You are beginning your left 
joins off of ec, so I believe neither e nor c can be referenced as join 
conditions. It might work if you reorder the first part of the FROM 
clause as the following:

FROM serendipity_category c
     , serendipity_entrycat ec
     , serendipity_entries e
LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended

(Though I can't be sure without seeing table definitions.)

It also appears you have a redundant e.timestamp constraint in the 
WHERE clause: if e.timestamp is >= 1110241185 it's definitely going to 
be <= 1112335200

Hope this helps. I find white space helps me read my own SQL much more 
easily.

Michael Glaesemann
grzm myrealbox com