Re: Very strange query difference between 7.3.6 and 7.4.6

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/