Performance Issue -- "Materialize"

[anand086 <anand086@xxxxxxxxx>]

I am a Postgres Newbie and trying to learn :)

We have a scenario wherein, one of the SQL with different input value for  import_num showing different execution plan.

As an example, with import_num = '4520440' the execution plan shows Nested Loop and is taking ~12secs. 
With import_num = '4520460' execution plan showed using "Materialize" and never completed. After I set enable_material to off, the execution plan is changed using Hash Semi Join and completes in less than 3 secs. 

SELECT count(*) FROM test_tab WHERE login IN (SELECT DISTINCT login FROM test_tab WHERE import_num = '4520440' AND 
login IS NOT NULL EXCEPT SELECT DISTINCT login FROM test_tab WHERE import_num = '0' AND login IS NOT NULL) 
AND import_num = '4520440';

+--------+
| count  |
+--------+
| 746982 |
+--------+
(1 row)

Time: 12054.274 ms

+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                        QUERY PLAN                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=351405.08..351405.09 rows=1 width=8)                                                                                                     |
|   ->  Nested Loop  (cost=349846.23..350366.17 rows=415562 width=0)                                                                                        |
|         ->  HashAggregate  (cost=349845.67..349847.67 rows=200 width=96)                                                                                  |
|               Group Key: ("ANY_subquery".login)::text                                                                                                     |
|               ->  Subquery Scan on "ANY_subquery"  (cost=340828.23..348557.47 rows=515282 width=96)                                                       |
|                     ->  SetOp Except  (cost=340828.23..343404.65 rows=515282 width=100)                                                                   |
|                           ->  Sort  (cost=340828.23..342116.44 rows=515283 width=100)                                                                     |
|                                 Sort Key: "*SELECT* 1".login                                                                                              |
|                                 ->  Append  (cost=0.56..275836.74 rows=515283 width=100)                                                                  |
|                                       ->  Subquery Scan on "*SELECT* 1"  (cost=0.56..275834.70 rows=515282 width=12)                                      |
|                                             ->  Unique  (cost=0.56..270681.88 rows=515282 width=8)                                                        |
|                                                   ->  Index Only Scan using ui_nkey_test_tab on test_tab test_tab_1  (cost=0.56..268604.07 rows=831125 width=8) |
|                                                         Index Cond: ((import_num = '4520440'::numeric) AND (login IS NOT NULL))                           |
|                                       ->  Subquery Scan on "*SELECT* 2"  (cost=0.56..2.04 rows=1 width=12)                                                |
|                                             ->  Unique  (cost=0.56..2.03 rows=1 width=8)                                                                  |
|                                                   ->  Index Only Scan using ui_nkey_test_tab on test_tab test_tab_2  (cost=0.56..2.03 rows=1 width=8)           |
|                                                         Index Cond: ((import_num = '0'::numeric) AND (login IS NOT NULL))                                 |
|         ->  Index Only Scan using ui_nkey_test_tab on test_tab  (cost=0.56..2.58 rows=1 width=8)                                                              |
|               Index Cond: ((import_num = '4520440'::numeric) AND (login = ("ANY_subquery".login)::text))                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
(19 rows)


SELECT count(*) FROM test_tab WHERE import_num = '4520460' and login IN (SELECT DISTINCT login FROM test_tab WHERE import_num = '4520460' AND login IS NOT NULL EXCEPT SELECT DISTINCT login FROM test_tab WHERE import_num = '0' AND login IS NOT NULL);

The SQL was never completing and had the below SQL execution plan --


+-------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                QUERY PLAN                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=6.14..6.15 rows=1 width=8)                                                                                               |
|   ->  Nested Loop Semi Join  (cost=1.12..6.13 rows=1 width=0)                                                                             |
|         Join Filter: ((test_tab.login)::text = ("ANY_subquery".login)::text)                                                                |
|         ->  Index Only Scan using ui_nkey_test_tab on test_tab  (cost=0.56..2.02 rows=1 width=8)                                              |
|               Index Cond: (import_num = '4520460'::numeric)                                                                               |
|         ->  Materialize  (cost=0.56..4.10 rows=1 width=96)                                                                                |
|               ->  Subquery Scan on "ANY_subquery"  (cost=0.56..4.09 rows=1 width=96)                                                      |
|                     ->  HashSetOp Except  (cost=0.56..4.08 rows=1 width=100)                                                              |
|                           ->  Append  (cost=0.56..4.08 rows=2 width=100)                                                                  |
|                                 ->  Subquery Scan on "*SELECT* 1"  (cost=0.56..2.04 rows=1 width=12)                                      |
|                                       ->  Unique  (cost=0.56..2.03 rows=1 width=8)                                                        |
|                                             ->  Index Only Scan using ui_nkey_test_tab on test_tab test_tab_1  (cost=0.56..2.03 rows=1 width=8) |
|                                                   Index Cond: ((import_num = '4520460'::numeric) AND (login IS NOT NULL))                 |
|                                 ->  Subquery Scan on "*SELECT* 2"  (cost=0.56..2.04 rows=1 width=12)                                      |
|                                       ->  Unique  (cost=0.56..2.03 rows=1 width=8)                                                        |
|                                             ->  Index Only Scan using ui_nkey_test_tab on test_tab test_tab_2  (cost=0.56..2.03 rows=1 width=8) |
|                                                   Index Cond: ((import_num = '0'::numeric) AND (login IS NOT NULL))                       |
+-------------------------------------------------------------------------------------------------------------------------------------------+
(17 rows)

#############################################
#  After I set enable_material to off;
#############################################

SELECT count(*) FROM test_tab WHERE import_num = '4520460' and login IN (SELECT DISTINCT login FROM test_tab WHERE import_num = '4520460' AND login IS NOT NULL EXCEPT SELECT DISTINCT login FROM test_tab WHERE import_num = '0' AND login IS NOT NULL);
+--------+
| count  |
+--------+
| 762599 |
+--------+
(1 row)

Time: 2116.889 ms

+-------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                QUERY PLAN                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=6.13..6.14 rows=1 width=8)                                                                                               |
|   ->  Hash Semi Join  (cost=4.67..6.13 rows=1 width=0)                                                                                    |
|         Hash Cond: ((test_tab.login)::text = ("ANY_subquery".login)::text)                                                                  |
|         ->  Index Only Scan using ui_nkey_test_tab on test_tab  (cost=0.56..2.02 rows=1 width=8)                                              |
|               Index Cond: (import_num = '4520460'::numeric)                                                                               |
|         ->  Hash  (cost=4.09..4.09 rows=1 width=96)                                                                                       |
|               ->  Subquery Scan on "ANY_subquery"  (cost=0.56..4.09 rows=1 width=96)                                                      |
|                     ->  HashSetOp Except  (cost=0.56..4.08 rows=1 width=100)                                                              |
|                           ->  Append  (cost=0.56..4.08 rows=2 width=100)                                                                  |
|                                 ->  Subquery Scan on "*SELECT* 1"  (cost=0.56..2.04 rows=1 width=12)                                      |
|                                       ->  Unique  (cost=0.56..2.03 rows=1 width=8)                                                        |
|                                             ->  Index Only Scan using ui_nkey_test_tab on test_tab test_tab_1  (cost=0.56..2.03 rows=1 width=8) |
|                                                   Index Cond: ((import_num = '4520460'::numeric) AND (login IS NOT NULL))                 |
|                                 ->  Subquery Scan on "*SELECT* 2"  (cost=0.56..2.04 rows=1 width=12)                                      |
|                                       ->  Unique  (cost=0.56..2.03 rows=1 width=8)                                                        |
|                                             ->  Index Only Scan using ui_nkey_test_tab on test_tab test_tab_2  (cost=0.56..2.03 rows=1 width=8) |
|                                                   Index Cond: ((import_num = '0'::numeric) AND (login IS NOT NULL))                       |
+-------------------------------------------------------------------------------------------------------------------------------------------+
(17 rows)


Looking at the row count for import_num

select import_num, count(*) from test_tab group by import_num order by 2;
+------------+--------+
| import_num | count  |
+------------+--------+
|    4520440 | 746982 |
|    4520460 | 762599 |
+------------+--------+
(37 rows)


With different value of import_num we are having different execution plan. Is there a way to force the same Hash semi Join plan to sql with import_num 4520440, currently doing nested loop.

I tried  /*+HashJoin(a1 ANY_subquery)*/ but the sql execution plan doesn't change.

SELECT /*+HashJoin(a1 ANY_subquery)*/ count(*) FROM test_tab a1 WHERE import_num = '4520440' and login IN (SELECT DISTINCT login FROM test_tab  a2  WHERE import_num = '4520440' AND login IS NOT NULL EXCEPT SELECT DISTINCT login FROM test_tab  a3 WHERE import_num = '0' AND login IS NOT NULL);


Regards,
Anand

---

View this message in context: Performance Issue -- "Materialize"
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.