Specs:

7.3.6 machine Dual Athlon MP
2 GIG of ram,
4 Drive IDE (3ware) RAID 10
OS FC1 with 2.4 kernel

7.4.6 machine Dual Opteron MP (64bit PostgreSQL),
2 Gig of Ram
10 Drive RAID 10 with 128 Meg battery backed cache (3WARE).
OS FC3 x86_64 with 2.6 kernel

Essentials parameters:

7.3.6:

shared_buffers = 8192
wal_buffers = 2048
sort_mem = 4096
checkpoint_segments = 25
effective_cache_size = 65536
random_page_cost = 1.5
statistics_target = 150

7.4.6
Same as above except 8192 sort mem and 50 checkpoint segments

Both are running fsync with open_sync

Both have been vacuumed and analyze repeatedly while trying to figure
this out.

Explain Analyzes:

7.3.6 (old)

Aggregate (cost=320.49..324.89 rows=7 width=338) (actual time=630.21..630.21 rows=1 loops=1)
-> Group (cost=320.49..324.71 rows=70 width=338) (actual time=447.98..623.91 rows=8845 loops=1)
-> Sort (cost=320.49..320.67 rows=70 width=338) (actual time=447.95..460.77 rows=8845 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..318.33 rows=70 width=338) (actual time=0.41..109.19 rows=8845 loops=1)
-> Nested Loop (cost=0.00..17.55 rows=1 width=330) (actual time=0.11..0.34 rows=1 loops=1)
-> Nested Loop (cost=0.00..6.75 rows=1 width=291) (actual time=0.08..0.12 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.04..0.05 rows=1 loops=1)
Index Cond: (post_id = 352888)
-> Index Scan using nuke_bbtopics_pkey on nuke_bbtopics t (cost=0.00..3.53 rows=1 width=283) (actual time=0.02..0.05 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..10.78 rows=1 width=39) (actual time=0.02..0.21 rows=1 loops=1)
Index Cond: (f.forum_id = "outer".forum_id)
-> Index Scan using topic_id_nuke_bbposts_index on nuke_bbposts p2 (cost=0.00..299.33 rows=117 width=8) (actual time=0.29..31.68 rows=8845 loops=1)
Index Cond: (p2.topic_id = "outer".topic_id)
Filter: (post_id <= 352888)
Total runtime: 633.72 msec
(17 rows)

7.4.6 (new)

GroupAggregate (cost=209.11..213.73 rows=71 width=328) (actual time=3701.837..3701.837 rows=1 loops=1)
-> Sort (cost=209.11..209.29 rows=71 width=328) (actual time=2725.518..2728.590 rows=8845 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..206.93 rows=71 width=328) (actual time=0.621..190.112 rows=8845 loops=1)
-> Nested Loop (cost=0.00..9.04 rows=1 width=328) (actual time=0.347..0.365 rows=1 loops=1)
-> Nested Loop (cost=0.00..6.04 rows=1 width=291) (actual time=0.298..0.307 rows=1 loops=1)
-> Index Scan using nuke_bbposts_pkey on nuke_bbposts p (cost=0.00..3.01 rows=1 width=8) (actual time=0.209..0.212 rows=1 loops=1)
Index Cond: (post_id = 352888)
-> Index Scan using nuke_bbtopics_pkey on nuke_bbtopics t (cost=0.00..3.01 rows=1 width=283) (actual time=0.069..0.073 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..2.99 rows=1 width=39) (actual time=0.028..0.035 rows=1 loops=1)
Index Cond: (f.forum_id = "outer".forum_id)
-> Index Scan using topic_id_nuke_bbposts_index on nuke_bbposts p2 (cost=0.00..196.46 rows=114 width=8) (actual time=0.256..95.501 rows=8845 loops=1)
Index Cond: (p2.topic_id = "outer".topic_id)
Filter: (post_id <= 352888)
Total runtime: 3728.376 ms
(16 rows)


