Re: Very strange query difference between 7.3.6 and 7.4.6 - Mailing list pgsql-hackers

Hello,

O.k. here is a great one for you. Here are some further comparisons:

8.0.1 FC3 64bit:

foo=# explain analyze SELECT t.topic_id, t.topic_title, t.topic_status,
t.topic_replies, t.topic_time, t.topic_type, t.topic_vote,
t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id,
f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit,
f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate,
f.auth_vote, f.auth_attachments, COUNT(p.post_id) AS prev_posts
foo-#                 FROM nuke_bbtopics t, nuke_bbforums f,
nuke_bbposts p
foo-#                 WHERE p.post_id = 624854 AND t.topic_id =
p.topic_id
foo-#                 AND p.topic_id = p.topic_id AND p.post_id <=
624854
foo-#                         AND f.forum_id = t.forum_id
foo-#                         GROUP BY p.post_id, t.topic_id,
t.topic_title, t.topic_status, t.topic_replies, t.topic_time,
t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name,
f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post,
f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky,
f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments
ORDER BY p.post_id ASC;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=13.44..13.51 rows=1 width=404) (actual time=0.233..0.234 rows=1 loops=1)  ->  Sort
(cost=13.44..13.45rows=1 width=404) (actual
 
time=0.196..0.196 rows=1 loops=1)        Sort Key: p.post_id, t.topic_id, t.topic_title, t.topic_status,
t.topic_replies, t.topic_time, t.topic_type, t.topic_vote,
t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id,
f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit,
f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate,
f.auth_vote, f.auth_attachments        ->  Nested Loop  (cost=0.00..13.43 rows=1 width=404) (actual
time=0.098..0.129 rows=1 loops=1)              Join Filter: ("inner".forum_id = "outer".forum_id)              ->
NestedLoop  (cost=0.00..12.03 rows=1 width=287)
 
(actual time=0.071..0.075 rows=1 loops=1)                    ->  Index Scan using nuke_bbposts_pkey on
nuke_bbposts p  (cost=0.00..6.02 rows=1 width=8) (actual
time=0.045..0.046 rows=1 loops=1)                          Index Cond: ((post_id = 624854) AND (post_id
<= 624854))                          Filter: (topic_id = topic_id)                    ->  Index Scan using
nuke_bbtopics_pkeyon
 
nuke_bbtopics t  (cost=0.00..6.00 rows=1 width=283) (actual
time=0.012..0.014 rows=1 loops=1)                          Index Cond: (t.topic_id = "outer".topic_id)              ->
SeqScan on nuke_bbforums f  (cost=0.00..1.18 rows=18
 
width=119) (actual time=0.004..0.018 rows=18 loops=1)Total runtime: 0.530 ms
(13 rows)

7.4.6 FC3 64bit:


rp_nuke=# explain analyze SELECT t.topic_id, t.topic_title,
t.topic_status, t.topic_replies, t.topic_time, t.topic_type,
t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status,
f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply,
f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce,
f.auth_pollcreate, f.auth_vote, f.auth_attachments, COUNT(p.post_id) AS
prev_posts
rp_nuke-#                 FROM nuke_bbtopics t, nuke_bbforums f,
nuke_bbposts p
rp_nuke-#                 WHERE p.post_id = 624854 AND t.topic_id =
p.topic_id
rp_nuke-#                 AND p.topic_id = p.topic_id AND p.post_id <=
624854
rp_nuke-#                         AND f.forum_id = t.forum_id
rp_nuke-#                         GROUP BY p.post_id, t.topic_id,
t.topic_title, t.topic_status, t.topic_replies, t.topic_time,
t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name,
f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post,
f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky,
f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments
ORDER BY p.post_id ASC
rp_nuke-#
rp_nuke-# ;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=10.18..10.25 rows=1 width=324) (actual time=0.502..0.502 rows=1 loops=1)  ->  Sort
(cost=10.18..10.18rows=1 width=324) (actual
 
time=0.460..0.461 rows=1 loops=1)        Sort Key: p.post_id, t.topic_id, t.topic_title, t.topic_status,
t.topic_replies, t.topic_time, t.topic_type, t.topic_vote,
t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id,
f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit,
f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate,
f.auth_vote, f.auth_attachments        ->  Nested Loop  (cost=0.00..10.17 rows=1 width=324) (actual
time=0.190..0.206 rows=1 loops=1)              ->  Nested Loop  (cost=0.00..6.69 rows=1 width=287)
(actual time=0.143..0.146 rows=1 loops=1)                    ->  Index Scan using nuke_bbposts_pkey on
nuke_bbposts p  (cost=0.00..3.21 rows=1 width=8) (actual
time=0.094..0.095 rows=1 loops=1)                          Index Cond: ((post_id = 624854) AND (post_id
<= 624854))                          Filter: (topic_id = topic_id)                    ->  Index Scan using
nuke_bbtopics_pkeyon
 
nuke_bbtopics t  (cost=0.00..3.46 rows=1 width=283) (actual
time=0.029..0.030 rows=1 loops=1)                          Index Cond: (t.topic_id = "outer".topic_id)              ->
IndexScan using nuke_bbforums_pkey on nuke_bbforums
 
f  (cost=0.00..3.47 rows=1 width=39) (actual time=0.030..0.041 rows=1
loops=1)                    Index Cond: (f.forum_id = "outer".forum_id)Total runtime: 1.126 ms
(13 rows)

Sincerely,

Joshua D. Drake



On Sun, 2005-03-20 at 20:21 -0800, Joshua D. Drake wrote:
> Tom Lane wrote:
> 
> >"Joshua D. Drake" <jd@commandprompt.com> writes:
> >  
> >
> >>O.k. I got 7.3.9 to operate as expected on FC2 (64bit) and these are my 
> >>results:
> >>    
> >>
> >
> >  
> >
> >>enable_hashagg on:
> >>    
> >>
> >
> >  
> >
> >> HashAggregate  (cost=80.00..82.50 rows=1000 width=404) (actual 
> >>time=209.746..209.750 rows=1 loops=1)
> >>    
> >>
> >
> >You got confused somewhere along the line, because 7.3 doesn't have
> >hash aggregation ...
> >  
> >
> DOH! You are correct. Heh... Anyway here is the real 7.3.9
> on FC2 64bit plan:
> RY 
> PLAN
                                    
 
> 
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=69.83..132.33 rows=100 width=404) (actual 
> time=771.96..771.96 rows=1 loops=1)
>    ->  Group  (cost=69.83..129.83 rows=1000 width=404) (actual 
> time=579.98..767.54 rows=8845 loops=1)
>          ->  Sort  (cost=69.83..72.33 rows=1000 width=404) (actual 
> time=579.96..590.00 rows=8845 loops=1)
>                Sort Key: post_id, topic_id, topic_title, topic_status, 
> topic_replies, topic_time, topic_type, topic_vote, topic_last_post_id, 
> forum_name, forum_status, forum_id, auth_view, auth_read, auth_post, 
> auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce, 
> auth_pollcreate, auth_vote, auth_attachments
>                ->  Seq Scan on foo  (cost=0.00..20.00 rows=1000 
> width=404) (actual time=0.05..107.62 rows=8845 loops=1)
>  Total runtime: 774.57 msec
> (6 rows)
> 
> 
> 
> 
> >            regards, tom lane
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >  
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
-- 
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/