Re: Weird query plans for my queries, - Mailing list pgsql-general

> Van: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Verzonden: vrijdag 31 januari 2003 2:35
>
> You sure you tried the inner-join case?  I did
>
> explain SELECT F_Topics.TopicID,F_Topics.StatusID as
> StatusID, F_Users.UserID FROM F_Topics  JOIN F_Users USING
> (userid) WHERE (StatusID IN(1) AND F_Topics.Deleted = false
> AND ForumID = 15) OR (F_Topics.Lastmessage > '2002-08-01
> 23:27:03+02' AND F_Topics.Deleted =
> false AND ForumID = 15)
>
> and got the plan you wanted:
>
>  Nested Loop  (cost=0.00..14.51 rows=1 width=16)
>    ->  Index Scan using f_topics_forum_status_deleted,
> f_topics_forum_lastmessage_deleted on f_topics
> (cost=0.00..9.67 rows=1 width=12)
>          Index Cond: (((forumid = 15) AND (statusid = 1) AND
> (deleted = false)) OR ((forumid = 15) AND (lastmessage >
> '2002-08-01 17:27:03-04'::timestamp with time zone) AND
> (deleted = false)))
>          Filter: (((statusid = 1) AND (deleted = false) AND
> (forumid = 15)) OR ((lastmessage > '2002-08-01
> 17:27:03-04'::timestamp with time zone) AND (deleted = false)
> AND (forumid = 15)))
>    ->  Index Scan using f_users_pkey on f_users
> (cost=0.00..4.82 rows=1 width=4)
>          Index Cond: ("outer".userid = f_users.userid)
>

That is weird, a copy&paste of your command into my psql results in:

 Nested Loop  (cost=0.00..23788.87 rows=1 width=16)
   ->  Index Scan using f_topics_forum_status_deleted on f_topics
(cost=0.00..23785.84 rows=1 width=12)
         Index Cond: (forumid = 15)
         Filter: (((lastmessage > '2002-08-01 23:27:03'::timestamp
without time zone) OR (statusid = 1)) AND ((deleted = false) OR
(statusid = 1)) AND ((forumid = 15) OR (statusid = 1)) AND ((lastmessage
> '2002-08-01 23:27:03'::timestamp without time zone) OR (deleted =
false)) AND (deleted = false) AND ((forumid = 15) OR (deleted = false))
AND ((lastmessage > '2002-08-01 23:27:03'::timestamp without time zone)
OR (forumid = 15)) AND ((deleted = false) OR (forumid = 15)))
   ->  Index Scan using f_users_pkey on f_users  (cost=0.00..3.01 rows=1
width=4)
         Index Cond: ("outer".userid = f_users.userid)

Which is the same as the one as when using a normal join in the where
part.

> Of course the cost estimates are bogus because I have no data
> in the test tables, but the thing is capable of producing the
> plan you want.
I didn't doubt it could, I was even counting on it :)

Actually, when I tried *not* to have postgres use any of the
dataknowledge using this:

prepare the_query(integer, integer, timestamp) AS
SELECT TopicID, StatusID as StatusID, F_Users.UserID
FROM F_Topics INNER JOIN F_Users USING (UserID)
WHERE (StatusID IN ( $1 ) AND F_Topics.Deleted = false AND ForumID = $2
)
OR
(Lastmessage > $3 AND F_Topics.Deleted = false AND ForumID = $2 )

execute the_query(1, 15, '2002-08-01 23:27:03+02')

The execution time is 2 seconds, ie it is doing a sequential scan
somewhere along the path.

> I believe the reason the query you give above doesn't work
> like you want is that the planner first converts the whole
> WHERE clause to CNF form (canonical AND-of-OR layout), and
> then is unable to separate out the join clause from the
> spaghetti-like restriction clause.  The CNF-conversion
> heuristic is usually a good one, but not in this case.
>
> Writing the join clause as a JOIN clause keeps it separate
> from WHERE, preventing this mistake from being made.  Then
> the WHERE clause is already in DNF (canonical OR-of-ANDs)
> form, which the planner also likes, so it doesn't rearrange it.
That would be nice, but in my case postgres 7.3 decides to rearrange it
apparently.
For all variants, I know, that I tried the same explain output resulted.

Regards,

Arjen