Re: Very strange query difference between 7.3.6 and 7.4.6

>>The rest are pretty basic integers.
>>
>>
>
>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:

Column | Type |
Modifiers
---------------------+----------------+---------------------------------------------------------------------
topic_id | integer | not null default
nextval('public.nuke_bbtopics_topic_id_seq'::text)
forum_id | smallint | not null default 0::smallint
topic_title | character(255) | not null default ''::bpchar
topic_poster | integer | not null default 0
topic_time | integer | not null default 0
topic_views | integer | not null default 0
topic_replies | integer | not null default 0
topic_status | smallint | not null default 0::smallint
topic_vote | smallint | not null default 0::smallint
topic_type | smallint | not null default 0::smallint
topic_last_post_id | integer | not null default 0
topic_first_post_id | integer | not null default 0
topic_moved_id | integer | not null default 0
news_id | integer | not null default 0
Indexes:
"nuke_bbtopics_pkey" primary key, btree (topic_id)
"forum_id_nuke_bbtopics" btree (forum_id)
"nuke_bbtopics_news_id" btree (news_id)
"topic_last_post_id_nuke_bbtopics" btree (topic_last_post_id)
"topic_type_nuke_bbtopics" btree (topic_type)
"topic_vote_nuke_bbtopics" btree (topic_vote)
Check constraints:
"$6" CHECK (topic_moved_id >= 0)
"$5" CHECK (topic_first_post_id >= 0)
"$4" CHECK (topic_last_post_id >= 0)
"$3" CHECK (topic_replies >= 0)
"$2" CHECK (topic_views >= 0)
"$1" CHECK (forum_id >= 0)

And the nuke_bbposts structure:

Table "public.nuke_bbposts"
Column | Type | Modifiers
-----------------+-----------------------+-------------------------------------------------------
post_id | integer | not null default
nextval('nuke_bbposts_id_seq'::text)
topic_id | integer | not null default 0
forum_id | smallint | not null default 0::smallint
poster_id | integer | not null default 0
post_time | integer | not null default 0
poster_ip | character varying(8) | not null default
''::character varying
post_username | character varying(25) |
enable_bbcode | smallint | not null default 1::smallint
enable_html | smallint | not null default 0::smallint
enable_smilies | smallint | not null default 1::smallint
enable_sig | smallint | not null default 1::smallint
post_edit_time | integer |
post_edit_count | smallint | not null default 0::smallint
Indexes:
"nuke_bbposts_pkey" primary key, btree (post_id)
"forum_id_nuke_bbposts_index" btree (forum_id)
"post_time_nuke_bbposts_index" btree (post_time)
"poster_id_nuke_bbposts_index" btree (poster_id)
"topic_id_nuke_bbposts_index" btree (topic_id)
Check constraints:
"$3" CHECK (post_edit_count >= 0)
"$2" CHECK (forum_id >= 0)
"$1" CHECK (topic_id >= 0)

And the nuke_bbforums:

Table "public.nuke_bbforums"
Column | Type |
Modifiers
--------------------+------------------------+--------------------------------------------------------------
forum_id | smallint | not null default
nextval('nuke_bbforums_forum_id_seq'::text)
cat_id | integer | not null default 0
forum_name | character varying(150) |
forum_desc | text |
forum_status | smallint | not null default 0::smallint
forum_order | integer | not null default 1
forum_posts | integer | not null default 0
forum_topics | integer | not null default 0
forum_last_post_id | integer | not null default 0
prune_next | integer |
prune_enable | smallint | not null default 1::smallint
auth_view | smallint | not null default 0::smallint
auth_read | smallint | not null default 0::smallint
auth_post | smallint | not null default 0::smallint
auth_reply | smallint | not null default 0::smallint
auth_edit | smallint | not null default 0::smallint
auth_delete | smallint | not null default 0::smallint
auth_sticky | smallint | not null default 0::smallint
auth_announce | smallint | not null default 0::smallint
auth_vote | smallint | not null default 0::smallint
auth_pollcreate | smallint | not null default 0::smallint
auth_attachments | smallint | not null default 0::smallint
auth_news | smallint | not null default 2::smallint
Indexes:
"nuke_bbforums_pkey" primary key, btree (forum_id)
Check constraints:
"$5" CHECK (forum_last_post_id >= 0)
"$4" CHECK (forum_topics >= 0)
"$3" CHECK (forum_posts >= 0)
"$2" CHECK (forum_order >= 0)
"$1" CHECK (cat_id >= 0)

And lastly... Here is the query:

SELECT 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,
COUNT(p2.post_id) AS prev_posts

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

GROUP BY 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

ORDER BY p.post_id ASC

Sincerely,

Joshua D. Drake

> I'm wondering how often the
>sort/group comparisons would even look at columns beyond the first
>two ...
>
> 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