Search Postgresql Archives

Re: query taking much longer since Postgres 8.4 upgrade

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2011/3/16 Tomas Vondra <tv@xxxxxxxx>:
> Dne 16.3.2011 21:38, Davenport, Julie napsal(a):
>> OK, I did the explain analyze on both sides (using a file for output instead) and used the tool you suggested.
>>
>> 8.0 - http://explain.depesz.com/s/Wam
>> 8.4 - http://explain.depesz.com/s/asJ
>
> Great, that's exactly what I asked for. I'll repost that to the mailing
> list so that the others can check it too.
>
>> When I run the queries I get 59,881 rows on the 8.0 side and 59,880 on the 8.4 side, which is what I expect because 8.4 side was updated a couple hours later and some minor changes make sense.
>
> Hm, obviously both versions got the row estimates wrong, but the 8.4
> difference (200x) is much bigger that the 8.0 (10x). This might be one
> of the reasons why a different plan is chosen.

the expression

to_char(course_begin_date, 'YYYYMMDD'::text) = '20101025'::text

should be a problem

much better is test on equality in date domain like:

course_begin_date = to_date('20101025', 'YYYYMMDD')

this is faster and probably better estimated

Regards

Pavel Stehule

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux