Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dj7hC-0007iF-DZ for pgsql-performance@arkaria.postgresql.org; Sat, 19 Aug 2017 17:38:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dj7hC-0001pE-0K for pgsql-performance@arkaria.postgresql.org; Sat, 19 Aug 2017 17:38:02 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dj7hA-0001jy-HZ for pgsql-performance@postgresql.org; Sat, 19 Aug 2017 17:38:00 +0000 Received: from mwork.nabble.com ([162.253.133.43]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dj7h7-0008CJ-J2 for pgsql-performance@postgresql.org; Sat, 19 Aug 2017 17:37:59 +0000 Received: from mben.nabble.com (unknown [162.253.133.72]) by mwork.nabble.com (Postfix) with ESMTP id D66A95DA322DE for ; Sat, 19 Aug 2017 10:37:56 -0700 (MST) Date: Sat, 19 Aug 2017 10:37:56 -0700 (MST) From: anand086 To: pgsql-performance@postgresql.org Message-ID: <1503164276875-5979128.post@n3.nabble.com> Subject: Performance Issue -- "Materialize" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_86459_1630741595.1503164276875" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_86459_1630741595.1503164276875 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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_numselect 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: http://www.postgresql-archive.org/Performance-Issue-Materialize-tp5979128.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. ------=_Part_86459_1630741595.1503164276875 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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.
------=_Part_86459_1630741595.1503164276875--