Re: Very slow query - Mailing list 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