Date: Tue, 13 Jun 2006 16:13:47 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Shaun Thomas <sthomas@leapfrogonline.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Confirmation of bad query plan generated by 7.4
Message-ID: <20060613211347.GB34196@pervasive.com>
References: <9119.1150222168@sss.pgh.pa.us>
	<448EDFC4.8F27.00A9.0@leapfrogonline.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <448EDFC4.8F27.00A9.0@leapfrogonline.com>
User-Agent: Mutt/1.5.11

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