Обсуждение: 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

2013/2/1 Casey Allen Shobe <casey@shobe.info>:
> Hopefully some improvements to the planner can come from this information?
>
> Here is the CTE version of the query:
> http://pgsql.privatepaste.com/2f7fd3f669
> ...and  here is it's explain analyze:  http://explain.depesz.com/s/5ml

Estimated rows for ‘hewitt_1_0_factors_precalc_new’ are 1000x less then actual.
And for ‘census_user’ estimation is 100x less, then actual.

How many rows are in those tables and what is your statistics target?


--
Victor Y. Yegorov

2013/2/1 Casey Allen Shobe <casey@shobe.info>:
> Rowcounts are shown in the earlier paste link, but apparently I forgot to
> include the census table - hewitt_1_0_factors_precalc_new has 4,135,890
> rows, and census_user has 1846439 rows.
>
> Statistics target is the default at 100.

I would try the following:
ALTER TABLE hewitt_1_0_factors_precalc_new SET STATISTICS 1000;
ALTER TABLE census_user SET STATISTICS 500;
ALTER TABLE census_output SET STATISTICS 500;
and analyzed them after. I hope I guessed ‘census_output’ name correctly.

And could you kindly share the plan after:
SET enable_nestloop TO off;


--
Victor Y. Yegorov

2013/2/2 Casey Allen Shobe <casey@shobe.info>:
> However I was unable to adjust the statistics target using that command:
>
> alter table opportunity.census_user set statistics 500;
> ERROR:  syntax error at or near "statistics"
> LINE 1: alter table opportunity.census_user set statistics 500;

I'm sorry for this, my bad. Try the following:

ALTER TABLE census_user ALTER parent_id SET STATISTICS 500;
ALTER TABLE census_user ALTER stakeholder_code SET STATISTICS 500;

Do the same for all the columns in ‘hewitt_1_0_factors_precalc_new_pkey’ index,
setting target at 1000. I would also updated target for columns from
this filter:
Filter: (((h.discount_type)::text = ANY ('{"Avg Comp",Blue}'::text[]))
AND ((h.data_type)::text = 'Historical'::text) AND ((h.source)::text =
'Hewitt 1.0'::text)
AND ((h.service_catg_scheme)::text = '11+3'::text))


--
Victor Y. Yegorov