SQL optimisation

[DavidMJ]

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...

[DavidMJ]

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...

[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

[jmpoure]

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]

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]

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.

[DavidMJ]

We use pg_escape_string. I am in the middle of rewritting the LIKE functionality.

[jmpoure]

Fine, thanks. Do you see any quick fix to remove these warnings?

[DavidMJ]

I am afraid I do not see a quick fix.

[jmpoure]

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