ISSUE #3:
2008-01-04 11:29:04 CET LOG: instruction : SELECT u.*, z.friend, z.foe, p.* FROM phpbb_users u, phpbb_posts p LEFT JOIN phpbb_zebra z ON (z.user_id = 104595 AND z.zebra_id = p.poster_id) WHERE p.post_id IN ('320002', '320006', '320014', '320019', '320022', '320025', '320050', '320088', '320090', '320096', '320100', '320101', '320104', '320108', '320124', '320143', '320148', '320198', '320199', '320235', '320241', '320269', '320276', '320282', '320299', '320306', '320315', '320402', '320408', '320435', '320494', '320533', '320639', '320742', '320950', '321065', '321258', '321318', '321319', '321476', '321487', '321834')
AND u.user_id = p.poster_id
2008-01-04 11:29:04 CET LOG: durée : 29.609 ms
In PostgreSQL, each query should execute in less than 0.5 microsecond.
Let's analyse the query:
explain analyse SELECT u.*, z.friend, z.foe, p.* FROM phpbb_users u, phpbb_posts p LEFT JOIN phpbb_zebra z ON (z.user_id = 104595 AND z.zebra_id = p.poster_id) WHERE p.post_id IN ('320002', '320006', '320014', '320019', '320022', '320025', '320050', '320088', '320090', '320096', '320100', '320101', '320104', '320108', '320124', '320143', '320148', '320198', '320199', '320235', '320241', '320269', '320276', '320282', '320299', '320306', '320315', '320402', '320408', '320435', '320494', '320533', '320639', '320742', '320950', '321065', '321258', '321318', '321319', '321476', '321487', '321834')
phpbb3-# AND u.user_id = p.poster_id
phpbb3-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=180.47..634.74 rows=42 width=1095) (actual time=0.265..0.754 rows=42 loops=1)
Hash Cond: (p.poster_id = z.zebra_id)
-> Nested Loop (cost=178.92..633.02 rows=42 width=1091) (actual time=0.194..0.646 rows=42 loops=1)
-> Bitmap Heap Scan on phpbb_posts p (cost=178.92..345.21 rows=42 width=651) (actual time=0.167..0.277 rows=42 loops=1)
Recheck Cond: (post_id = ANY ('{320002,320006,320014,320019,320022,320025,320050,320088,320090,320096,320100,320101,320104,320108,320124,320143,320148,320198,320199,320235,320241,320269,320276,320282,320299,320306,320315,320402,320408,320435,320494,320533,320639,320742,320950,321065,321258,321318,321319,321476,321487,321834}'::integer[]))
-> Bitmap Index Scan on phpbb_posts_pkey (cost=0.00..178.91 rows=42 width=0) (actual time=0.154..0.154 rows=42 loops=1)
Index Cond: (post_id = ANY ('{320002,320006,320014,320019,320022,320025,320050,320088,320090,320096,320100,320101,320104,320108,320124,320143,320148,320198,320199,320235,320241,320269,320276,320282,320299,320306,320315,320402,320408,320435,320494,320533,320639,320742,320950,321065,321258,321318,321319,321476,321487,321834}'::integer[]))
-> Index Scan using phpbb_users_pkey on phpbb_users u (cost=0.00..6.84 rows=1 width=440) (actual time=0.005..0.006 rows=1 loops=42)
Index Cond: (u.user_id = p.poster_id)
-> Hash (cost=1.54..1.54 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)
-> Seq Scan on phpbb_zebra z (cost=0.00..1.54 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1)
Filter: (user_id = 104595)
Total runtime: 0.976 ms
(13 lignes)
There is a sequential scan due to a mixed OLD join with modern LEFT JOIN.
The query should be (I am not sure, please check):
SELECT u.*, z.friend, z.foe, p.* FROM phpbb_posts p
INNER JOIN phpbb_users u ON u.user_id = p.poster_id
LEFT JOIN phpbb_zebra z ON (z.user_id = 104595 AND z.zebra_id = p.poster_id)
WHERE p.post_id IN ('320002', '320006', '320014', '320019', '320022', '320025', '320050', '320088', '320090', '320096', '320100', '320101', '320104', '320108', '320124', '320143', '320148', '320198', '320199', '320235', '320241', '320269', '320276', '320282', '320299', '320306', '320315', '320402', '320408', '320435', '320494', '320533', '320639', '320742', '320950', '321065', '321258', '321318', '321319', '321476', '321487', '321834')
Also, avoid '*' in queries. It eats up you CPU time in both PostgreSQL and PHP.
Let's analyse the new query:
phpbb3=# explain analyse
phpbb3-# SELECT u.*, z.friend, z.foe, p.* FROM phpbb_posts p
phpbb3-# INNER JOIN phpbb_users u ON u.user_id = p.poster_id
phpbb3-# LEFT JOIN phpbb_zebra z ON (z.user_id = 104595 AND z.zebra_id = p.poster_id)
phpbb3-# WHERE p.post_id IN ('320002', '320006', '320014', '320019', '320022', '320025', '320050', '320088', '320090', '320096', '320100', '320101', '320104', '320108', '320124', '320143', '320148', '320198', '320199', '320235', '320241', '320269', '320276', '320282', '320299', '320306', '320315', '320402', '320408', '320435', '320494', '320533', '320639', '320742', '320950', '321065', '321258', '321318', '321319', '321476', '321487', '321834');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=180.47..634.74 rows=42 width=1099) (actual time=0.277..0.788 rows=42 loops=1)
-> Hash Left Join (cost=180.47..346.93 rows=42 width=655) (actual time=0.251..0.441 rows=42 loops=1)
Hash Cond: (p.poster_id = z.zebra_id)
-> Bitmap Heap Scan on phpbb_posts p (cost=178.92..345.21 rows=42 width=651) (actual time=0.179..0.316 rows=42 loops=1)
Recheck Cond: (post_id = ANY ('{320002,320006,320014,320019,320022,320025,320050,320088,320090,320096,320100,320101,320104,320108,320124,320143,320148,320198,320199,320235,320241,320269,320276,320282,320299,320306,320315,320402,320408,320435,320494,320533,320639,320742,320950,321065,321258,321318,321319,321476,321487,321834}'::integer[]))
-> Bitmap Index Scan on phpbb_posts_pkey (cost=0.00..178.91 rows=42 width=0) (actual time=0.164..0.164 rows=42 loops=1)
Index Cond: (post_id = ANY ('{320002,320006,320014,320019,320022,320025,320050,320088,320090,320096,320100,320101,320104,320108,320124,320143,320148,320198,320199,320235,320241,320269,320276,320282,320299,320306,320315,320402,320408,320435,320494,320533,320639,320742,320950,321065,321258,321318,321319,321476,321487,321834}'::integer[]))
-> Hash (cost=1.54..1.54 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)
-> Seq Scan on phpbb_zebra z (cost=0.00..1.54 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)
Filter: (user_id = 104595)
-> Index Scan using phpbb_users_pkey on phpbb_users u (cost=0.00..6.84 rows=1 width=444) (actual time=0.005..0.006 rows=1 loops=42)
Index Cond: (u.user_id = p.poster_id)
Total runtime: 0.991 ms
(13 lignes)
As you can read, the query is executed in memory on indexes.
RESULT:
SELECT u.*, z.friend, z.foe, p.* FROM phpbb_posts p
INNER JOIN phpbb_users u ON u.user_id = p.poster_id
LEFT JOIN phpbb_zebra z ON (z.user_id = 104595 AND z.zebra_id = p.poster_id)
WHERE p.post_id IN ('320002', '320006', '320014', '320019', '320022', '320025', '320050', '320088', '320090', '320096', '320100', '320101', '320104', '320108', '320124', '320143', '320148', '320198', '320199', '320235', '320241', '320269', '320276', '320282', '320299', '320306', '320315', '320402', '320408', '320435', '320494', '320533', '320639', '320742', '320950', '321065', '321258', '321318', '321319', '321476', '321487', '321834')
2008-01-04 11:44:24 CET LOG: durée : 6.882 ms
Execution time divided by five.