Re: Fighting the planner >:-(

On 01/02/13 17:54, Casey Allen Shobe wrote:
> 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

Three quick observations before the weekend.

1. You said config_id was now "smallint" in your email, but it reads
"int" in the pastes above.
    Doesn't matter much which, but just checking we've got the right pastes.

2. The total estimated cost of both queries is about the same
(477,225.19 for the varchar, 447,623.86 for the int).
    This suggests something about your configuration doesn't match the
performance of your machine, since presumably the int version is taking
at least twice as long as the varchar one.

3. Interestingly, the config_id search on both plans seems to be using a
Bitmap Index, so I'm not sure that's the root cause. However, the
varchar version seems to have a literal string it's matching against. If
you've manually substituted in a literal value, that could be skewing
the tests.

And two things for you to try if you would:

1. Can you just check and see if any of the row estimates are horribly
off for any particular clause in the query?

2. You mention your config settings are mostly at default. What's your
work_mem and can you increase it? You can issue a SET for the current
session, no need to change it globally. If you've got the RAM try
doubling it, then double it again. See what happens to your plan then.

--
   Richard Huxton
   Archonet Ltd