Thread: Optimizer choosing the wrong plan
*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
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
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
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