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...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)
SQL optimisation
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!
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!
Re: SQL optimisation
Freedom from fear
Re: SQL optimisation
We really do need all the data returned by that query...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
Freedom from fear
Re: SQL optimisation
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
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.
Re: SQL optimisation
My cut and paste is not very explicit.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...
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)
Sequencial scans need access to the data on disc.-> 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)
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.
Re: SQL optimisation
Another issue is that PostgreSQL complains 10 times a minute that backslash are not escaped well.
The query at stake is:
From here : http://www.postgresql.org/docs/8.3/stat ... xical.html
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'\\'.
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.
Re: SQL optimisation
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.
Seems like we should be using a special class in case of PostgreSQL when generating templates.
Re: SQL optimisation
We use pg_escape_string. I am in the middle of rewritting the LIKE functionality.
Freedom from fear
Re: SQL optimisation
Fine, thanks. Do you see any quick fix to remove these warnings?
Re: SQL optimisation
Do you plan to integrate PostgreSQL tseach2 search : viewtopic.php?f=4&t=28707&st=0&sk=t&sd=a