SQL optimisation

Discussion of general topics related to the new version and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Post Reply
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

Dear David,

I am looking at the statics table in PostgreSQL, to see when the database does sequential scans.
Do :

Code: Select all

select * from pg_tables
On my server, I can see that phpbb_banlist results in hundred thousands of disc access :

I found a query in my logs, that may be responsible:
explain analyse
SELECT ban_ip, ban_userid, ban_email, ban_exclude, ban_give_reason, ban_end
FROM phpbb_banlist
WHERE ban_email = '' AND (ban_userid = 100394 OR ban_ip <> '')
PostgreSQL will make an extensive sequential scan on disc:

Code: Select all

 Seq Scan on phpbb_banlist  (cost=0.00..1.51 rows=13 width=33) (actual time=0.013..0.026 rows=19 loops=1)
   Filter: (((ban_email)::text = ''::text) AND ((ban_userid = 100394) OR ((ban_ip)::text <> ''::text)))
 Total runtime: 0.063 ms
The EXPLAIN ANALYSE tree is an inverted tree.

The indexes are not used because Phpbb indexes are set on pair values.
Presently, we should have:

Code: Select all

CREATE INDEX phpbb_banlist_ban_email
  ON phpbb_banlist
  USING btree
  (ban_email, ban_exclude);
Replace with

Code: Select all

CREATE INDEX phpbb_banlist_ban_email
  ON phpbb_banlist
  USING btree
  (ban_email);

Code: Select all

CREATE INDEX phpbb_banlist_ban_exclude
  ON phpbb_banlist
  USING btree
  (ban_exclude);
AND

Code: Select all

CREATE INDEX phpbb_banlist_ban_ip
  ON phpbb_banlist
  USING btree
  (ban_ip, ban_exclude);
replaced with :

Code: Select all

CREATE INDEX phpbb_banlist_ban_ip
  ON phpbb_banlist
  USING btree
  (ban_ip);
AND :

Code: Select all

CREATE INDEX phpbb_banlist_ban_user
  ON phpbb_banlist
  USING btree
  (ban_userid, ban_exclude);
replaced with :

Code: Select all

CREATE INDEX phpbb_banlist_ban_user
  ON phpbb_banlist
  USING btree
  (ban_userid);
For strange reasons, when running the explain analyse query, PostgreSQL still indicates a sequential scan.
Any explaination? Without explaination, I will post on postgresql mailing list to learn why so many disc access is performed.
I suspect that my shared memory is not large enough compared to the database size.

User avatar
DavidMJ
Registered User
Posts: 932
Joined: Thu Jun 16, 2005 1:14 am
Location: Great Neck, NY

Re: SQL optimisation

Post by DavidMJ »

I know this query is a problem query with regarding to index use, I do not know why PostgreSQL is giving such a query plan...
Freedom from fear

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

I got the reply from PostgreSQL : for a 19 row table, a sequential access is faster.
But you should change indexes for larger table.

User avatar
DavidMJ
Registered User
Posts: 932
Joined: Thu Jun 16, 2005 1:14 am
Location: Great Neck, NY

Re: SQL optimisation

Post by DavidMJ »

Thanks for the info, I will investigate this. :)
Freedom from fear

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

This one is very long:

Code: Select all

2008-01-04 17:39:35 CET LOG:  instruction : SELECT t.*, p.root_level, p.message_time, p.message_subject, p.icon_id, p.to_address, p.message_attachment, p.bcc_address, u.username, u.username_clean, u.user_colour
			FROM phpbb_privmsgs_to t, phpbb_privmsgs p, phpbb_users u
			WHERE t.user_id = 102889
				AND p.author_id = u.user_id
				AND t.folder_id = -2
				AND t.msg_id = p.msg_id
				
			ORDER BY p.msg_id DESC
	 LIMIT 50 OFFSET 0
2008-01-04 17:39:35 CET LOG:  durée : 58.329 ms
Should be better to write:

Code: Select all