If you look at the second line in each explain it is the sort that is
causing the grief. On 7.3.6 it only takes say 447ms (on an completely
unused machine), on the Opteron it takes 2725.518.

The query on the opteron even after a fresh restart of apache and
PostgreSQL takes at least 1100 ms.

Other 7.4.6 information:

[root@www contrib]# mpstat
Linux 2.6.10-1.770_FC3smp (www.radioparadise.com) 03/19/2005

10:05:59 AM CPU %user %nice %system %iowait %irq %soft
%idle intr/s
10:05:59 AM all 4.48 0.00 0.79 2.96 0.01 0.04
91.72 1112.02


[root@www contrib]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
1 0 3560 230524 31196 1599572 0 0 10 189 3 3 4
1 92 3


[root@www contrib]# iostat
Linux 2.6.10-1.770_FC3smp (www.radioparadise.com) 03/19/2005

avg-cpu: %user %nice %sys %iowait %idle
4.48 0.00 0.84 2.96 91.72

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 29.72 38.28 757.12 49022777 969575882




The database is identical in the sense of schema (direct dump from 7.3.6
to 7.4.6).
Both were initalized with initdb --no-locale .

Any ideas?

Sincerely,

Joshua D. Drake




--
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 8: explain analyze is your friend

"Joshua D. Drake" <jd@commandprompt.com> writes:
> If you look at the second line in each explain it is the sort that is
> causing the grief.

The aggregation is a great deal worse as well. I suspect that for some
reason the comparison operations involved in the sorting and grouping
are much slower on the FC3 machine. What are the data types of the
leading sort keys ... and are you *certain* the FC3 database has
LC_COLLATE and LC_CTYPE set to C?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom Lane wrote:

>"Joshua D. Drake" <jd@commandprompt.com> writes:
>
>
>>If you look at the second line in each explain it is the sort that is
>>causing the grief.
>>
>>
>
>The aggregation is a great deal worse as well. I suspect that for some
>reason the comparison operations involved in the sorting and grouping
>are much slower on the FC3 machine. What are the data types of the
>leading sort keys ... and are you *certain* the FC3 database has
>LC_COLLATE and LC_CTYPE set to C?
>
>
Well I definately did a initdb --no-locale

34 bin/initdb -D cdata --no-locale

But I didn't specify LC_COLLATE or LC_CTYPE explicitly.

I did set LANG="C" in /etc/sysconfig/i18n however.

Sincerely,

Joshua D. Drake



> regards, tom lane
>
>


--
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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>>
>>
> Well I definately did a initdb --no-locale
>
> 34 bin/initdb -D cdata --no-locale
>
> But I didn't specify LC_COLLATE or LC_CTYPE explicitly.
>
> I did set LANG="C" in /etc/sysconfig/i18n however.

Just to be specific...

show all from psql:

lc_collate | C
lc_ctype | C
lc_messages | C
lc_monetary | C
lc_numeric | C
lc_time | C


Sincerely,

Joshua D. Drake



>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>> regards, tom lane
>>
>>
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>


--
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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>> The aggregation is a great deal worse as well. I suspect that for some
>> reason the comparison operations involved in the sorting and grouping
>> are much slower on the FC3 machine. What are the data types of the
>> leading sort keys ...
>>
> Data types are:

> post_id integer
> topic_id integer
> topic_title character(255) (I have no idea why )

> forum_status and forum_id are smallints...

> The rest are pretty basic integers.

Hm. What is the data like --- in particular, are the topic_ids unique
in the data processed by the sort? I'm wondering how often the
sort/group comparisons would even look at columns beyond the first
two ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>>The rest are pretty basic integers.
>>
>>
>
>Hm. What is the data like --- in particular, are the topic_ids unique
>in the data processed by the sort?
>
Yes topic_ids are the primary key. Here is the nuke_bbtopics structure:

