Re: Very strange query difference between 7.3.6 and 7.4.6 - Mailing list pgsql-hackers

>>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@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL