Re: Confirmation of bad query plan generated by 7.4

On Tue, Jun 13, 2006 at 03:54:44PM -0500, Shaun Thomas wrote:
> >>> On 6/13/2006 at 1:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461