Re: Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt)

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