Column | Type |
Modifiers
---------------------+----------------+---------------------------------------------------------------------
topic_id | integer | not null default
nextval('public.nuke_bbtopics_topic_id_seq'::text)
forum_id | smallint | not null default 0::smallint
topic_title | character(255) | not null default ''::bpchar
topic_poster | integer | not null default 0
topic_time | integer | not null default 0
topic_views | integer | not null default 0
topic_replies | integer | not null default 0
topic_status | smallint | not null default 0::smallint
topic_vote | smallint | not null default 0::smallint
topic_type | smallint | not null default 0::smallint
topic_last_post_id | integer | not null default 0
topic_first_post_id | integer | not null default 0
topic_moved_id | integer | not null default 0
news_id | integer | not null default 0
Indexes:
"nuke_bbtopics_pkey" primary key, btree (topic_id)
"forum_id_nuke_bbtopics" btree (forum_id)
"nuke_bbtopics_news_id" btree (news_id)
"topic_last_post_id_nuke_bbtopics" btree (topic_last_post_id)
"topic_type_nuke_bbtopics" btree (topic_type)
"topic_vote_nuke_bbtopics" btree (topic_vote)
Check constraints:
"$6" CHECK (topic_moved_id >= 0)
"$5" CHECK (topic_first_post_id >= 0)
"$4" CHECK (topic_last_post_id >= 0)
"$3" CHECK (topic_replies >= 0)
"$2" CHECK (topic_views >= 0)
"$1" CHECK (forum_id >= 0)

And the nuke_bbposts structure:

Table "public.nuke_bbposts"
Column | Type | Modifiers
-----------------+-----------------------+-------------------------------------------------------
post_id | integer | not null default
nextval('nuke_bbposts_id_seq'::text)
topic_id | integer | not null default 0
forum_id | smallint | not null default 0::smallint
poster_id | integer | not null default 0
post_time | integer | not null default 0
poster_ip | character varying(8) | not null default
''::character varying
post_username | character varying(25) |
enable_bbcode | smallint | not null default 1::smallint
enable_html | smallint | not null default 0::smallint
enable_smilies | smallint | not null default 1::smallint
enable_sig | smallint | not null default 1::smallint
post_edit_time | integer |
post_edit_count | smallint | not null default 0::smallint
Indexes:
"nuke_bbposts_pkey" primary key, btree (post_id)
"forum_id_nuke_bbposts_index" btree (forum_id)
"post_time_nuke_bbposts_index" btree (post_time)
"poster_id_nuke_bbposts_index" btree (poster_id)
"topic_id_nuke_bbposts_index" btree (topic_id)
Check constraints:
"$3" CHECK (post_edit_count >= 0)
"$2" CHECK (forum_id >= 0)
"$1" CHECK (topic_id >= 0)

And the nuke_bbforums:

Table "public.nuke_bbforums"
Column | Type |
Modifiers
--------------------+------------------------+--------------------------------------------------------------
forum_id | smallint | not null default
nextval('nuke_bbforums_forum_id_seq'::text)
cat_id | integer | not null default 0
forum_name | character varying(150) |
forum_desc | text |
forum_status | smallint | not null default 0::smallint
forum_order | integer | not null default 1
forum_posts | integer | not null default 0
forum_topics | integer | not null default 0
forum_last_post_id | integer | not null default 0
prune_next | integer |
prune_enable | smallint | not null default 1::smallint
auth_view | smallint | not null default 0::smallint
auth_read | smallint | not null default 0::smallint
auth_post | smallint | not null default 0::smallint
auth_reply | smallint | not null default 0::smallint
auth_edit | smallint | not null default 0::smallint
auth_delete | smallint | not null default 0::smallint
auth_sticky | smallint | not null default 0::smallint
auth_announce | smallint | not null default 0::smallint
auth_vote | smallint | not null default 0::smallint
auth_pollcreate | smallint | not null default 0::smallint
auth_attachments | smallint | not null default 0::smallint
auth_news | smallint | not null default 2::smallint
Indexes:
"nuke_bbforums_pkey" primary key, btree (forum_id)
Check constraints:
"$5" CHECK (forum_last_post_id >= 0)
"$4" CHECK (forum_topics >= 0)
"$3" CHECK (forum_posts >= 0)
"$2" CHECK (forum_order >= 0)
"$1" CHECK (cat_id >= 0)

