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 <ca...@shobe.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 > ca...@shobe.info > > > -- Casey Allen Shobe ca...@shobe.info
