Re: Confirmation of bad query plan generated by 7.4

>>> 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.

Thanks!

--

Shaun Thomas
Database Administrator

Leapfrog Online
807 Greenwood Street
Evanston, IL 60201
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com

Confidentiality Note:

The document(s) accompanying this e-mail transmission, if any, and the
e-mail transmittal message contain information from Leapfrog Online
Customer Acquisition, LLC is confidential or privileged. The information
is intended to be for the use of the individual(s) or entity(ies) named
on this e-mail transmission message. If you are not the intended
recipient, be aware that any disclosure, copying, distribution or use of
the contents of this e-mail is prohibited. If you have received this
e-mail in error, please immediately delete this e-mail and notify us by
telephone of the error