And lastly... Here is the query:

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(p2.post_id) AS prev_posts

FROM nuke_bbtopics t, nuke_bbforums f, nuke_bbposts p, nuke_bbposts p2
WHERE p.post_id = 352888
AND t.topic_id = p.topic_id
AND p2.topic_id = p.topic_id
AND p2.post_id <= 352888
AND f.forum_id = t.forum_id

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

Sincerely,

Joshua D. Drake





> I'm wondering how often the
>sort/group comparisons would even look at columns beyond the first
>two ...
>
> regards, tom lane
>
>


--
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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Joshua D. Drake" <jd@commandprompt.com> writes:
>> Hm. What is the data like --- in particular, are the topic_ids unique
>> in the data processed by the sort?
>>
> Yes topic_ids are the primary key. Here is the nuke_bbtopics structure:

Hmmm ... because p.post_id has only a single value allowed by the WHERE
clause, and that in turn determines single t and f rows, the SORT step
is actually seeing thousands of rows that have all the *same* sort key.
Ditto for the grouping step.

I can't offhand see any change between 7.3 and 7.4 that would make 7.4
much worse on this corner case. Maybe the problem is in the glibc
qsort() routine? It would be good to try the case in 7.3 and 7.4 on
identical platforms.

I have 7.3 and 7.4 built here on FC3, so if you don't, you could send me
the data off-list. I'd just need the info going into the sort, ie

create table foo as select
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
FROM nuke_bbtopics t, nuke_bbforums f, nuke_bbposts p, nuke_bbposts p2
WHERE p.post_id = 352888
AND t.topic_id = p.topic_id
AND p2.topic_id = p.topic_id
AND p2.post_id <= 352888
AND f.forum_id = t.forum_id;

and send a pg_dump of foo.

regards, tom lane

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

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Let me know what you come up with. Thanks for the help.

Hmph. On my FC3 machine, 7.4 is consistently faster than 7.3 in sorting
and grouping this data --- it's about 710 vs 960 msec. (This is on a P4
1.8GHz, presumably slower than your machines.) So there's no
algorithmic change that might be biting us. It seems we have to look at
the platforms involved. At this point I can think of two hypotheses
that haven't been eliminated:
1. FC1's qsort is much faster than FC3's on this case.
2. The 64-bit build has got some kind of performance problem
that's not generic to 7.4.*.
#1 doesn't seem very probable, though it's possible. I think what you
should do next is build 7.3 on the 64-bit machine and see what performance
it's got. You might also try non-64-bit builds and see what they do.

Just FYI, you can test the behavior without loading your full database
--- just load the data you sent me and do

explain analyze
select count(*) from foo
group by
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
;

On 7.4 and up you may have to set enable_hashagg = off to force a
Sort/GroupAggregate plan instead of HashAggregate.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane wrote:

>"Joshua D. Drake" <jd@commandprompt.com> writes:
>
>
>>Let me know what you come up with. Thanks for the help.
>>
>>
>
>Hmph. On my FC3 machine, 7.4 is consistently faster than 7.3 in sorting
>and grouping this data --- it's about 710 vs 960 msec. (This is on a P4
>1.8GHz, presumably slower than your machines.) So there's no
>algorithmic change that might be biting us. It seems we have to look at
>the platforms involved. At this point I can think of two hypotheses
>that haven't been eliminated:
> 1. FC1's qsort is much faster than FC3's on this case.
> 2. The 64-bit build has got some kind of performance problem
> that's not generic to 7.4.*.
>#1 doesn't seem very probable, though it's possible. I think what you
>should do next is build 7.3 on the 64-bit machine and see what performance
>it's got. You might also try non-64-bit builds and see what they do.
>
>
>
O.k. thanks for the help. I will take a look and let you know the results.

