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 on Postgres 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 to do that from within a coldfusion script). Many thanks for all your help guys! Julie -----Original Message----- From: Pavel Stehule [mailto:pavel.steh...@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 <jdavenp...@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 mind for future use elsewhere. I > agree, there are ways to rewrite this query, just wondering which is best to > take advantage of 8.4. > Thanks much. > > ok, sorry, do column_course_begin::date = ... :) Pavel > > > -----Original Message----- > From: Tomas Vondra [mailto:t...@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 <t...@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 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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