SELECT t.*, p.root_level, p.message_time, p.message_subject, 
p.icon_id, p.to_address, p.message_attachment, 
p.bcc_address, u.username, u.username_clean, u.user_colour
			FROM phpbb_privmsgs_to t
INNER JOIN phpbb_privmsgs p ON t.msg_id = p.msg_id
INNER JOIN phpbb_users u ON p.author_id = u.user_id
WHERE t.user_id = 102889 AND t.folder_id = -2
ORDER BY t.msg_id DESC
LIMIT 50 OFFSET 0
The parser will understand the query more easily.
Such a query would benefit from materialized views written in PL.

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

This is a nasty one:

Code: Select all

2008-01-04 17:51:51 CET LOG:  instruction : SELECT p.post_id
		FROM phpbb_posts p
		WHERE p.topic_id = 215
			AND p.post_approved = 1
			
			
		ORDER BY p.post_time DESC
	 LIMIT 48 OFFSET 0
2008-01-04 17:51:51 CET LOG:  durée : 131.323 ms
Let's look at the query plan:

Code: Select all

EXPLAIN ANALYSE
SELECT p.post_id
		FROM phpbb_posts p
		WHERE p.topic_id = 215
			AND p.post_approved = 1
			
			
		ORDER BY p.post_time DESC
	 LIMIT 48 OFFSET 0

Code: Select all

 Limit  (cost=0.00..129.89 rows=48 width=8) (actual time=0.082..0.548 rows=48 loops=1)
   ->  Index Scan Backward using phpbb_posts_tid_post_time on phpbb_posts p  (cost=0.00..2208.11 rows=816 width=8) (actual time=0.081..0.533 rows=48 loops=1)
         Index Cond: (topic_id = 215)
         Filter: (post_approved = 1)
 Total runtime: 0.596 ms

Code: Select all

CREATE INDEX phpbb_posts_tid_post_time
  ON phpbb_posts
  USING btree
  (topic_id, post_time);
should be replaced with:

Code: Select all

CREATE INDEX phpbb_posts_post_time
  ON phpbb_posts
  USING btree
  (post_time);
The query plans becomes:

Code: Select all

Limit  (cost=1428.94..1429.06 rows=48 width=8) (actual time=0.679..0.703 rows=48 loops=1)
   ->  Sort  (cost=1428.94..1430.98 rows=816 width=8) (actual time=0.678..0.684 rows=48 loops=1)
         Sort Key: post_time
         ->  Index Scan using phpbb_posts_topic_id on phpbb_posts p  (cost=0.00..1389.48 rows=816 width=8) (actual time=0.055..0.569 rows=148 loops=1)
               Index Cond: (topic_id = 215)
               Filter: (post_approved = 1)
The query planner is not very accurate calculating duration. The result should be very less than 131 ms.

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

Another easy one:

2008-01-04 19:17:30 CET LOG: instruction :

Code: Select all

SELECT m.*, u.user_colour, g.group_colour, g.group_type 
FROM phpbb_moderator_cache m 
LEFT JOIN phpbb_users u ON (m.user_id = u.user_id) 
LEFT JOIN phpbb_groups g ON (m.group_id = g.group_id) 
WHERE m.display_on_index = 1 AND m.forum_id = 23
Let's look at the query plan:

Code: Select all

