Re: Slow query after upgrade to 8.4

On Wed, Sep 23, 2009 at 10:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> One thing that is hobbling the performane on 8.4 is that you have
> work_mem set to only 1MB
>
> Other things you might try include increasing join_collapse_limit
> to 12 or so, and reducing random_page_cost.
>
> Another thing to look into is whether you can't get it to make a
> better estimate for this:
>
>                     ->  Index Scan using index_tbldata_variableid on tbldata dv118488y0  (cost=0.00..5914.49 rows=8 width=22) (actual time=1.555..209.856 rows=16193 loops=1)
>                           Index Cond: (variableid = 118488)
>                           Filter: (castbooltoint((((value)::text ~ '^-?[0-9]*([0-9]+.|.[0-9]+)?[0-9]*([Ee][-+]d*)?$'::text) AND ((value)::text <> '-'::text))) = 1)
>
> Being off by a factor of 2000 on a first-level rowcount estimate is
> almost inevitably a ticket to a bad join plan.  I doubt that the
> condition on variableid is being that badly estimated; the problem is
> the filter condition.  Whatever possessed you to take a perfectly good
> boolean condition and wrap it in "castbooltoint(condition) = 1"?
> I'm not sure how good the estimate would be anyway for the LIKE
> condition, but that bit of obscurantism isn't helping.
>
>                        regards, tom lane
>

After following all of Tom's suggestions, the query is now executing
in about one minute instead of seventeen minutes. Thanks, Tom.

In case you were curious, after removing the confusing call to
castbooltoint() the row estimate increased from the vastly incorrect 8
rows to the moderately incorrect 1000 rows (compared to the actual
16193 rows)

Should we try to improve statistics collection for that column
(variableid) by using ALTER TABLE ... ALTER COLUMN ... SET STATISTICS?
In other words, if the row estimate were perfect would we be likely
to get a better plan? Or is that impossible to speculate on?

Thanks again. Already you've been a big help. We love postgres and
are very happy with our upgrade to 8.4 so far!
-Jared

--
------------------
Jared Beck
Web Developer
Singlebrook Technology
(607) 330-1493
jared(at)singlebrook(dot)com