pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedReasons for choosing one execution plan over another?
6+ messages / 4 participants
[nested] [flat]
* Reasons for choosing one execution plan over another?
@ 2013-09-11 11:16 Mikkel Lauritsen <renard@tala.dk>
2013-09-11 16:20 ` Re: Reasons for choosing one execution plan over another? Jeff Janes <jeff.janes@gmail.com>
2013-09-11 16:55 ` Re: Reasons for choosing one execution plan over another? Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
0 siblings, 2 replies; 6+ messages in thread
From: Mikkel Lauritsen @ 2013-09-11 11:16 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have a number of Postgres 9.2.4 databases with the same schema but with
slightly different contents, running on small servers that are basically
alike (8-16 GB ram).
When I run the same query on these databases it results in one of two
different execution plans where one is much faster (appx. 50 times) than
the other. Each database always gives the same plan, and vacuuming,
updating statistics and reindexing doesn't seem to make any difference.
Clearly the fast plan is preferred, but I haven't been able to identify
any pattern (table sizes, tuning etc.) in why one plan is chosen over the
other, so is there any way I can make Postgres tell me why it chooses to
plan the way it does?
As reference I have included the query and execution plans for two
different databases below. The x and e tables contain about 5 and 10
million records respectively, and the performance difference is perfectly
reasonable as the outer loop has to process 3576 rows in the fast case and
154149 rows in the slow case.
Best regards & thanks,
Mikkel Lauritsen
---
Query:
SELECT x.r, e.id, a.id
FROM x
INNER JOIN e ON x.id = e.id
INNER JOIN a ON x.a_id = a.id
INNER JOIN i ON a.i_id = i.id
WHERE e.h_id = 'foo' AND i.c = 'bar';
Fast plan:
Nested Loop (cost=0.00..24553.77 rows=1 width=86) (actual
time=2.810..102.451 rows=20 loops=1)
Join Filter: (x.a_id = a.id)
Rows Removed by Join Filter: 3556
-> Nested Loop (cost=0.00..16.55 rows=1 width=39) (actual
time=0.036..0.046 rows=3 loops=1)
-> Index Scan using i_c_idx on i (cost=0.00..8.27 rows=1
width=39) (actual time=0.019..0.020 rows=1 loops=1)
Index Cond: (c = 'bar'::text)
-> Index Scan using a_i_id_idx on a (cost=0.00..8.27 rows=1
width=78) (actual time=0.014..0.021 rows=3 loops=1)
Index Cond: (i_id = i.id)
-> Nested Loop (cost=0.00..24523.00 rows=1138 width=86) (actual
time=2.641..33.818 rows=1192 loops=3)
-> Index Scan using e_h_id_idx on e (cost=0.00..6171.55
rows=1525 width=39) (actual time=0.049..1.108 rows=1857 loops=3)
Index Cond: (h_id = 'foo'::text)
-> Index Scan using x_id_idx on x (cost=0.00..12.02 rows=1
width=86) (actual time=0.017..0.017 rows=1 loops=5571)
Index Cond: (id = e.id)
Total runtime: 102.526 ms
Slow plan:
Nested Loop (cost=0.00..858.88 rows=1 width=86) (actual
time=89.430..2589.905 rows=11 loops=1)
-> Nested Loop (cost=0.00..448.38 rows=169 width=86) (actual
time=0.135..142.246 rows=154149 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=39) (actual
time=0.056..0.064 rows=3 loops=1)
-> Index Scan using i_c_idx on i (cost=0.00..8.27 rows=1
width=39) (actual time=0.030..0.030 rows=1 loops=1)
Index Cond: (c = 'bar'::text)
-> Index Scan using a_i_id_idx on a (cost=0.00..8.27
rows=1 width=78) (actual time=0.022..0.028 rows=3 loops=1)
Index Cond: (i_id = i.id)
-> Index Scan using x_a_id_idx on x (cost=0.00..372.48
rows=5935 width=86) (actual time=0.065..35.479 rows=51383 loops=3)
Index Cond: (a_id = a.id)
-> Index Scan using e_pkey on e (cost=0.00..2.42 rows=1 width=39)
(actual time=0.015..0.015 rows=0 loops=154149)
Index Cond: (id = x.id)
Filter: (h_id = 'foo'::text)
Rows Removed by Filter: 1
Total runtime: 2589.970 ms
--
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] 6+ messages in thread
* Re: Reasons for choosing one execution plan over another?
2013-09-11 11:16 Reasons for choosing one execution plan over another? Mikkel Lauritsen <renard@tala.dk>
@ 2013-09-11 16:20 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jeff Janes @ 2013-09-11 16:20 UTC (permalink / raw)
To: Mikkel Lauritsen <renard@tala.dk>; +Cc: pgsql-performance
On Wed, Sep 11, 2013 at 4:16 AM, Mikkel Lauritsen <renard@tala.dk> wrote:
> Hi all,
>
> I have a number of Postgres 9.2.4 databases with the same schema but with
> slightly different contents, running on small servers that are basically
> alike (8-16 GB ram).
>
> When I run the same query on these databases it results in one of two
> different execution plans where one is much faster (appx. 50 times) than
> the other. Each database always gives the same plan, and vacuuming,
> updating statistics and reindexing doesn't seem to make any difference.
>
> Clearly the fast plan is preferred, but I haven't been able to identify
> any pattern (table sizes, tuning etc.) in why one plan is chosen over the
> other, so is there any way I can make Postgres tell me why it chooses to
> plan the way it does?
>
Are you sure the schemas are identical, including the existence of
identical indexes?
Also, using "explain (analyze, buffers)" gives more info than just "explain
analyze"
If you can get both systems to use the same plan, then you can compare the
cost estimates of each directly. But that is easier said than done.
You can temporarily drop an index used in the slow query but not the fast
one, to see what plan that comes up with:
begin; drop index x_a_id_idx; <run query>; rollback;
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Reasons for choosing one execution plan over another?
2013-09-11 11:16 Reasons for choosing one execution plan over another? Mikkel Lauritsen <renard@tala.dk>
@ 2013-09-11 16:55 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-09-11 21:07 ` Re: Reasons for choosing one execution plan over another? Gavin Flower <GavinFlower@archidevsys.co.nz>
1 sibling, 1 reply; 6+ messages in thread
From: Giuseppe Broccolo @ 2013-09-11 16:55 UTC (permalink / raw)
To: pgsql-performance
Il 11/09/2013 13:16, Mikkel Lauritsen ha scritto:
> Hi all,
>
> I have a number of Postgres 9.2.4 databases with the same schema but with
> slightly different contents, running on small servers that are basically
> alike (8-16 GB ram).
>
I think that your answer can be found in your statement "slightly
different contents". Planner choices query execution plans basing on
statistics obtained during ANALYSE operations, including the autovacuum.
In this way, Planner can decide which execution plan is the most
suitable. Different content of values in your table could correspond to
different statistical distribution of values in your columns and of rows
in your tables, bringing to different choices of the Planner. Execution
times can be very different, also by factor 10-100.
There is a parameter (stat_target) which set the "selectivity" of
statistical samples of a table. Maybe, but it's not necessarily true,
you could obtain more comparable execution times for the two execution
plans changing it, probably increasing them.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
--
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] 6+ messages in thread
* Re: Reasons for choosing one execution plan over another?
2013-09-11 11:16 Reasons for choosing one execution plan over another? Mikkel Lauritsen <renard@tala.dk>
2013-09-11 16:55 ` Re: Reasons for choosing one execution plan over another? Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
@ 2013-09-11 21:07 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 0 replies; 6+ messages in thread
From: Gavin Flower @ 2013-09-11 21:07 UTC (permalink / raw)
To: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>; +Cc: pgsql-performance
On 12/09/13 04:55, Giuseppe Broccolo wrote:
> Il 11/09/2013 13:16, Mikkel Lauritsen ha scritto:
>> Hi all,
>>
>> I have a number of Postgres 9.2.4 databases with the same schema but
>> with
>> slightly different contents, running on small servers that are basically
>> alike (8-16 GB ram).
>>
> I think that your answer can be found in your statement "slightly
> different contents". Planner choices query execution plans basing on
> statistics obtained during ANALYSE operations, including the
> autovacuum. In this way, Planner can decide which execution plan is
> the most suitable. Different content of values in your table could
> correspond to different statistical distribution of values in your
> columns and of rows in your tables, bringing to different choices of
> the Planner. Execution times can be very different, also by factor
> 10-100.
>
> There is a parameter (stat_target) which set the "selectivity" of
> statistical samples of a table. Maybe, but it's not necessarily true,
> you could obtain more comparable execution times for the two execution
> plans changing it, probably increasing them.
>
> Giuseppe.
>
Even identical content could lead to different plans, as the sampling is
done randomly (or at least 'randomly' according to the documentation).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Reasons for choosing one execution plan over another?
@ 2013-09-11 19:23 Mikkel Lauritsen <renard@tala.dk>
2013-09-12 13:29 ` Re: Reasons for choosing one execution plan over another? Mikkel Lauritsen <renard@tala.dk>
0 siblings, 1 reply; 6+ messages in thread
From: Mikkel Lauritsen @ 2013-09-11 19:23 UTC (permalink / raw)
To: pgsql-performance
Hi all,
On Wed, 11 Sep 2013 18:55:38 +0200, Giuseppe Broccolo
<giuseppe.broccolo@2ndquadrant.it> wrote:
> Il 11/09/2013 13:16, Mikkel Lauritsen ha scritto:
> > Hi all,
> >
> > I have a number of Postgres 9.2.4 databases with the same schema but
> > with slightly different contents, running on small servers that are
> > basically alike (8-16 GB ram).
>
> I think that your answer can be found in your statement "slightly
> different contents".
Yup, that's what I've been thinking myself so far - it definitely doesn't
look as if I'm hitting a bug, and I've been through the schema so I feel
reasonably sure that I'm not missing an index.
In the example from my original mail the i and a tables are identical in
the two databases. The slow plan is chosen when the x and e tables contain
3.2M and 6.2M rows, the fast plan has 12.8M and 17M rows.
So - does anybody with enough insight in the planner know if it sounds
likely that it would choose the given plans in these two cases, or if
it's more likely that I have a tuning problem that leads to bad planning?
And if the different plans are to be expected, is there any way I can hint
at the planner to make it choose the fast plan in both cases?
FWIW if I do a very simple query like
SELECT e.id FROM e INNER JOIN x USING (id) WHERE e.h_id = 'foo';
on the two databases I also end up with two different plans (included
below). Here the execution time difference is much less pronounced (note
that the "fast" execution is on inferior hardware and with a much larger
result), but the way the join is executed is the same as in the initial
larger plans. Setting seq_page_cost and random_page_cost to the same
value makes the planner choose the fast plan in both cases, but unfortu-
nately that has no effect on my initial problem :-/
Best regards & thanks,
Mikkel Lauritsen
---
Fast plan:
Nested Loop (cost=0.00..24523.00 rows=1138 width=39) (actual
time=2.546..33.858 rows=1192 loops=1)
Buffers: shared hit=8991
-> Index Scan using e_h_id_idx on e (cost=0.00..6171.55 rows=1525
width=39) (actual time=0.053..1.211 rows=1857 loops=1)
Index Cond: (healthtrack_id =
'-95674114670403931535179954575983492851'::text)
Buffers: shared hit=350
-> Index Only Scan using x_pkey on x (cost=0.00..12.02 rows=1
width=39) (actual time=0.017..0.017 rows=1 loops=1857)
Index Cond: (id = e.id)
Heap Fetches: 1192
Buffers: shared hit=8641
Total runtime: 34.065 ms
Slow plan:
Nested Loop (cost=22.25..7020.66 rows=277 width=39) (actual
time=0.298..13.996 rows=228 loops=1)
Buffers: shared hit=3173
-> Bitmap Heap Scan on e (cost=22.25..2093.50 rows=537 width=39)
(actual time=0.219..0.628 rows=697 loops=1)
Recheck Cond: (healthtrack_id = 'foo'::text)
Buffers: shared hit=152
-> Bitmap Index Scan on e_h_id_idx (cost=0.00..22.12 rows=537
width=0) (actual time=0.188..0.188 rows=697 loops=1)
Index Cond: (h_id = 'foo'::text)
Buffers: shared hit=9
-> Index Only Scan using x_pkey on x (cost=0.00..9.17 rows=1
width=39) (actual time=0.018..0.018 rows=0 loops=697)
Index Cond: (id = e.id)
Heap Fetches: 228
Buffers: shared hit=3021
Total runtime: 14.070 ms
--
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] 6+ messages in thread
* Re: Reasons for choosing one execution plan over another?
2013-09-11 19:23 Re: Reasons for choosing one execution plan over another? Mikkel Lauritsen <renard@tala.dk>
@ 2013-09-12 13:29 ` Mikkel Lauritsen <renard@tala.dk>
0 siblings, 0 replies; 6+ messages in thread
From: Mikkel Lauritsen @ 2013-09-12 13:29 UTC (permalink / raw)
To: pgsql-performance
I wrote:
--- snip ---
> So - does anybody with enough insight in the planner know if it sounds
> likely that it would choose the given plans in these two cases, or if
> it's more likely that I have a tuning problem that leads to bad
> planning?
Duh. It suddenly dawned on me that I need to look closer at the plans...
The big difference in the estimated and actual row count in lines like
-> Nested Loop (cost=0.00..250.78 rows=338 width=47) (actual
time=0.100..189.676 rows=187012 loops=1)
indicates that the planner is somehow mislead by the statistics on (at
least) one of the tables, right? Any suggestions as to how I go about
investigating that further?
One thing here that is slightly confusing is the relationship between
the estimated row count of 169 in the outer loop and 6059 in the last
index scan in the partial plan below. How do they relate to each other?
-> Nested Loop (cost=0.00..452.10 rows=169 width=47) (actual
time=0.088..41.244 rows=32863 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=39) (actual
time=0.031..0.035 rows=1 loops=1)
-> Index Scan using i_c_id on i (cost=0.00..8.27 rows=1
width=39) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: (c = 'bar'::text)
-> Index Scan using a_i_id_idx on a (cost=0.00..8.27 rows=1
width=78) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (i_id = i.id)
-> Index Scan using x_a_id_idx on x (cost=0.00..374.95 rows=6059
width=86) (actual time=0.055..27.219 rows=32863 loops=1)
Index Cond: (a_id = a.id)
Best regards & thanks,
Mikkel Lauritsen
--
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] 6+ messages in thread
end of thread, other threads:[~2013-09-12 13:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-09-11 11:16 Reasons for choosing one execution plan over another? Mikkel Lauritsen <renard@tala.dk>
2013-09-11 16:20 ` Jeff Janes <jeff.janes@gmail.com>
2013-09-11 16:55 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-09-11 21:07 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2013-09-11 19:23 Re: Reasons for choosing one execution plan over another? Mikkel Lauritsen <renard@tala.dk>
2013-09-12 13:29 ` Mikkel Lauritsen <renard@tala.dk>
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