phpbb3=# EXPLAIN ANALYSE
phpbb3-# 
phpbb3-# SELECT m.*, u.user_colour, g.group_colour, g.group_type 
phpbb3-# FROM phpbb_moderator_cache m LEFT JOIN phpbb_users u ON (m.user_id = u.user_id) 
phpbb3-# LEFT JOIN phpbb_groups g ON (m.group_id = g.group_id) 
phpbb3-# WHERE m.display_on_index = 1 AND m.forum_id = 23;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=1.37..3.05 rows=1 width=52) (actual time=0.068..0.072 rows=1 loops=1)
   Join Filter: (m.group_id = g.group_id)
   ->  Merge Right Join  (cost=1.37..1.85 rows=1 width=44) (actual time=0.060..0.060 rows=1 loops=1)
         Merge Cond: (u.user_id = m.user_id)
         ->  Index Scan using phpbb_users_pkey on phpbb_users u  (cost=0.00..1985.98 rows=4361 width=8) (actual time=0.019..0.019 rows=1 loops=1)
         ->  Sort  (cost=1.37..1.37 rows=1 width=40) (actual time=0.037..0.037 rows=1 loops=1)
               Sort Key: m.user_id
               ->  Seq Scan on phpbb_moderator_cache m  (cost=0.00..1.36 rows=1 width=40) (actual time=0.013..0.015 rows=1 loops=1)
                     Filter: ((display_on_index = 1) AND (forum_id = 23))
   ->  Seq Scan on phpbb_groups g  (cost=0.00..1.09 rows=9 width=12) (actual time=0.004..0.005 rows=9 loops=1)
 Total runtime: 0.213 ms
(11 lignes)
m.group_id is not indexed and m.user_id are not indexed.
Let's index them:

Code: Select all

CREATE INDEX phpbb_moderator_cache_group_id
  ON phpbb_moderator_cache
  USING btree
  (group_id);

CREATE INDEX phpbb_moderator_cache_user_id
  ON phpbb_moderator_cache
  USING btree
  (user_id);
Query plans becomes:

Code: Select all

phpbb3=# EXPLAIN ANALYSE
phpbb3-# 
phpbb3-# SELECT m.*, u.user_colour, g.group_colour, g.group_type 
phpbb3-# FROM phpbb_moderator_cache m 
phpbb3-# LEFT JOIN phpbb_users u ON (m.user_id = u.user_id) 
phpbb3-# LEFT JOIN phpbb_groups g ON (m.group_id = g.group_id) 
phpbb3-# WHERE m.display_on_index = 1 AND m.forum_id = 23;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=1.37..3.05 rows=1 width=52) (actual time=0.042..0.047 rows=1 loops=1)
   Join Filter: (m.group_id = g.group_id)
   ->  Merge Right Join  (cost=1.37..1.85 rows=1 width=44) (actual time=0.036..0.037 rows=1 loops=1)
         Merge Cond: (u.user_id = m.user_id)
         ->  Index Scan using phpbb_users_pkey on phpbb_users u  (cost=0.00..1985.98 rows=4361 width=8) (actual time=0.008..0.008 rows=1 loops=1)
         ->  Sort  (cost=1.37..1.37 rows=1 width=40) (actual time=0.024..0.025 rows=1 loops=1)
               Sort Key: m.user_id
               ->  Seq Scan on phpbb_moderator_cache m  (cost=0.00..1.36 rows=1 width=40) (actual time=0.008..0.011 rows=1 loops=1)
                     Filter: ((display_on_index = 1) AND (forum_id = 23))
   ->  Seq Scan on phpbb_groups g  (cost=0.00..1.09 rows=9 width=12) (actual time=0.002..0.004 rows=9 loops=1)
 Total runtime: 0.108 ms
(11 lignes)

The query plan does not show any advantage, because my moderator table is nearly empty.
But we need these two indexes.

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

That one is quite slow:

Code: Select all

2008-01-04 19:31:15 CET LOG:  instruction : SELECT g.group_id, g.group_name, g.group_colour, g.group_type
			FROM phpbb_groups g
			LEFT JOIN phpbb_user_group ug
				ON (
					g.group_id = ug.group_id
					AND ug.user_id = 1
					AND ug.user_pending = 0
				)
			WHERE g.group_legend = 1
				AND (g.group_type <> 2 OR ug.user_id = 1)
			ORDER BY g.group_name ASC
2008-01-04 19:31:15 CET LOG:  durée : 7.861 ms
Indexes are missing:

Code: Select all

CREATE INDEX phpbb_user_group_user_pending in a LEFT JOIN

  ON phpbb_user_group
  USING btree
  (user_pending);

CREATE INDEX phpbb_groups_group_type
  ON phpbb_groups
  USING btree
  (group_type);

CREATE INDEX phpbb_groups_group_name
  ON phpbb_groups
  USING btree
  (group_name);
There should be an increase.

Let's display the query plan:

Code: Select all

EXPLAIN ANALYSE

SELECT g.group_id, g.group_name, g.group_colour, g.group_type
			FROM phpbb_groups g
			LEFT JOIN phpbb_user_group ug
				ON (
					g.group_id = ug.group_id
					AND ug.user_id = 1
					AND ug.user_pending = 0
				)
			WHERE g.group_legend = 1
				AND (g.group_type <> 2 OR ug.user_id = 1)
			ORDER BY g.group_name ASC;

Code: Select all

                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=9.43..9.43 rows=2 width=28) (actual time=0.105..0.106 rows=2 loops=1)
   Sort Key: g.group_name
   ->  Hash Left Join  (cost=8.28..9.42 rows=2 width=28) (actual time=0.048..0.051 rows=2 loops=1)
         Hash Cond: (g.group_id = ug.group_id)
         Filter: ((g.group_type <> 2) OR (ug.user_id = 1))
         ->  Seq Scan on phpbb_groups g  (cost=0.00..1.11 rows=2 width=28) (actual time=0.009..0.011 rows=2 loops=1)
               Filter: (group_legend = 1)
         ->  Hash  (cost=8.27..8.27 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)
               ->  Index Scan using phpbb_user_group_user_id on phpbb_user_group ug  (cost=0.00..8.27 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)
                     Index Cond: (user_id = 1)
                     Filter: (user_pending = 0)
 Total runtime: 0.142 ms
(12 lignes)
Should be faster, thanks to user_pending index.

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

Let's look at this one :

Code: Select all

2008-01-04 19:31:15 CET LOG:  instruction : SELECT t.*, f.*, tw.notify_status, bm.topic_id as bookmarked, tt.mark_time, ft.mark_time as forum_mark_time 
FROM phpbb_forums f, phpbb_topics t 
LEFT JOIN phpbb_topics_watch tw ON (tw.user_id = 103701 AND t.topic_id = tw.topic_id) 
LEFT JOIN phpbb_bookmarks bm ON (bm.user_id = 103701 AND t.topic_id = bm.topic_id) 
LEFT JOIN phpbb_topics_track tt ON (tt.user_id = 103701 AND t.topic_id = tt.topic_id) 
LEFT JOIN phpbb_forums_track ft ON (ft.user_id = 103701 AND t.forum_id = ft.forum_id) 
WHERE t.topic_id = 22576 AND (f.forum_id = t.forum_id OR (t.topic_type = 3
			AND f.forum_id = 49))
2008-01-04 19:31:15 CET LOG:  durée : 3.065 ms
We need indexes on LEFT JOINS keys:

Query plan :

Code: Select all

