On Tue, Jun 13, 2006 at 03:54:44PM -0500, Shaun Thomas wrote: > >>> On 6/13/2006 at 1:09 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > > I missed the part where you explain why you think this plan is > terrible? > > 412ms for what seems a rather expensive query doesn't sound so > awful. > > Sorry, I based that statement on the estimated/actual disparity. That > particular query plan is not terrible in its results, but look at the > estimates and how viciously the explain analyze corrects the values. > > Here's an example: > > -> Index Scan using idx_evt_dt on l_event_log e > (cost=0.00..2641742.75 rows=15752255 width=12) > (actual time=0.034..229.641 rows=38923 loops=1) > > rows=15752255 ? That's over half the 27M row table. As expected, the > *actual* match is much, much lower at 38923. As it turns out, Marcin > was right. Simply changing: > > now() - interval '2 days' > > to > > '2006-06-11 15:30:00' > > generated a much more accurate set of estimates. I have to assume > that > 7.4 is incapable of that optimization step. Now that I know this, I > plan on modifying my stored proc to calculate the value before > inserting > it into the query. Is there some compelling reason to stick with 7.4? In my experience you'll see around double (+100%) the performance going to 8.1... Also, I'm not sure that the behavior is entirely changed, either. On a 8.1.4 database I'm still seeing a difference between now() - interval and a hard-coded date. What's your stats target set to for that table? > -- > Shaun Thomas > Database Administrator > > Leapfrog Online > 807 Greenwood Street > Evanston, IL 60201 Heh, I grew up 3 miles from there. In fact, IIRC my old dentist is/was at 807 Davis. > Tel. 847-440-8253 > Fax. 847-570-5750 > www.leapfrogonline.com -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
