Re: Very strange query difference between 7.3.6 and 7.4.6

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(at)commandprompt(dot)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(at)postgresql(dot)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/