Re: query taking much longer since Postgres 8.4 upgrade

Dne 16.3.2011 22:31, Pavel Stehule napsal(a):
> 2011/3/16 Tomas Vondra <tv(at)fuzzy(dot)cz>:
>> 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

Which is not going to work if the course_begin_date column is a
timestamp, because of the time part.

But yes, there are several ways to improve this query, yet it does not
explain why the 8.4 is so much slower.

Tomas