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 »

Code: Select all

CREATE INDEX phpbb_forums_left_right_id
  ON phpbb_forums
  USING btree
  (left_id, right_id);

Should be:

Code: Select all

CREATE INDEX phpbb_forums_right_id
  ON phpbb_forums
  USING btree
  (right_id);

AND

Code: Select all

CREATE INDEX phpbb_forums_right_id
  ON phpbb_forums
  USING btree
  (right_id);

This kind of query is too slow:

Code: Select all

2008-01-04 21:53:58 CET LOG:  instruction : SELECT f.* FROM phpbb_forums f ORDER BY f.left_id
2008-01-04 21:53:58 CET LOG:  durée : 2.125 ms

Query plan:

Code: Select all

                                                  QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
Sort  (cost=23.81..24.32 rows=203 width=397) (actual time=0.141..0.147 rows=29 loops=1)
   Sort Key: left_id
   ->  Seq Scan on phpbb_forums f  (cost=0.00..16.03 rows=203 width=397) (actual time=0.010..0.111 rows=29 loops=1)
Total runtime: 0.187 ms
(4 lignes)

After applying indexes:

Code: Select all

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
Sort  (cost=14.99..15.07 rows=29 width=397) (actual time=0.073..0.077 rows=29 loops=1)
   Sort Key: left_id
   ->  Seq Scan on phpbb_forums f  (cost=0.00..14.29 rows=29 width=397) (actual time=0.004..0.045 rows=29 loops=1)
Total runtime: 0.117 ms
(4 lignes)

Twice as fast.
Last edited by jmpoure on Mon Jan 07, 2008 10:21 pm, edited 1 time in total.

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

Re: SQL optimisation

Post by jmpoure »

Olay, this is enough for me.
Talk to you next time.

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 will look at this locally, I should have patches in CVS soon
Freedom from fear

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 »

Thank you very much for your efforts :)
Freedom from fear

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

Re: SQL optimisation

Post by jmpoure »

Code: Select all

2008-01-07 23:04:58 CET LOG:  instruction : UPDATE phpbb_config
                        SET config_value = 'eXXXXXXXXXXXXXae6'
                        WHERE config_name = 'rand_seed'
2008-01-07 23:04:58 CET LOG:  durée : 26.001 ms
There should be an index on config_name :

Code: Select all

CREATE INDEX phpbb_config_config_name
  ON phpbb_config
  USING btree
  (config_name);
I cannot test the difference in speed, but I think it's obvious.
The result should be around 1 millisecond.
Last edited by jmpoure on Mon Jan 07, 2008 10:25 pm, edited 1 time in total.

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 »

Wow, I cannot believe we have missed this... Thanks for that!
Freedom from fear

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 »

jmpoure wrote:

Code: Select all

CREATE INDEX phpbb_forums_left_id
  ON phpbb_forums
  USING btree
  (right_id);
That looks like a bug, no?
Freedom from fear

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

Re: SQL optimisation

Post by jmpoure »

I don't know if we discussed about this one:

Code: Select all

2008-01-07 23:06:50 CET LOG:  instruction : SELECT u.*, s.*
                                        FROM phpbb_sessions s, phpbb_users u
                                        WHERE s.session_id = 'cXXXXXXXXXXXXXXXX9'
                                                AND u.user_id = s.session_user_id
2008-01-07 23:06:50 CET LOG:  durée : 37.006 ms
Query plan:

Code: Select all

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..16.55 rows=1 width=610) (actual time=0.092..0.094 rows=1 loops=1)
   ->  Index Scan using phpbb_sessions_pkey on phpbb_sessions s  (cost=0.00..8.27 rows=1 width=206) (actual time=0.063..0.063 rows=1 loops=1)
         Index Cond: (session_id = 'XXXXXXXXXXXXXXXXx'::bpchar)
   ->  Index Scan using phpbb_users_pkey on phpbb_users u  (cost=0.00..8.27 rows=1 width=404) (actual time=0.020..0.020 rows=1 loops=1)
         Index Cond: (u.user_id = s.session_user_id)
 Total runtime: 0.251 ms
(6 lignes)
The query plan does a nested loop.

Now, let's rewrite the query:

Code: Select all

SELECT u.*, s.*
                                        FROM phpbb_sessions s
LEFT JOIN phpbb_users u ON u.user_id = s.session_user_id
                                        WHERE s.session_id = 'XXXXXXXXXXXXXXX'
Query plan:

Code: Select all

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..16.55 rows=1 width=610) (actual time=0.031..0.033 rows=1 loops=1)
   ->  Index Scan using phpbb_sessions_pkey on phpbb_sessions s  (cost=0.00..8.27 rows=1 width=206) (actual time=0.021..0.022 rows=1 loops=1)
         Index Cond: (session_id = 'cXXXXXXXXXXXXXXx9'::bpchar)
   ->  Index Scan using phpbb_users_pkey on phpbb_users u  (cost=0.00..8.27 rows=1 width=404) (actual time=0.005..0.006 rows=1 loops=1)
         Index Cond: (u.user_id = s.session_user_id)
 Total runtime: 0.120 ms
(6 lignes)
Three times as fast.
Left joins are always faster than inner joins.

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

Re: SQL optimisation

Post by jmpoure »

DavidMJ wrote:Wow, I cannot believe we have missed this... Thanks for that!
The best solution is when all queries run between 0 and 5 millisecond.

I believe you should develop on PostgreSQL and port to other databases.
pgAdmin3 gives you access to all statistics in a nice GUI.

Some fixes are just minor, because PostgreSQL will cache small tables in memory and run sequence scans from memory.

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

Re: SQL optimisation

Post by jmpoure »

DavidMJ wrote:
jmpoure wrote:

Code: Select all

CREATE INDEX phpbb_forums_left_id
  ON phpbb_forums
  USING btree
  (right_id);
That looks like a bug, no?
Yes, fixed in my previous message.

Post Reply