From: | Casey Allen Shobe <casey(at)shobe(dot)info> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Fighting the planner >:-( |
Date: | 2013-02-01 17:54:09 |
Message-ID: | CAFmVg3gLE9QZRN0pPZdaL6+=1tYC0nPWE7DjPkvYQ2gcnDyQRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
My apologies - I included the wrong version of the query before...during
testing I had tried deparameterizing a few of the input parameters. I
also accidentally left out the schema for the network_config_tot2 table
from the initial paste.
Here is an updated paste, which shows the correct query in a prepare
statements. The explain plans are from explain execute hewitt_test (...):
http://pgsql.privatepaste.com/00c582c840
Here is the correct explain plan for this statement (still bad):
http://explain.depesz.com/s/c46
On Fri, Feb 1, 2013 at 12:11 PM, Casey Allen Shobe <casey(at)shobe(dot)info> wrote:
> So where I'm working, a performance issue was identified that affected
> many functions, because the (SQL language) functions took an int argument
> used it in a where clause against a column (config_id) that was stored in
> varchar format, leading to an inefficient casting when the query was
> parameterized. We could work around that with (select $3::text) instead of
> just $3, but since the data is actually all numbers under 65k, we altered
> the data type of the column to smallint, rather than editing a boatload of
> functions with a hacky workaround.
>
> For most functions, this fixed the problem.
>
> However, it had a drastically-negative impact on the query in question,
> which was originally taking 2 minutes, 45 seconds. After adding a couple
> indexes with the config_id still as a varchar, that time is reduced down to
> 42 seconds. However when the data type is smallint, the query runs for
> many hours - I let it run for 4.5 hours yesterday before cancelling it.
>
> It's pretty clear that the planner is making horrid misestimates and
> picking a terrible plan. I would appreciate any advice for getting this
> into a better state.
>
> Here are the explain plans:
>
> When config_id is a varchar, it executes in 42 seconds:
> http://explain.depesz.com/s/wuf
>
> When config_id is a smallint, it runs too long to allow to complete, but
> clearly the plan is bad:
> http://explain.depesz.com/s/u5P
>
> Here is the query, along with rowcounts and schema of every table involved
> in the query:
> http://pgsql.privatepaste.com/c66fd497c9
>
> PostgreSQL version is 8.4, and most of our GUC's are default.
>
> Thanks in advance for any suggestions.
> --
> Casey Allen Shobe
> casey(at)shobe(dot)info
>
>
>
--
Casey Allen Shobe
casey(at)shobe(dot)info
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2013-02-01 18:50:13 | Re: Fighting the planner >:-( |
Previous Message | Casey Allen Shobe | 2013-02-01 17:11:53 | Fighting the planner >:-( |