Re: Very strange query difference between 7.3.6 and 7.4.6

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