pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFighting the planner >:-(
10+ messages / 3 participants
[nested] [flat]
* Fighting the planner >:-(
@ 2013-02-01 17:11 Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
0 siblings, 1 reply; 10+ messages in thread
From: Casey Allen Shobe @ 2013-02-01 17:11 UTC (permalink / raw)
To: pgsql-performance
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@shobe.info
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
@ 2013-02-01 17:54 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Casey Allen Shobe @ 2013-02-01 17:54 UTC (permalink / raw)
To: 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@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
> casey@shobe.info
>
>
>
--
Casey Allen Shobe
casey@shobe.info
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
@ 2013-02-01 18:50 ` Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2013-02-01 18:50 UTC (permalink / raw)
To: Casey Allen Shobe <casey@shobe.info>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
@ 2013-02-01 19:18 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:02 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
0 siblings, 1 reply; 10+ messages in thread
From: Casey Allen Shobe @ 2013-02-01 19:18 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
On Fri, Feb 1, 2013 at 1:50 PM, Richard Huxton <dev@archonet.com> wrote:
> 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.
>
You have the correct pastes. I did an alter to int in an attempt to see if
that made any difference. It didn't. It takes a couple hours to do that
alter so I didn't convert it back to smallint.
> 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.
>
Configuration is pretty standard. As for the machine, it's a VM in an ESXi
setup, with dedicated resources. The disk is very fast and is barely
touched. One CPU sits at 100% for as long as I let the query run. There
is 18GB RAM and PostgreSQL is the only service running on the machine.
> 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.
>
That's why I sent the followup re-parameterizing everything. And the
explains are on prepared statements with the parameterization. If I just
put the parameter values directly into the query and run it straight, it's
fast.
1. Can you just check and see if any of the row estimates are horribly off
> for any particular clause in the query?
>
Yes they are. The places where the estimate is rows=1, particularly.
> 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.
>
21861KB. I tried setting it to 192MB and re-preparing the same statement.
Here's the explain execute: http://explain.depesz.com/s/pZ0, which looks
identical as before.
--
Casey Allen Shobe
casey@shobe.info
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
@ 2013-02-01 21:02 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:12 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Casey Allen Shobe @ 2013-02-01 21:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Rhodiumtoad on IRC helped me figure out how to move part of the query into
a CTE in order to work around the planner problem. This is a hack but it
brings the time down from many hours to 17 seconds, which is better than it
was even with the better plan in the first place! For some reason it
actually gets 2 seconds faster yet by putting it in a SQL function rather
than using prepare/execute.
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
--
Casey Allen Shobe
casey@shobe.info
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:02 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
@ 2013-02-01 21:12 ` Виктор Егоров <vyegorov@gmail.com>
2013-02-01 21:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
0 siblings, 1 reply; 10+ messages in thread
From: Виктор Егоров @ 2013-02-01 21:12 UTC (permalink / raw)
To: Casey Allen Shobe <casey@shobe.info>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:02 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:12 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
@ 2013-02-01 21:18 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:55 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Casey Allen Shobe @ 2013-02-01 21:18 UTC (permalink / raw)
To: Виктор Егоров <vyegorov@gmail.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
On Fri, Feb 1, 2013 at 4:12 PM, Виктор Егоров <vyegorov@gmail.com> wrote:
> 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?
>
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.
--
Casey Allen Shobe
casey@shobe.info
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:02 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:12 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
2013-02-01 21:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
@ 2013-02-01 21:55 ` Виктор Егоров <vyegorov@gmail.com>
2013-02-01 22:03 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
0 siblings, 1 reply; 10+ messages in thread
From: Виктор Егоров @ 2013-02-01 21:55 UTC (permalink / raw)
To: Casey Allen Shobe <casey@shobe.info>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:02 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:12 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
2013-02-01 21:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:55 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
@ 2013-02-01 22:03 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 22:17 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Casey Allen Shobe @ 2013-02-01 22:03 UTC (permalink / raw)
To: Виктор Егоров <vyegorov@gmail.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
I'll get back to you on this Monday - I'm heading home for the week now.
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;
^
On Fri, Feb 1, 2013 at 4:55 PM, Виктор Егоров <vyegorov@gmail.com> wrote:
> 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
>
--
Casey Allen Shobe
casey@shobe.info
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fighting the planner >:-(
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Re: Fighting the planner >:-( Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:02 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:12 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
2013-02-01 21:18 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:55 ` Re: Fighting the planner >:-( Виктор Егоров <vyegorov@gmail.com>
2013-02-01 22:03 ` Re: Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
@ 2013-02-01 22:17 ` Виктор Егоров <vyegorov@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Виктор Егоров @ 2013-02-01 22:17 UTC (permalink / raw)
To: Casey Allen Shobe <casey@shobe.info>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2013-02-01 22:17 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-01 17:11 Fighting the planner >:-( Casey Allen Shobe <casey@shobe.info>
2013-02-01 17:54 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 18:50 ` Richard Huxton <dev@archonet.com>
2013-02-01 19:18 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:02 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:12 ` Виктор Егоров <vyegorov@gmail.com>
2013-02-01 21:18 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 21:55 ` Виктор Егоров <vyegorov@gmail.com>
2013-02-01 22:03 ` Casey Allen Shobe <casey@shobe.info>
2013-02-01 22:17 ` Виктор Егоров <vyegorov@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox