pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer choosing the wrong plan
4+ messages / 3 participants
[nested] [flat]
* Optimizer choosing the wrong plan
@ 2018-11-26 10:11 Viswanath <M.Viswanath16@gmail.com>
2018-11-26 15:53 ` Re: Optimizer choosing the wrong plan Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Viswanath @ 2018-11-26 10:11 UTC (permalink / raw)
To: pgsql-performance
*Postgres server version - 9.5.10*
*RAM - 128 GB*
*WorkMem 64 MB*
*Problematic query with explain :*
*Query 1 (original):*
explain analyse SELECT myTable1.ID FROM myTable1 LEFT JOIN myTable2 ON
myTable1.ID=myTable2.ID WHERE ((((myTable1.bool_val = true) AND
(myTable1.small_intval IN (1,2,3))) AND ((*myTable2.bigint_val = 1*) AND
(myTable1.bool_val = true))) AND (((myTable1.ID >= 1000000000000) AND
(myTable1.ID <= 1999999999999)) )) ORDER BY 1 DESC , 1 NULLS FIRST LIMIT
11;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.00..8077.43 rows=11 width=8) (actual time=6440.245..6440.245
rows=0 loops=1)
-> Nested Loop (cost=1.00..1268000.55 *rows=1727* width=8) (actual
time=6440.242..6440.242 rows=0 loops=1)
-> Index Scan Backward using myTable2_fk1_idx on myTable2
(cost=0.43..1259961.54 rows=1756 width=8) (actual time=6440.241..6440.241
rows=0 loops=1)
Filter: (bigint_val = 1)
Rows Removed by Filter: 12701925
-> Index Scan using myTable1_fk2_idx on myTable1 (cost=0.56..4.57
rows=1 width=8) (never executed)
Index Cond: ((id = myTable2.id) AND (id >=
'1000000000000'::bigint) AND (id <= '1999999999999'::bigint))
Filter: (bool_val AND bool_val AND (small_intval = ANY
('{1,2,3}'::integer[])))
Planning time: 0.654 ms
Execution time: 6440.353 ms
(10 rows)
*The columns myTable1.ID and myTable2.bigint_val = 1 both are indexed*
The table myTable2 contains *12701952* entries. Out of which only *86227* is
not null and *146* entries are distinct.
The above query returns 0 rows since 'myTable2.bigint_val = 1' criteria
satisfies nothing. It takes 6 seconds for execution as the planner chooses*
myTable1.ID column's index*.
If I use nulls last on the order by clause of the query then the planner
chooses this plan since it doesn't use index for *DESC NULLS LAST*. And the
query executes in milliseconds.
*Query 2 (order by modified to avoid index):*
explain analyse SELECT myTable1.ID FROM myTable1 LEFT JOIN myTable2 ON
myTable1.ID=myTable2.ID WHERE ((((myTable1.bool_val = true) AND
(myTable1.small_intval IN (1,2,3))) AND ((myTable2.bigint_val = 1) AND
(myTable1.bool_val = true))) AND (((myTable1.ID >= 1000000000000) AND
(myTable1.ID <= 1999999999999)) )) ORDER BY 1 DESC *NULLS LAST*, 1 NULLS
FIRST LIMIT 11;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=11625.07..11625.10 rows=11 width=8) (actual time=0.028..0.028
rows=0 loops=1)
-> Sort (cost=11625.07..11629.39 rows=1727 width=8) (actual
time=0.028..0.028 rows=0 loops=1)
Sort Key: myTable1.id DESC NULLS LAST
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.85..11586.56 *rows=1727 *width=8) (actual
time=0.024..0.024 rows=0 loops=1)
-> Index Scan using bigint_val_idx_px on myTable2
(cost=0.29..3547.55 rows=1756 width=8) (actual time=0.024..0.024 rows=0
loops=1)
Index Cond: (bigint_val = 1)
-> Index Scan using myTable1_fk2_idx on myTable1
(cost=0.56..4.57 rows=1 width=8) (never executed)
Index Cond: ((id = myTable2.id) AND (id >=
'1000000000000'::bigint) AND (id <= '1999999999999'::bigint))
Filter: (bool_val AND bool_val AND (small_intval = ANY
('{1,2,3}'::integer[])))
Planning time: 0.547 ms
Execution time: 0.110 ms
The reason why postgres chooses the 1st plan over the 2nd was due to it's
cost. *plan 1 - 8077.43 and plan 2 - 11625.10* . But obviously plan 2 is
correct.
I tried running *vacuum analyse* table many times, tried changing the
*statistics target of the column to 250 (since there are only 149 distinct
values)*. But none worked out. The planner thinks that there are *1727* rows
that matches the condition *myTable2.bigint_val = 1* but there are none.
Also I tried changing the limit of the 1st query, increasing the limit
increases the cost of the 1st plan so if I use 16 as limit for the same 1st
query the planner chooses the 2nd plan.
*Query 3 (same as 1st but limit increased to 16):*
explain analyse SELECT myTable1.ID FROM myTable1 LEFT JOIN myTable2 ON
myTable1.ID=myTable2.ID WHERE ((((myTable1.bool_val = true) AND
(myTable1.small_intval IN (1,2,3))) AND ((myTable2.bigint_val = 1) AND
(myTable1.bool_val = true))) AND (((myTable1.ID >= 1000000000000) AND
(myTable1.ID <= 1999999999999)) )) ORDER BY 1 DESC , 1 NULLS FIRST *LIMIT
16*;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=11629.74..11629.78 rows=16 width=8) (actual time=0.043..0.043
rows=0 loops=1)
-> Sort (cost=11629.74..11634.05 rows=1727 width=8) (actual
time=0.042..0.042 rows=0 loops=1)
Sort Key: myTable1.id DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.85..11586.56 rows=1727 width=8) (actual
time=0.036..0.036 rows=0 loops=1)
-> Index Scan using bigint_val_idx_px on myTable2
(cost=0.29..3547.55 rows=1756 width=8) (actual time=0.036..0.036 rows=0
loops=1)
Index Cond: (bigint_val = 1)
-> Index Scan using myTable1_fk2_idx on myTable1
(cost=0.56..4.57 rows=1 width=8) (never executed)
Index Cond: ((id = myTable2.id) AND (id >=
'1000000000000'::bigint) AND (id <= '1999999999999'::bigint))
Filter: (bool_val AND bool_val AND (small_intval = ANY
('{1,2,3}'::integer[])))
Planning time: 0.601 ms
Execution time: 0.170 ms
(12 rows)
Is there any way to make postgres use the myTable2.bigint_val's index by
changing/optimizing parameters?
I tried changing cost parameters too but since bot plan uses index scan it
doesn't affect much. Is there any
way to set *how the cost works based on limit*?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer choosing the wrong plan
2018-11-26 10:11 Optimizer choosing the wrong plan Viswanath <M.Viswanath16@gmail.com>
@ 2018-11-26 15:53 ` Jeff Janes <jeff.janes@gmail.com>
2018-12-29 12:17 ` Re: Optimizer choosing the wrong plan Jim Finnerty <jfinnert@amazon.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Janes @ 2018-11-26 15:53 UTC (permalink / raw)
To: Viswanath <M.Viswanath16@gmail.com>; +Cc: pgsql-performance
On Mon, Nov 26, 2018 at 5:11 AM Viswanath <M.Viswanath16@gmail.com> wrote:
> *Postgres server version - 9.5.10*
> *RAM - 128 GB*
> *WorkMem 64 MB*
>
> *Problematic query with explain :*
> *Query 1 (original):*
> explain analyse SELECT myTable1.ID FROM myTable1 LEFT JOIN myTable2 ON
> myTable1.ID=myTable2.ID WHERE ((((myTable1.bool_val = true) AND
> (myTable1.small_intval IN (1,2,3))) AND ((*myTable2.bigint_val = 1*) AND
> (myTable1.bool_val = true))) AND (((myTable1.ID >= 1000000000000) AND
> (myTable1.ID <= 1999999999999)) )) ORDER BY 1 DESC , 1 NULLS FIRST LIMIT
> 11;
>
There is no point doing a LEFT JOIN when the NULL-extended rows get
filtered out later.
Also, ordering by the same column twice is peculiar, to say the least.
> The table myTable2 contains *12701952* entries. Out of which only *86227*
> is
> not null and *146* entries are distinct.
>
I assume you mean the column myTable2.ID has that many not null and
distinct?
>
> The above query returns 0 rows since 'myTable2.bigint_val = 1' criteria
> satisfies nothing. It takes 6 seconds for execution as the planner chooses*
> myTable1.ID column's index*.
More importantly, it chooses the index on myTable2.ID. It does also use
the index on myTable1.ID, but that is secondary.
The ideal index for this query would probably be a composite index on
myTable2 (bigint_val, id DESC);
The planner would probably choose to use that index, even if the statistics
are off.
I tried running *vacuum analyse* table many times, tried changing the
> *statistics target of the column to 250 (since there are only 149 distinct
> values)*. But none worked out. The planner thinks that there are *1727*
> rows
> that matches the condition *myTable2.bigint_val = 1* but there are none.
>
It would interesting if you can upgrade a copy of your server to v11 and
try it there. We made changes to ANALYZE in that version which were
intended to improve this situation, and it would be nice to know if it
actually did so for your case.
Also, increasing statistics target even beyond 250 might help. If every
one of the observed value is seen at least twice, it will trigger the
system to assume that it has observed all distinct values that exist. But
if any of the values are seen exactly once, that causes it to take a
different path (the one which got modified in v11).
Cheers,
Jeff
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer choosing the wrong plan
2018-11-26 10:11 Optimizer choosing the wrong plan Viswanath <M.Viswanath16@gmail.com>
2018-11-26 15:53 ` Re: Optimizer choosing the wrong plan Jeff Janes <jeff.janes@gmail.com>
@ 2018-12-29 12:17 ` Jim Finnerty <jfinnert@amazon.com>
2018-12-29 17:14 ` Re: Optimizer choosing the wrong plan Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jim Finnerty @ 2018-12-29 12:17 UTC (permalink / raw)
To: pgsql-performance
re: It would interesting if you can upgrade a copy of your server to v11 and
try it there. We made changes to ANALYZE in that version which were
intended to improve this situation, and it would be nice to know if it
actually did so for your case.
Jeff, can you describe the changes that were made to ANALYZE in v11, please?
I've found that running ANALYZE on v10 on the Join Order Benchmark, using
the default statistics target of 100, produces quite unstable results, so
I'd be interested to hear what has been improved in v11.
/Jim
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer choosing the wrong plan
2018-11-26 10:11 Optimizer choosing the wrong plan Viswanath <M.Viswanath16@gmail.com>
2018-11-26 15:53 ` Re: Optimizer choosing the wrong plan Jeff Janes <jeff.janes@gmail.com>
2018-12-29 12:17 ` Re: Optimizer choosing the wrong plan Jim Finnerty <jfinnert@amazon.com>
@ 2018-12-29 17:14 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2018-12-29 17:14 UTC (permalink / raw)
To: Jim Finnerty <jfinnert@amazon.com>; +Cc: pgsql-performance
On Sat, Dec 29, 2018 at 7:17 AM Jim Finnerty <jfinnert@amazon.com> wrote:
> Jeff, can you describe the changes that were made to ANALYZE in v11,
> please?
>
> I've found that running ANALYZE on v10 on the Join Order Benchmark, using
> the default statistics target of 100, produces quite unstable results, so
> I'd be interested to hear what has been improved in v11.
>
There are two paths the code can take. One if all values which were
sampled at all were sampled at least twice, and another if the
least-sampled value was sampled exactly once. For some distributions (like
exponential-ish or maybe power-law), it is basically a coin flip whether
the least-sampled value is seen once, or more than once. If you are seeing
instability, it is probably for this reason. That fundamental instability
was not addressed in v11.
Once you follow the "something seen exactly once" path, it has to decide
how many of the values get represented in the most-common-value list. That
is where the change was. The old method said a value had to have an
estimated prevalence at least 25% more than the average estimated
prevalence to get accepted into the list. The problem is that if there
were a few dominant values, it wouldn't be possible for any others to be
"over-represented" because those few dominant values dragged the average
prevalence up so far nothing else could qualify. What it was changed to
was to include a value in the most-common-value list if its
overrepresentation was statistically significant given the sample size.
The most significant change (from my perspective) is that
over-representation is measured not against all values, but only against
all values more rare in the sample then the one currently being considered
for inclusion into the MCV. The old method basically said "all rare values
are the same", while the new method realizes that a rare value present
10,000 times in a billion row table is much different than a rare value
present 10 time in a billion row table.
It is possible that this change will fix the instability for you, because
it could cause the "seen exactly once" path to generate a MCV list which is
close enough in size to the "seen at least twice" path that you won't
notice the difference between them anymore. But, it is also possible they
will still be different enough in size that it will still appear unstable.
It depends on your distribution of values.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-12-29 17:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-26 10:11 Optimizer choosing the wrong plan Viswanath <M.Viswanath16@gmail.com>
2018-11-26 15:53 ` Jeff Janes <jeff.janes@gmail.com>
2018-12-29 12:17 ` Jim Finnerty <jfinnert@amazon.com>
2018-12-29 17:14 ` Jeff Janes <jeff.janes@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