"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 ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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/


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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/


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org