Re: query taking much longer since Postgres 8.4 upgrade - Mailing list pgsql-general

FYI, I implemented Pavel's suggestion to use:

 course_begin_date::date IN (
'20100412','20100510','20100607','20100712','20100830','20100927','20101025','20101122','20101213','20110110','20110207','20110307'
)

instead of 

to_char(course_begin_date,'YYYYMMDD') IN (
'20100412','20100510','20100607','20100712','20100830','20100927','20101025','20101122','20101213','20110110','20110207','20110307'
)

and it did help significantly.  The overall script (where there are several queries like this one) was taking 7.5 mins
onPostgres 8.0 and initially took 20 mins on 8.4; but now after this change (::date) it only takes 14.9 mins.
Progress! I have not yet had time to try Tomas' suggestion of bumping up the work_mem first (trying to figure out how
todo that from within a coldfusion script).  Many thanks for all your help guys!
 

Julie


-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com] 
Sent: Thursday, March 17, 2011 12:13 AM
To: Davenport, Julie
Cc: Tomas Vondra; pgsql-general@postgresql.org
Subject: Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

2011/3/16 Davenport, Julie <JDavenport@ctcd.edu>:
> Yes, the column course_begin_date is a timestamp, so that would not work in this instance, but I will keep that in
mindfor future use elsewhere.  I agree, there are ways to rewrite this query, just wondering which is best to take
advantageof 8.4.
 
> Thanks much.
>
>

ok, sorry, do column_course_begin::date = ...

:)

Pavel

>
>
> -----Original Message-----
> From: Tomas Vondra [mailto:tv@fuzzy.cz]
> Sent: Wednesday, March 16, 2011 4:40 PM
> To: Pavel Stehule
> Cc: pgsql-general@postgresql.org; Davenport, Julie
> Subject: Re: [GENERAL] 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@fuzzy.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
because8.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
>