Re: Very slow query

[Nick Barr <nicky@chuckie.co.uk>]

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)