*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
