Re: Very slow query
От | Nick Barr |
---|---|
Тема | Re: Very slow query |
Дата | |
Msg-id | 40A0A1EA.8030500@chuckie.co.uk обсуждение исходный текст |
Ответ на | Very slow query (Rory Campbell-Lange <rory@campbell-lange.net>) |
Ответы |
Re: Very slow query
(Rory Campbell-Lange <rory@campbell-lange.net>)
|
Список | pgsql-general |
Rory Campbell-Lange wrote: >Wow, this is an education! My php profiler now shows: > >0.02 db->db_board_listing C: /var/www/trial/php/db.php:175 M: 867024 >0.02 ob_start C: /var/www/trial/php/db.php:238 M: 867024 >0.02 pg_exec C: /var/www/trial/php/db.php:239 M: 908328 >1.24 pg_result_status C: /var/www/trial/php/db.php:240 M: 908392 >1.24 ob_end_clean C: /var/www/trial/php/db.php:241 M: 908416 >1.24 db->result_checker C: /var/www/trial/php/db.php:243 M: 867472 >1.24 is_resource C: /var/www/trial/php/db.php:1317 M: 867472 >1.24 pg_numrows C: /var/www/trial/php/db.php:248 M: 867568 >1.24 pg_fetch_array C: /var/www/trial/php/db.php:250 M: 867600 >1.24 pg_fetch_array C: /var/www/trial/php/db.php:250 M: 867760 > >and now shows: > >C: /var/www/trial/php/core.php:151 M: 867040 > 0.02 db->db_board_listing C: /var/www/trial/php/db.php:175 M: 867040 > 0.02 ob_start C: /var/www/trial/php/db.php:238 M: 867040 > 0.02 pg_exec C: /var/www/trial/php/db.php:239 M: 908344 > 0.14 pg_result_status C: /var/www/trial/php/db.php:240 M: 908408 > 0.14 ob_end_clean C: /var/www/trial/php/db.php:241 M: 908432 > 0.14 db->result_checker C: /var/www/trial/php/db.php:243 M: 867488 > 0.14 is_resource C: /var/www/trial/php/db.php:1317 M: 867488 > 0.14 pg_numrows C: /var/www/trial/php/db.php:248 M: 867584 > 0.14 pg_fetch_array C: /var/www/trial/php/db.php:250 M: 867616 > 0.14 pg_fetch_array C: /var/www/trial/php/db.php:250 M: 867776 > >This is a saving of 1.1 seconds! > > > Thats a bit better ;-) >>On 10/05/2004 22:30 Rory Campbell-Lange wrote: >> >> >>>Sorry for replying to my own post, but I'm anxious for an answer. Should >>>I provide other information? >>> >>> >>Look carefully at your column types. I can see several smallint columns in >>there WHERE clause which are not expicitely typed as such. >> >> > >I'm not sure how to do this, Paul. do I do b.n_id::smallint ? Is >smallint not implied? > >Thanks >Rory > > > Not quite. Explicit casts are needed when you have any numbers in the WHERE condition and the columns are not of type integer/int4. For example I have tweaked your query. WHERE b.b_hidden = 'f' AND ( b.n_type = 3::smallint OR b.n_creator = 71 OR ( b.n_id = o.n_board_id AND o.n_creator = 71 AND o.n_joined > 0::smallint ) ) Note that b.n_creator and o.n_creator do not need explicit casts because they are both of type integer anyway. You could of course put them in for clarity. PG only casts the numbers to integer's, and not to smallint or bigint, which basically means it does not use any indexes on that column. This is fixed in 7.4 I believe, which you seem to be running anyway so you might not be affected. HTH Nick
В списке pgsql-general по дате отправления: