From: Jim C. Nasby <jnasby@pervasive.com>
To: Shaun Thomas <sthomas@leapfrogonline.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Confirmation of bad query plan generated by 7.4
Date: Tue, 13 Jun 2006 16:13:47 -0500
Message-ID: <20060613211347.GB34196@pervasive.com> (raw)
In-Reply-To: <448EDFC4.8F27.00A9.0@leapfrogonline.com>
References: <9119.1150222168@sss.pgh.pa.us>
<448EDFC4.8F27.00A9.0@leapfrogonline.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, sthomas@leapfrogonline.com, tgl@sss.pgh.pa.us
Subject: Re: Confirmation of bad query plan generated by 7.4
In-Reply-To: <20060613211347.GB34196@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox