PHPBB3-9856

main

qi_t21=> ANALYZE phpbb_topics;
ANALYZE
qi_t21=> explain SELECT t.topic_id
qi_t21-> FROM phpbb_topics t
qi_t21-> WHERE t.forum_id = 2
qi_t21-> AND t.topic_type IN (0, 1)
qi_t21-> ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
qi_t21-> LIMIT 25 OFFSET 100;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=1582.37..1582.43 rows=25 width=10)
   ->  Sort  (cost=1582.12..1623.31 rows=16478 width=10)
         Sort Key: topic_type, topic_last_post_time
         ->  Index Scan using phpbb_topics_forum_id on phpbb_topics t  (cost=0.00..925.81 rows=16478 width=10)
               Index Cond: (forum_id = 2)
               Filter: (topic_type = ANY ('{0,1}'::integer[]))
(6 rows)

qi_t21=> create index fast on phpbb_topics (forum_id, topic_type, topic_last_post_time);
CREATE INDEX
qi_t21=> ANALYZE phpbb_topics;                                                      
ANALYZE
qi_t21=> explain SELECT t.topic_id                                                      
FROM phpbb_topics t
WHERE t.forum_id = 2
AND t.topic_type IN (0, 1)
ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
LIMIT 25 OFFSET 100;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Limit  (cost=15.31..19.13 rows=25 width=10)
   ->  Index Scan Backward using fast on phpbb_topics t  (cost=0.00..2529.86 rows=16527 width=10)
         Index Cond: (forum_id = 2)
         Filter: (topic_type = ANY ('{0,1}'::integer[]))
(4 rows)

qi_t21=> explain SELECT t.topic_id
FROM phpbb_topics t
WHERE t.forum_id = 2
AND t.topic_type IN (0, 1) and topic_approved=1
ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
LIMIT 25 OFFSET 100;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Limit  (cost=15.54..19.43 rows=25 width=10)
   ->  Index Scan Backward using fast on phpbb_topics t  (cost=0.00..2689.57 rows=17303 width=10)
         Index Cond: (forum_id = 2)
         Filter: ((topic_type = ANY ('{0,1}'::integer[])) AND (topic_approved = 1))
(4 rows)

qi_t21=> select count(*) from phpbb_topics;
 count 
-------
 44001
(1 row)

This was a quickinstall set to create between 10,000-20,000 topics per forum.

sup1

Postgres prefers sort + table scan over index scan for small tables/result sets:

qi_t225=> ANALYZE phpbb_topics;
ANALYZE
qi_t225=> explain SELECT t.topic_id                                                                                           
FROM phpbb_topics t
WHERE t.forum_id = 4
AND t.topic_type IN (0, 1)
ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
LIMIT 25 OFFSET 0;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Limit  (cost=5.19..5.24 rows=23 width=10)
   ->  Sort  (cost=5.19..5.24 rows=23 width=10)
         Sort Key: topic_type, topic_last_post_time
         ->  Seq Scan on phpbb_topics t  (cost=0.00..4.67 rows=23 width=10)
               Filter: ((topic_type = ANY ('{0,1}'::integer[])) AND (forum_id = 4))
(5 rows)

qi_t225=> alter table phpbb_topics drop CONSTRAINT phpbb_topics_pkey;
ALTER TABLE
qi_t225=> insert into phpbb_topics select * from phpbb_topics;
INSERT 0 111
qi_t225=> explain SELECT t.topic_id
FROM phpbb_topics t
WHERE t.forum_id = 4
AND t.topic_type IN (0, 1)
ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
LIMIT 25 OFFSET 0;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..10.04 rows=25 width=10)
   ->  Index Scan Backward using phpbb_topics_forum_id_type_last_post_time on phpbb_topics t  (cost=0.00..18.48 rows=46 width=10)
         Index Cond: (forum_id = 4)
         Filter: (topic_type = ANY ('{0,1}'::integer[]))
(4 rows)

qi_t225=> select count(*) from phpbb_topics;
 count 
-------
   222
(1 row)

sup2

Redundant index drops:

qi_t225=> select forum_id,count(*) from phpbb_topics group by forum_id;
 forum_id | count 
----------+-------
        6 |    64
        8 |    64
       12 |   896
        2 |   640
       10 |    64
       11 |   640
        4 |  1472
        5 |  1472
        9 |   640
        7 |  1152
(10 rows)

qi_t225=> insert into phpbb_topics select * from phpbb_topics;
INSERT 0 7104
qi_t225=> EXPLAIN select * from phpbb_topics where forum_id=4 limit 5;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Limit  (cost=0.00..0.87 rows=5 width=147)
   ->  Seq Scan on phpbb_topics  (cost=0.00..508.06 rows=2935 width=147)
         Filter: (forum_id = 4)
(3 rows)

qi_t225=> insert into phpbb_topics select * from phpbb_topics;
INSERT 0 14208
qi_t225=> EXPLAIN select * from phpbb_topics where forum_id=4 limit 5;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Limit  (cost=0.00..0.87 rows=5 width=147)
   ->  Seq Scan on phpbb_topics  (cost=0.00..1014.60 rows=5861 width=147)
         Filter: (forum_id = 4)
(3 rows)

qi_t225=> EXPLAIN select * from phpbb_topics where forum_id=10 limit 5;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Limit  (cost=6.23..15.91 rows=5 width=147)
   ->  Bitmap Heap Scan on phpbb_topics  (cost=6.23..500.13 rows=255 width=147)
         Recheck Cond: (forum_id = 10)
         ->  Bitmap Index Scan on phpbb_topics_forum_id_type  (cost=0.00..6.17 rows=255 width=0)
               Index Cond: (forum_id = 10)
(5 rows)

qi_t225=> EXPLAIN analyze select * from phpbb_topics where forum_id=10 limit 5;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6.23..15.91 rows=5 width=147) (actual time=0.188..0.216 rows=5 loops=1)
   ->  Bitmap Heap Scan on phpbb_topics  (cost=6.23..500.13 rows=255 width=147) (actual time=0.184..0.198 rows=5 loops=1)
         Recheck Cond: (forum_id = 10)
         ->  Bitmap Index Scan on phpbb_topics_forum_id_type  (cost=0.00..6.17 rows=255 width=0) (actual time=0.103..0.103 rows=256 loops=1)
               Index Cond: (forum_id = 10)
 Total runtime: 0.301 ms
(6 rows)

qi_t225=> EXPLAIN select * from phpbb_topics where forum_id=4 limit 5;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Limit  (cost=0.00..0.87 rows=5 width=147)
   ->  Seq Scan on phpbb_topics  (cost=0.00..1014.60 rows=5861 width=147)
         Filter: (forum_id = 4)
(3 rows)

qi_t225=> create index test on phpbb_topics (forum_id);
CREATE INDEX
qi_t225=> EXPLAIN select * from phpbb_topics where forum_id=4 limit 5;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Limit  (cost=0.00..0.86 rows=5 width=147)
   ->  Seq Scan on phpbb_topics  (cost=0.00..1016.20 rows=5888 width=147)
         Filter: (forum_id = 4)
(3 rows)

Conclusions:

1. Postgres seems to really want selectivity from indexes to take them over table scans.
2. That query plans depend on actual data values passed in is very interesting.

So far this seems to support the idea of dropping indexes on forum_id and (forum_id, topic_type).