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(at)commandprompt(dot)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