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

Hello,

O.k. this is the wrong query, but it still shows the odd slowness. I am
going to test the full dataset on FC2 and see what happens.

J

On Mon, 2005-03-21 at 10:58 -0800, Joshua D. Drake wrote:
> 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.45 rows=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)
>                ->  Nested Loop  (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_pkey on
> 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)
>                ->  Seq Scan 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.18 rows=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_pkey on
> 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)
>                ->  Index Scan 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/