Sincerely,

Joshua D. Drake


>Just FYI, you can test the behavior without loading your full database
>--- just load the data you sent me and do
>
>explain analyze
>select count(*) from foo
>group by
> 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
>;
>
>On 7.4 and up you may have to set enable_hashagg = off to force a
>Sort/GroupAggregate plan instead of HashAggregate.
>
> regards, tom lane
>
>


--
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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

>>
>> On 7.4 and up you may have to set enable_hashagg = off to force a
>> Sort/GroupAggregate plan instead of HashAggregate.
>
O.k. on FC2 7.4.6 64bit I get:


-------------------------------------------------------------------------------------------------------------
HashAggregate (cost=80.00..82.50 rows=1000 width=404) (actual
time=235.064..235.068 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=404) (actual
time=0.024..10.409 rows=8845 loops=1)
Total runtime: 236.703 ms
(3 rows)


With enable_hashagg on... With it enable_hashagg off I get:

GroupAggregate (cost=69.83..134.83 rows=1000 width=404) (actual
time=688.150..688.151 rows=1 loops=1)
-> Sort (cost=69.83..72.33 rows=1000 width=404) (actual
time=543.251..554.363 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.008..7.635 rows=8845 loops=1)
Total runtime: 690.881 ms
(5 rows)

On the FC3 64bit, I am seeing similar results:

With enable_hashagg on:

QUERY PLAN
---------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1041.15..1041.15 rows=1 width=333) (actual
time=260.543..260.544 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..510.45 rows=8845 width=333) (actual
time=11.638..68.744 rows=8845 loops=1)
Total runtime: 261.195 ms
(3 rows)


With enable_hashagg off:

QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1090.27..1643.08 rows=1 width=333) (actual
time=1075.690..1075.690 rows=1 loops=1)
-> Sort (cost=1090.27..1112.38 rows=8845 width=333) (actual
time=943.242..946.261 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..510.45 rows=8845 width=333)
(actual time=0.044..15.936 rows=8845 loops=1)
Total runtime: 1084.778 ms
(5 rows)


Odd that FC3 is so much slower, the FC3 machine puts the FC2 machine
to shame for IO.

However, The source query doesn't choose a hashagg on the FC3 machine,
which your
test case does. I am having problems getting 7.3.9 to start on the FC3
machine.
Very weird, I get this error:

IpcSemaphoreCreate: semget(key=5435117, num=17, 03600) failed: No space
left on device

Of which I am familiar with and know how to fix. However, I get the
error even with
default settings with the other instance of PostgreSQL (the 7.4.6)
shutdown. So I am
at a loss there.

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)
-> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=404) (actual
time=0.018..10.218 rows=8845 loops=1)
Total runtime: 210.580 ms
(3 rows)

enable_hashagg off:

GroupAggregate (cost=69.83..134.83 rows=1000 width=404) (actual
time=661.197..661.198 rows=1 loops=1)
-> Sort (cost=69.83..72.33 rows=1000 width=404) (actual
time=517.531..528.360 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.008..7.728 rows=8845 loops=1)
Total runtime: 663.903 ms
(5 rows)


So at this point, from what I can tell FC3 64bit 7.4.6 is slower by an
at least 400ms (with the wrong plan) and is choosing the wrong plan. Yet
FC2 doesn't have these issues. Hmmm....

FC2 has glibc 2.3.3 and gcc 3.3.3
FC3 has glibc 2.3.4 and gcc 3.4.2

What next?

Sincerely,

Joshua D. Drake








>>
>> regards, tom lane
>>
>>
>
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" 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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org