Nested Loop Left Join  (cost=8.52..48.95 rows=1 width=594) (actual time=0.154..0.158 rows=1 loops=1)
   ->  Nested Loop Left Join  (cost=8.52..40.67 rows=1 width=590) (actual time=0.142..0.145 rows=1 loops=1)
         Join Filter: (t.topic_id = tt.topic_id)
         ->  Nested Loop Left Join  (cost=8.52..32.38 rows=1 width=586) (actual time=0.109..0.111 rows=1 loops=1)
               Join Filter: (t.topic_id = bm.topic_id)
               ->  Nested Loop Left Join  (cost=8.52..31.11 rows=1 width=582) (actual time=0.100..0.102 rows=1 loops=1)
                     ->  Nested Loop  (cost=8.52..22.49 rows=1 width=580) (actual time=0.070..0.072 rows=1 loops=1)
                           Join Filter: ((f.forum_id = t.forum_id) OR ((t.topic_type = 3) AND (f.forum_id = 49)))
                           ->  Index Scan using phpbb_topics_pkey on phpbb_topics t  (cost=0.00..8.28 rows=1 width=183) (actual time=0.029..0.030 rows=1 loops=1)
                                 Index Cond: (topic_id = 22576)
                           ->  Bitmap Heap Scan on phpbb_forums f  (cost=8.52..14.18 rows=2 width=397) (actual time=0.033..0.034 rows=1 loops=1)
                                 Recheck Cond: ((f.forum_id = t.forum_id) OR (f.forum_id = 49))
                                 ->  BitmapOr  (cost=8.52..8.52 rows=2 width=0) (actual time=0.024..0.024 rows=0 loops=1)
                                       ->  Bitmap Index Scan on phpbb_forums_pkey  (cost=0.00..4.26 rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=1)
                                             Index Cond: (f.forum_id = t.forum_id)
                                       ->  Bitmap Index Scan on phpbb_forums_pkey  (cost=0.00..4.26 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
                                             Index Cond: (forum_id = 49)
                     ->  Index Scan using phpbb_topics_watch_topic_id on phpbb_topics_watch tw  (cost=0.00..8.61 rows=1 width=6) (actual time=0.029..0.029 rows=0 loops=1)
                           Index Cond: (topic_id = 22576)
                           Filter: (user_id = 103701)
               ->  Seq Scan on phpbb_bookmarks bm  (cost=0.00..1.25 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                     Filter: ((user_id = 103701) AND (topic_id = 22576))
         ->  Index Scan using phpbb_topics_track_pkey on phpbb_topics_track tt  (cost=0.00..8.27 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=1)
               Index Cond: ((user_id = 103701) AND (topic_id = 22576))
   ->  Index Scan using phpbb_forums_track_pkey on phpbb_forums_track ft  (cost=0.00..8.27 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1)
         Index Cond: ((ft.user_id = 103701) AND (t.forum_id = ft.forum_id))
 Total runtime: 0.431 ms
(27 lignes)
Indexes on phpbb_forums_track are not enough to create fast LEFT JOINS.
We need :

Code: Select all

CREATE INDEX pphpbb_forums_track_forum_id
  ON phpbb_forums_track
  USING btree
  (forum_id);

CREATE INDEX pphpbb_forums_track_user_id
  ON phpbb_forums_track
  USING btree
  (user_id);

CREATE INDEX phpbb_topics_track_user_id
  ON phpbb_topics_track
  USING btree
  (user_id);
Query plan becomes:

Code: Select all

                                                                                   QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=8.52..48.95 rows=1 width=594) (actual time=0.067..0.074 rows=1 loops=1)
   ->  Nested Loop Left Join  (cost=8.52..40.67 rows=1 width=590) (actual time=0.062..0.069 rows=1 loops=1)
         Join Filter: (t.topic_id = tt.topic_id)
         ->  Nested Loop Left Join  (cost=8.52..32.38 rows=1 width=586) (actual time=0.051..0.057 rows=1 loops=1)
               Join Filter: (t.topic_id = bm.topic_id)
               ->  Nested Loop  (cost=8.52..31.11 rows=1 width=582) (actual time=0.045..0.051 rows=1 loops=1)
                     Join Filter: ((f.forum_id = t.forum_id) OR ((t.topic_type = 3) AND (f.forum_id = 49)))
                     ->  Nested Loop Left Join  (cost=0.00..16.90 rows=1 width=185) (actual time=0.031..0.036 rows=1 loops=1)
                           ->  Index Scan using phpbb_topics_pkey on phpbb_topics t  (cost=0.00..8.28 rows=1 width=183) (actual time=0.016..0.021 rows=1 loops=1)
                                 Index Cond: (topic_id = 22576)
                           ->  Index Scan using phpbb_topics_watch_topic_id on phpbb_topics_watch tw  (cost=0.00..8.61 rows=1 width=6) (actual time=0.012..0.012 rows=0 loops=1)
                                 Index Cond: (topic_id = 22576)
                                 Filter: (user_id = 103701)
                     ->  Bitmap Heap Scan on phpbb_forums f  (cost=8.52..14.18 rows=2 width=397) (actual time=0.011..0.012 rows=1 loops=1)
                           Recheck Cond: ((f.forum_id = t.forum_id) OR (f.forum_id = 49))
                           ->  BitmapOr  (cost=8.52..8.52 rows=2 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                                 ->  Bitmap Index Scan on phpbb_forums_pkey  (cost=0.00..4.26 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)
                                       Index Cond: (f.forum_id = t.forum_id)
                                 ->  Bitmap Index Scan on phpbb_forums_pkey  (cost=0.00..4.26 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
                                       Index Cond: (forum_id = 49)
               ->  Seq Scan on phpbb_bookmarks bm  (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((user_id = 103701) AND (topic_id = 22576))
         ->  Index Scan using phpbb_topics_track_pkey on phpbb_topics_track tt  (cost=0.00..8.27 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)
               Index Cond: ((user_id = 103701) AND (topic_id = 22576))
   ->  Index Scan using phpbb_forums_track_pkey on phpbb_forums_track ft  (cost=0.00..8.27 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ((ft.user_id = 103701) AND (t.forum_id = ft.forum_id))
 Total runtime: 0.271 ms
(27 lignes)
Time divided by 2.5

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

This is too slow:

Code: Select all

2008-01-04 21:53:58 CET LOG:  instruction : SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_ip, s.session_viewonline
				FROM phpbb_users u, phpbb_sessions s
				WHERE s.session_time >= 1199479738
					AND u.user_id = s.session_user_id
				ORDER BY u.username_clean ASC, s.session_ip ASC
2008-01-04 21:53:58 CET LOG:  durée : 8.137 ms
Quey plan :

Code: Select all

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=712.17..713.04 rows=347 width=52) (actual time=16.027..16.133 rows=551 loops=1)
   Sort Key: u.username_clean, s.session_ip
   ->  Hash Join  (cost=548.12..697.53 rows=347 width=52) (actual time=8.597..10.044 rows=551 loops=1)
         Hash Cond: (s.session_user_id = u.user_id)
         ->  Seq Scan on phpbb_sessions s  (cost=0.00..143.34 rows=347 width=22) (actual time=0.008..1.006 rows=551 loops=1)
               Filter: (session_time >= 1199479738)
         ->  Hash  (cost=493.61..493.61 rows=4361 width=34) (actual time=8.578..8.578 rows=4363 loops=1)
               ->  Seq Scan on phpbb_users u  (cost=0.00..493.61 rows=4361 width=34) (actual time=0.007..6.355 rows=4363 loops=1)
 Total runtime: 16.303 ms
(9 lignes)
In fact, this query is a LEFT JOIN.
A left join is always fast than an inner join.

It should be written:

Code: Select all

SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_ip, s.session_viewonline
				FROM phpbb_sessions s
LEFT JOIN phpbb_users u ON u.user_id = s.session_user_id
				WHERE s.session_time >= 1199479738
				ORDER BY u.username_clean ASC, s.session_ip ASC
Query plan :

Code: Select all

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=712.17..713.04 rows=347 width=52) (actual time=14.096..14.202 rows=558 loops=1)
   Sort Key: u.username_clean, s.session_ip
   ->  Hash Left Join  (cost=548.12..697.53 rows=347 width=52) (actual time=7.102..8.086 rows=558 loops=1)
         Hash Cond: (s.session_user_id = u.user_id)
         ->  Seq Scan on phpbb_sessions s  (cost=0.00..143.34 rows=347 width=22) (actual time=0.006..0.544 rows=558 loops=1)
               Filter: (session_time >= 1199479738)
         ->  Hash  (cost=493.61..493.61 rows=4361 width=34) (actual time=7.084..7.084 rows=4363 loops=1)
               ->  Seq Scan on phpbb_users u  (cost=0.00..493.61 rows=4361 width=34) (actual time=0.005..4.863 rows=4363 loops=1)
 Total runtime: 14.336 ms
(9 lignes)
Gain: 20% time at least ...

Post Reply