From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: Very strange query difference between 7.3.6 and 7.4.6 |
Date: | 2005-03-19 20:29:03 |
Message-ID: | 423C8B8F.9030205@commandprompt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
>"Joshua D. Drake" <jd(at)commandprompt(dot)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(at)commandprompt(dot)com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
Attachment | Content-Type | Size |
---|---|---|
jd.vcf | text/x-vcard | 285 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-19 23:53:37 | Avoiding unnecessary writes during relation drop and truncate |
Previous Message | Tom Lane | 2005-03-19 20:23:59 | Re: Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt) |