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
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: Searches are done on indexes:
phpbb3=# 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 = 1 AND z.zebra_id = p.poster_id) WHERE p.post_id IN ('432652', '433287', '433346', '433380')
phpbb3-# AND u.user_id = p.poster_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=18.63..67.71 rows=4 width=1095) (actual time=0.172..0.225 rows=4 loops=1)
Hash Cond: (p.poster_id = z.zebra_id)
-> Nested Loop (cost=17.08..66.14 rows=4 width=1091) (actual time=0.097..0.148 rows=4 loops=1)
-> Bitmap Heap Scan on phpbb_posts p (cost=17.08..33.01 rows=4 width=651) (actual time=0.061..0.079 rows=4 loops=1)
Recheck Cond: (post_id = ANY ('{432652,433287,433346,433380}'::integer[]))
-> Bitmap Index Scan on phpbb_posts_pkey (cost=0.00..17.08 rows=4 width=0) (actual time=0.050..0.050 rows=4 loops=1)
Index Cond: (post_id = ANY ('{432652,433287,433346,433380}'::integer[]))
-> Index Scan using phpbb_users_pkey on phpbb_users u (cost=0.00..8.27 rows=1 width=440) (actual time=0.012..0.013 rows=1 loops=4)
Index Cond: (u.user_id = p.poster_id)
-> Hash (cost=1.54..1.54 rows=1 width=8) (actual time=0.012..0.012 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 = 1)
Total runtime: 0.447 ms
(13 lignes)
.
I am sorry but I do not see how the zebra table is taking advantage of the indexes you created because it is doing hash joins...
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:Here is a collection of SQL queries which run with *
008-01-04 14:44:43 CET LOG: instruction : SELECT u.*, s.*
FROM phpbb_sessions s, phpbb_users u
WHERE s.session_id = 'XXXXXXXXXXXXXXXXXXXXXXXX'
AND u.user_id = s.session_user_id
2008-01-04 14:44:43 CET LOG: durée : 2.099 ms
We really do need all the data returned by that query...
Freedom from fear
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

I turned debugging on and viewed a lot of queries.
Most of them run in less than 3 millisecond.

Seems okay for me.

Kind regards,
Jean-Michel
Last edited by jmpoure on Fri Jan 04, 2008 8:56 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 »

DavidMJ wrote:I am sorry but I do not see how the zebra table is taking advantage of the indexes you created because it is doing hash joins...
My cut and paste is not very explicit.
Indenting was not preserved.

Here is a normal paste of the query plan:

Code: Select all

 Hash Left Join  (cost=18.63..67.71 rows=4 width=1095) (actual time=0.172..0.225 rows=4 loops=1)
   Hash Cond: (p.poster_id = z.zebra_id)
   ->  Nested Loop  (cost=17.08..66.14 rows=4 width=1091) (actual time=0.097..0.148 rows=4 loops=1)
         ->  Bitmap Heap Scan on phpbb_posts p  (cost=17.08..33.01 rows=4 width=651) (actual time=0.061..0.079 rows=4 loops=1)
               Recheck Cond: (post_id = ANY ('{432652,433287,433346,433380}'::integer[]))
               ->  Bitmap Index Scan on phpbb_posts_pkey  (cost=0.00..17.08 rows=4 width=0) (actual time=0.050..0.050 rows=4 loops=1)
                     Index Cond: (post_id = ANY ('{432652,433287,433346,433380}'::integer[]))
         ->  Index Scan using phpbb_users_pkey on phpbb_users u  (cost=0.00..8.27 rows=1 width=440) (actual time=0.012..0.013 rows=1 loops=4)
               Index Cond: (u.user_id = p.poster_id)
   ->  Hash  (cost=1.54..1.54 rows=1 width=8) (actual time=0.012..0.012 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 = 1)
 Total runtime: 0.447 ms
