Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djtL7-00024R-8l for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 20:30:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djtL6-0006lG-AA for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 20:30:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1djtJ5-0003LA-Jk for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 20:28:19 +0000 Received: from mail-qk0-x22e.google.com ([2607:f8b0:400d:c09::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djtIw-0000iU-5C for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 20:28:18 +0000 Received: by mail-qk0-x22e.google.com with SMTP id 130so53629329qkg.2 for ; Mon, 21 Aug 2017 13:28:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=3zNNKxg8QJzXLBJzlzH9NxYdu3Y4JUt4/1AS4PtClLQ=; b=bI1yi8Fh5CsoxLGW8lnggkk/cvxDLc/fLIxYX8dENq8HPgMWNhSvAQP+/kxss2hlUf zv7ds/RDZNjSF3N0H+6TH1fG73pXqT+rkuXyQ5sJ3cVkRKV643tOMmviAdHXoMgf/yY5 iDynrXqiuxzff/EZoBDRvBmm/9HD8Mp/I7RRgzZnAplWlJTMUlNO1mMeMRj4T6lqaJ3i lS7w1Y4VS55U2JbZTGzI9dz8QcrH0fqnXHcBfI1wGonrUkCF3w1GlLmPnzEtLVmn7Fu1 PfYCsVVxX94cNWLYo+8MGCICbbmDyaGmh/mjOI/Oif/2Bf1SAt/Gs46V000h/hL6DjBP BIww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=3zNNKxg8QJzXLBJzlzH9NxYdu3Y4JUt4/1AS4PtClLQ=; b=ZvxuJ8ylx5TWaxmc0Yi9mKyr01YERPm6XqJuhTK68zX2jEb0nCVlHISxTRJ9EXn0U7 cXJZN/WPPXlzvLcZqqSJrZ0W2A9sUBYhTXPqCalpyrrkbaTcCPI6BtxFOaPPGwHYiQLX e1BMS1drLqatHS3z/hJrq8XjuVWVIQETZDhJzw8pTO7O3DGjT1n1jkup5sBu6qTkvjwL 5yTRbFcRVRXUfINdXRrZEObtbB0j7yMf1Igo8N98M7uERWvioNgPVDHYF+cmhQr8vio9 8fB/KxT8d1dO4fetOymm9Rm7CCIpY0TyG+jke/Ezx617LaHM2vEx5J2mDZ8hRf+VZWcf g+AA== X-Gm-Message-State: AHYfb5h3uiwxInmLudT6UNdbbem4KHyf/c3jBEgBiBUnbs9LVyPATH3/ yxrhk8aAmQxfZGzGbuZ3dYV42mDp1Q== X-Received: by 10.55.126.195 with SMTP id z186mr23760504qkc.197.1503347288209; Mon, 21 Aug 2017 13:28:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.140.102.41 with HTTP; Mon, 21 Aug 2017 13:28:07 -0700 (PDT) In-Reply-To: <1503164276875-5979128.post@n3.nabble.com> References: <1503164276875-5979128.post@n3.nabble.com> From: Jeff Janes Date: Mon, 21 Aug 2017 13:28:07 -0700 Message-ID: Subject: Re: Performance Issue -- "Materialize" To: anand086 Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c05d03016803e055749516f" 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 --94eb2c05d03016803e055749516f Content-Type: text/plain; charset="UTF-8" On Sat, Aug 19, 2017 at 10:37 AM, anand086 wrote: Your email is very hard to read, the formatting and line wrapping is heavily mangled. You might want to attach the plans as files attachments instead of or in addition to putting the in the body. > -> 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)) > > It looks like the statistics for your table are desperately out of date, as a later query showed there are 762599 rows (unless login is null for all of them) but the above is estimating there is only one. When was the table last analyzed? Cheers, Jeff On Sat, Aug 19, 2017 at 10:37 AM, anand086 wrote: > 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. > --94eb2c05d03016803e055749516f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On S= at, Aug 19, 2017 at 10:37 AM, anand086 <anand086@gmail.com>= wrote:

Your email is very hard to read, the formatt= ing and line wrapping is heavily mangled.=C2=A0 You might want to attach th= e plans as files attachments instead of or in addition to putting the in th= e body.

=C2=A0
=C2=A0-> Index Only Scan using ui_nkey_test_tab= on test_tab test_tab_1 (cost=3D0.56..2.03 rows=3D1 width=3D8) |
     =
    Index Cond: ((import_num =3D '4520460'::numeric) AND (login IS =
NOT NULL))

It looks like the statist= ics for your table are desperately out of date, as a later query showed the= re are=C2=A0762599 rows (unless login is null for all of them) but the abov= e is estimating there is only one. =C2=A0 When was the table last analyzed?= =C2=A0

Cheers,

Jeff

On Sat, Aug 19, 2017 at 10:37 AM, anand086 <anand086@gmail.com>= wrote:
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 =3D '4520440' the execution plan sho= ws Nested Loop and is taking ~12secs.=20 With import_num =3D '4520460' execution plan showed using "Mat= erialize" 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.=20 SELECT count(*) FROM test_tab WHERE login IN (SELECT DISTINCT login FROM te= st_tab WHERE import_num =3D '4520440' AND=20 login IS NOT NULL EXCEPT SELECT DISTINCT login FROM test_tab WHERE import_n= um =3D '0' AND login IS NOT NULL)=20 AND import_num =3D '4520440'; +--------+ | count | +--------+ | 746982 | +--------+ (1 row) Time: 12054.274 ms 
+-----------------------------------------------------------=
-----------------------------------------------------------------=
-------------------------------+
|                                                                        QU=
ERY PLAN                                                                   =
      |
+----------------------------------------------------------------=
-----------------------------------------------------------------=
--------------------------+
| Aggregate  (cost=3D351405.08..351405.09 rows=3D1 width=3D8)              =
                                                                           =
            |
|   ->  Nested Loop  (cost=3D349846.23..350366.17 rows=3D415562 width=3D=
0)                                                                         =
               |
|         ->  HashAggregate  (cost=3D349845.67..349847.67 rows=3D200 wid=
th=3D96)                                                                   =
               |
|               Group Key: ("ANY_subquery".login)::text          =
                                                                           =
                |
|               ->  Subquery Scan on "ANY_subquery"  (cost=3D3=
40828.23..348557.47 rows=3D515282 width=3D96)                              =
                         |
|                     ->  SetOp Except  (cost=3D340828.23..343404.65 row=
s=3D515282 width=3D100)                                                    =
               |
|                           ->  Sort  (cost=3D340828.23..342116.44 rows=
=3D515283 width=3D100)                                                     =
                |
|                                 Sort Key: "*SELECT* 1".login   =
                                                                           =
                |
|                                 ->  Append  (cost=3D0.56..275836.74 ro=
ws=3D515283 width=3D100)                                                   =
               |
|                                       ->  Subquery Scan on "*SELE=
CT* 1"  (cost=3D0.56..275834.70 rows=3D515282 width=3D12)             =
                         |
|                                             ->  Unique  (cost=3D0.56..=
270681.88 rows=3D515282 width=3D8)                                         =
               |
|                                                   ->  Index Only Scan =
using ui_nkey_test_tab on test_tab test_tab_1  (cost=3D0.56..268604.07 rows=
=3D831125 width=3D8) |
|                                                         Index Cond: ((imp=
ort_num =3D '4520440'::numeric) AND (login IS NOT NULL))           =
                |
|                                       ->  Subquery Scan on "*SELE=
CT* 2"  (cost=3D0.56..2.04 rows=3D1 width=3D12)                       =
                         |
|                                             ->  Unique  (cost=3D0.56..=
2.03 rows=3D1 width=3D8)                                                   =
               |
|                                                   ->  Index Only Scan =
using ui_nkey_test_tab on test_tab test_tab_2  (cost=3D0.56..2.03 rows=3D1 =
width=3D8)           |
|                                                         Index Cond: ((imp=
ort_num =3D '0'::numeric) AND (login IS NOT NULL))                 =
                |
|         ->  Index Only Scan using ui_nkey_test_tab on test_tab  (cost=
=3D0.56..2.58 rows=3D1 width=3D8)                                          =
                    |
|               Index Cond: ((import_num =3D '4520440'::numeric) AN=
D (login =3D ("ANY_subquery".login)::text))                      =
                            |
+----------------------------------------------------------------=
-----------------------------------------------------------------=
--------------------------+
(19 rows)
SELECT count(*) FROM test_tab WHERE import_num =3D '4520460' and lo= gin IN (SELECT DISTINCT login FROM test_tab WHERE import_num =3D '45204= 60' AND login IS NOT NULL EXCEPT SELECT DISTINCT login FROM test_tab WH= ERE import_num =3D '0' AND login IS NOT NULL); The SQL was never completing and had the below SQL execution plan -- 
+-----------------------------------------------------------=
-----------------------------------------------------------------=
---------------+
|                                                                QUERY PLAN=
                                                                 |
+----------------------------------------------------------------=
-----------------------------------------------------------------=
----------+
| Aggregate  (cost=3D6.14..6.15 rows=3D1 width=3D8)                        =
                                                                       |
|   ->  Nested Loop Semi Join  (cost=3D1.12..6.13 rows=3D1 width=3D0)   =
                                                                          |
|         Join Filter: ((test_tab.login)::text =3D ("ANY_subquery"=
;.login)::text)                                                            =
    |
|         ->  Index Only Scan using ui_nkey_test_tab on test_tab  (cost=
=3D0.56..2.02 rows=3D1 width=3D8)                                          =
    |
|               Index Cond: (import_num =3D '4520460'::numeric)    =
                                                                           =
|
|         ->  Materialize  (cost=3D0.56..4.10 rows=3D1 width=3D96)      =
                                                                          |
|               ->  Subquery Scan on "ANY_subquery"  (cost=3D0=
.56..4.09 rows=3D1 width=3D96)                                             =
         |
|                     ->  HashSetOp Except  (cost=3D0.56..4.08 rows=3D1 =
width=3D100)                                                              |
|                           ->  Append  (cost=3D0.56..4.08 rows=3D2 widt=
h=3D100)                                                                  |
|                                 ->  Subquery Scan on "*SELECT* 1&=
quot;  (cost=3D0.56..2.04 rows=3D1 width=3D12)                             =
         |
|                                       ->  Unique  (cost=3D0.56..2.03 r=
ows=3D1 width=3D8)                                                        |
|                                             ->  Index Only Scan using =
ui_nkey_test_tab on test_tab test_tab_1  (cost=3D0.56..2.03 rows=3D1 width=
=3D8) |
|                                                   Index Cond: ((import_nu=
m =3D '4520460'::numeric) AND (login IS NOT NULL))                 =
|
|                                 ->  Subquery Scan on "*SELECT* 2&=
quot;  (cost=3D0.56..2.04 rows=3D1 width=3D12)                             =
         |
|                                       ->  Unique  (cost=3D0.56..2.03 r=
ows=3D1 width=3D8)                                                        |
|                                             ->  Index Only Scan using =
ui_nkey_test_tab on test_tab test_tab_2  (cost=3D0.56..2.03 rows=3D1 width=
=3D8) |
|                                                   Index Cond: ((import_nu=
m =3D '0'::numeric) AND (login IS NOT NULL))                       =
|
+----------------------------------------------------------------=
-----------------------------------------------------------------=
----------+
(17 rows)
############################################# # After I set enable_material to off; ############################################# SELECT count(*) FROM test_tab WHERE import_num =3D '4520460' and lo= gin IN (SELECT DISTINCT login FROM test_tab WHERE import_num =3D '45204= 60' AND login IS NOT NULL EXCEPT SELECT DISTINCT login FROM test_tab WH= ERE import_num =3D '0' AND login IS NOT NULL); +--------+ | count | +--------+ | 762599 | +--------+ (1 row) Time: 2116.889 ms 
+-----------------------------------------------------------=
-----------------------------------------------------------------=
---------------+
|                                                                QUERY PLAN=
                                                                 |
+----------------------------------------------------------------=
-----------------------------------------------------------------=
----------+
| Aggregate  (cost=3D6.13..6.14 rows=3D1 width=3D8)                        =
                                                                       |
|   ->  Hash Semi Join  (cost=3D4.67..6.13 rows=3D1 width=3D0)          =
                                                                          |
|         Hash Cond: ((test_tab.login)::text =3D ("ANY_subquery".=
login)::text)                                                              =
    |
|         ->  Index Only Scan using ui_nkey_test_tab on test_tab  (cost=
=3D0.56..2.02 rows=3D1 width=3D8)                                          =
    |
|               Index Cond: (import_num =3D '4520460'::numeric)    =
                                                                           =
|
|         ->  Hash  (cost=3D4.09..4.09 rows=3D1 width=3D96)             =
                                                                          |
|               ->  Subquery Scan on "ANY_subquery"  (cost=3D0=
.56..4.09 rows=3D1 width=3D96)                                             =
         |
|                     ->  HashSetOp Except  (cost=3D0.56..4.08 rows=3D1 =
width=3D100)                                                              |
|                           ->  Append  (cost=3D0.56..4.08 rows=3D2 widt=
h=3D100)                                                                  |
|                                 ->  Subquery Scan on "*SELECT* 1&=
quot;  (cost=3D0.56..2.04 rows=3D1 width=3D12)                             =
         |
|                                       ->  Unique  (cost=3D0.56..2.03 r=
ows=3D1 width=3D8)                                                        |
|                                             ->  Index Only Scan using =
ui_nkey_test_tab on test_tab test_tab_1  (cost=3D0.56..2.03 rows=3D1 width=
=3D8) |
|                                                   Index Cond: ((import_nu=
m =3D '4520460'::numeric) AND (login IS NOT NULL))                 =
|
|                                 ->  Subquery Scan on "*SELECT* 2&=
quot;  (cost=3D0.56..2.04 rows=3D1 width=3D12)                             =
         |
|                                       ->  Unique  (cost=3D0.56..2.03 r=
ows=3D1 width=3D8)                                                        |
|                                             ->  Index Only Scan using =
ui_nkey_test_tab on test_tab test_tab_2  (cost=3D0.56..2.03 rows=3D1 width=
=3D8) |
|                                                   Index Cond: ((import_nu=
m =3D '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= 9;t change. SELECT /*+HashJoin(a1 ANY_subquery)*/ count(*) FROM test_tab a1 WHERE impor= t_num =3D '4520440' and login IN (SELECT DISTINCT login FROM test_t= ab a2 WHERE import_num =3D '4520440' AND login IS NOT NULL EXCEPT= SELECT DISTINCT login FROM test_tab a3 WHERE import_num =3D '0' A= ND login IS NOT NULL); Regards, Anand =09 =09 =09
View this message in context: Performance= Issue -- "Materialize"
Sent from the PostgreSQL - performance mailing li= st archive at Nabble.com.

--94eb2c05d03016803e055749516f--