X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4E8039DCCED for ; Thu, 16 Feb 2006 13:03:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36731-03 for ; Thu, 16 Feb 2006 13:03:34 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.194]) by postgresql.org (Postfix) with ESMTP id CBAAE9DCCD5 for ; Thu, 16 Feb 2006 13:03:29 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i5so242404wra for ; Thu, 16 Feb 2006 09:03:29 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=TLVzyqAmqUjpKYWpTnn/NcWs9zz+xqSgO1Uhz16QxALmY+4ijiA2Lo2KEBRzfhKaqGDFDlY3dvHI3w9wEIJVKzE+eTW0SUSbTUCWLmXL/wt+vVXbjjk/Jfi+RDfCfkNPB1IL0oPEQW2Jw9nSohZegmrSCrjR46LsfmcVg6PtWUo= Received: by 10.64.220.13 with SMTP id s13mr341821qbg; Thu, 16 Feb 2006 09:03:29 -0800 (PST) Received: by 10.65.231.4 with HTTP; Thu, 16 Feb 2006 09:03:29 -0800 (PST) Message-ID: Date: Thu, 16 Feb 2006 13:03:29 -0400 From: Adam Alkins To: pgsql-performance@postgresql.org Subject: Index Choice Problem MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/299 X-Sequence-Number: 17285 Hi List, I would like some insight from the experts here as to how I can alter which index PostgreSQL is choosing to run a query. First off, I'm running an active web forum (phpBB) with sometimes hundreds of concurrent users. The query in question is one which pulls the lists of topics in the forum. The table in question is here: -- forums=3D> \d phpbb_topics; Table "public.phpbb_topics" Column | Type | =20 Modifiers ----------------------+-----------------------+----------------------------= --------------------------- topic_id | integer | not null default nextval('phpbb_topics_id_seq'::text) forum_id | integer | not null default 0 topic_title | character varying(60) | not null default ''::character varying 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)::small= int topic_vote | smallint | not null default (0)::small= int topic_type | smallint | not null default (0)::small= int topic_first_post_id | integer | not null default 0 topic_last_post_id | integer | not null default 0 topic_moved_id | integer | not null default 0 topic_last_post_time | integer | not null default 0 Indexes: "forum_id_phpbb_topics_index" btree (forum_id) "topic_id_phpbb_topics_index" btree (topic_id) "topic_last_post_id_phpbb_topics_index" btree (topic_last_post_id) "topic_last_post_time_phpbb_topics_index" btree (topic_last_post_time) "topic_moved_id_phpbb_topics_index" btree (topic_moved_id) -- To layout the contents of the table, here are some relevant queries showing the number of entries forums=3D# SELECT COUNT(*) FROM phpbb_topics; SELECT COUNT(*) FROM phpbb_topics WHERE forum_id =3D 71; SELECT COUNT(*) FROM phpbb_topics WHERE forum_id =3D 55; count -------- 190588 (1 row) count ------- 1013 (1 row) count ------- 35035 (1 row) -- Ok. Now, here's the problem. I run a query to pull the list of topics for the forum. There pagination, so the first page query looks like this: SELECT t.topic_id =09=09=09FROM phpbb_topics AS t =09=09=09=09WHERE t.forum_id =3D 71 =09=09=09=09=09AND t.topic_id NOT IN (205026, 29046, 144569, 59780, 187424, 138635, 184973, 170551, 22419, 181690, 197254, 205130) =09=09=09=09=09=09ORDER BY t.topic_last_post_time DESC =09=09=09=09=09=09=09LIMIT 23 OFFSET 0 =20 =20 =20 QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------- Limit (cost=3D3487.78..3487.87 rows=3D34 width=3D8) (actual time=3D1112.921..1113.005 rows=3D34 loops=3D1) -> Sort (cost=3D3486.15..3489.10 rows=3D1181 width=3D8) (actual time=3D1112.087..1112.535 rows=3D687 loops=3D1) Sort Key: topic_last_post_time -> Index Scan using forum_id_phpbb_topics_index on phpbb_topics t (cost=3D0.00..3425.89 rows=3D1181 width=3D8) (actual time=3D54.650..1109.877 rows=3D1012 loops=3D1) Index Cond: (forum_id =3D 71) Filter: (topic_id <> 205026) Total runtime: 1113.268 ms (7 rows) -- This is the query on one of the lesser active forums (forum_id =3D 71) which as list earlier only has 1013 rows. This query slow because PostgreSQL is not using the index on the "forum_id" column, but instead scanning through the topics via the topic_last_post_time and filtering through the posts. This would be good for the forum_id =3D 55 where the most recent topics would be quickly found. Now here's the stranger part, going deeper into the results (ie selecting pages further down), the planner does this: -- SELECT t.topic_id =09=09=09FROM phpbb_topics AS t =09=09=09=09WHERE t.forum_id =3D 71 =09=09=09=09=09AND t.topic_id NOT IN (205026) =09=09=09=09=09=09ORDER BY t.topic_last_post_time DESC =09=09=09=09=09=09=09LIMIT 34 OFFSET 653 =20 QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------- Limit (cost=3D3487.78..3487.87 rows=3D34 width=3D8) (actual time=3D6.140..6.202 rows=3D34 loops=3D1) -> Sort (cost=3D3486.15..3489.10 rows=3D1181 width=3D8) (actual time=3D5.306..5.753 rows=3D687 loops=3D1) Sort Key: topic_last_post_time -> Index Scan using forum_id_phpbb_topics_index on phpbb_topics t (cost=3D0.00..3425.89 rows=3D1181 width=3D8) (actual time=3D0.070..3.581 rows=3D1012 loops=3D1) Index Cond: (forum_id =3D 71) Filter: (topic_id <> 205026) Total runtime: 6.343 ms (7 rows) -- This is more like how it should be done IMO. Results are much faster when the forum id index is used. Now, the output of the first query on the forum_id =3D 55 looks like this -- SELECT t.topic_id =09=09=09FROM phpbb_topics AS t =09=09=09=09WHERE t.forum_id =3D 55 =09=09=09=09=09AND t.topic_id NOT IN (159934, 168973, 79609, 179029, 61593, 184896, 190572) =09=09=09=09=09=09ORDER BY t.topic_last_post_time DESC =09=09=09=09=09=09=09LIMIT 28 OFFSET 0 =20 QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------- Limit (cost=3D0.00..50.50 rows=3D28 width=3D8) (actual time=3D0.060..0.71= 4 rows=3D28 loops=3D1) -> Index Scan Backward using topic_last_post_time_phpbb_topics_index on phpbb_topics t=20 (cost=3D0.00..63232.38 rows=3D35063 width=3D8) (actual time=3D0.057..0.675 rows=3D28 loops=3D1) Filter: ((forum_id =3D 55) AND (topic_id <> 159934) AND (topic_id <> 168973) AND (topic_id <> 79609) AND (topic_id <> 179029) AND (topic_id <> 61593) AND (topic_id <> 184896) AND (topic_id <> 190572)) Total runtime: 0.794 ms -- This is acceptable usage when the forum_id is heavily populated. Next now, here again puzzles me, pulling entries in the middle of forum_id =3D 55 -- SELECT t.topic_id =09=09=09FROM phpbb_topics AS t =09=09=09=09WHERE t.forum_id =3D 55 =09=09=09=09=09AND t.topic_id NOT IN (159934, 168973) =09=09=09=09=09=09ORDER BY t.topic_last_post_time DESC =09=09=09=09=09=09=09LIMIT 33 OFFSET 17458 QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------- Limit (cost=3D29302.43..29302.51 rows=3D33 width=3D8) (actual time=3D625.907..625.969 rows=3D33 loops=3D1) -> Sort (cost=3D29258.78..29346.44 rows=3D35064 width=3D8) (actual time=3D603.710..615.411 rows=3D17491 loops=3D1) Sort Key: topic_last_post_time -> Seq Scan on phpbb_topics t (cost=3D0.00..26611.85 rows=3D35064 width=3D8) (actual time=3D0.067..528.271 rows=3D35034 loops=3D= 1) Filter: ((forum_id =3D 55) AND (topic_id <> 159934) AND (topic_id <> 168973)) Total runtime: 632.444 ms (6 rows) -- Why is it doing a sequential scan? :( My questions... is there a method for me to suggest which index to use in the query. I'm think adding logic in my script depending on which forum_id is used (since I can hard code in my scripts which are the popular forums) and tell the planner to use a specific index first? Secondly, why in the last output did it opt to do a sequential scan over using the forum_id index as it did earlier. Side note, a vacuum analayze was done just prior to running these tests. Thank you, -- Adam Alkins http://www.rasadam.com Mobile: 868-680-4612