(13 lignes)
The zebra query starts with a sequential scan.
-> 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)
Sequencial scans need access to the data on disc.
Whenever any sequencial happens, this is bad.

When adding an index, sequencial scan is replaced with an index scan.
Index scans are performed in memory, without access to disc.
No access to disc, all preliminary work is done in memory.

The big advantage of using PostgreSQL for development is that you have access to the query planner and optimiser.
PostgreSQL query planner is able to EXPLAIN if queries are executed from memory or from disc.
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

Another issue is that PostgreSQL complains 10 times a minute that backslash are not escaped well.

The query at stake is:

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_users u, phpbb_sessions s
				WHERE s.session_time >= 1199449464 AND s.session_page LIKE '%\\_f\\_=3x%'
					AND u.user_id = s.session_user_id
				ORDER BY u.username_clean ASC, s.session_ip ASC

Code: Select all

2008-01-04 22:01:15 CET ATTENTION:  utilisation non standard de \\ dans une chaîne littérale au caractère 239
2008-01-04 22:01:15 CET ASTUCE :  Utilisez la syntaxe de chaîne d'échappement pour les antislashs, c'est-à-dire E'\\'.
2008-01-04 22:01:15 CET ATTENTION:  utilisation non standard de \\ dans une chaîne littérale au caractère 239
2008-01-04 22:01:15 CET ASTUCE :  Utilisez la syntaxe de chaîne d'échappement pour les antislashs, c'est-à-dire E'\\'.
2008-01-04 22:01:15 CET ATTENTION:  utilisation non standard de \\ dans une chaîne littérale au caractère 239
2008-01-04 22:01:15 CET ASTUCE :  Utilisez la syntaxe de chaîne d'échappement pour les antislashs, c'est-à-dire E'\\'.
2008-01-04 22:01:15 CET ATTENTION:  utilisation non standard de \\ dans une chaîne littérale au caractère 239
2008-01-04 22:01:15 CET ASTUCE :  Utilisez la syntaxe de chaîne d'échappement pour les antislashs, c'est-à-dire E'\\'.
2008-01-04 22:01:15 CET ATTENTION:  utilisation non standard de \\ dans une chaîne littérale au caractère 239
2008-01-04 22:01:15 CET ASTUCE :  Utilisez la syntaxe de chaîne d'échappement pour les antislashs, c'est-à-dire E'\\'.
2008-01-04 22:01:17 CET ATTENTION:  utilisation non standard de \\ dans une chaîne littérale au caractère 239
2008-01-04 22:01:17 CET ASTUCE :  Utilisez la syntaxe de chaîne d'échappement pour les antislashs, c'est-à-dire E'\\'.
2008-01-04 22:01:18 CET ATTENTION:  utilisation non standard de \\ dans une chaîne littérale au caractère 239
2008-01-04 22:01:18 CET ASTUCE :  Utilisez la syntaxe de chaîne d'échappement pour les antislashs, c'est-à-dire E'\\'.
From here : http://www.postgresql.org/docs/8.3/stat ... xical.html

Code: Select all

If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. This is for backward compatibility with the historical behavior, in which backslash escapes were always recognized. Although standard_conforming_strings currently defaults to off, the default will change to on in a future release for improved standards compliance. Applications are therefore encouraged to migrate away from using backslash escapes. If you need to use a backslash escape to represent a special character, write the constant with an E to be sure it will be handled the same way in future releases.
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

Are you using pg_ escape_ string instead of addslashes ?
Seems like we should be using a special class in case of PostgreSQL when generating templates.
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 »

We use pg_escape_string. I am in the middle of rewritting the LIKE functionality.
Freedom from fear
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

Fine, thanks. Do you see any quick fix to remove these warnings?
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 am afraid I do not see a quick fix.
Freedom from fear
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: SQL optimisation

Post by jmpoure »

Do you plan to integrate PostgreSQL tseach2 search : viewtopic.php?f=4&t=28707&st=0&sk=t&sd=a
Post Reply