From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: Very strange query difference between 7.3.6 and 7.4.6 (7.3.6 kicking 7.4.6 butt) |
Date: | 2005-03-19 20:23:59 |
Message-ID: | 18007.1111263839@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
"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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2005-03-19 20:29:03 | Re: Very strange query difference between 7.3.6 and 7.4.6 |
Previous Message | Marc G. Fournier | 2005-03-19 20:19:14 | Re: GUC variable for setting number of local buffers |