From: "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Hash join and picking which result set to build the hash table with.
Thread-Topic: Hash join and picking which result set to build the hash table
 with.
Thread-Index: AQHarIiWZkY8A9yvPke0vYbf4ZH5DQ==
Date: Wed, 22 May 2024 20:42:39 +0000
Message-ID:
 <BL0PR03MB4001869EF0A4859686FDCF16FAEB2@BL0PR03MB4001.namprd03.prod.outlook.com>
References: <202405221100.fy66dsew2f52@alvherre.pgsql>
 <6806.1716408332@sss.pgh.pa.us>
In-Reply-To: <6806.1716408332@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?Xdo5zyj8SY2T78ZZWeZTi70b0fVMdwAO256goufpG7VVOzFEj/o/6sStxY8g?=
 =?us-ascii?Q?XmHTM5llxIl9CYk89uZ7TFWtAkkdNuXCokSGCbvh45haQjTJbpf02psjjPPH?=
 =?us-ascii?Q?tcAk00nXb8poZ8b9hPPVoF0NdV+joQAPzP0A28oLT6If6Uh7bU8aL6jWWFh5?=
 =?us-ascii?Q?L1PeZCEg7wDoIccqN2POFjqzdUDzboDbG64AFsuvQpeEVh80IM3N5UFFf7wA?=
 =?us-ascii?Q?CXrioYP/82Px8kjwD6WkVGN0f+ssexWWPBUzUjTZmLD1IrI1M4Lazjc7B6SY?=
 =?us-ascii?Q?OIdonsWYLPi3X6S3bK/h7b7kZbj/zGRC6WnicJ2WAJZttr7Q1r8HsHhzaR59?=
 =?us-ascii?Q?u5EkTxpRKTmEdXg39dxsrfHXkZtNEOgANPyiXN09B8/UjTQxFgYNsKl8yH/6?=
 =?us-ascii?Q?Uglo3J18fVHZA7v+Bl0etxNCzihHlXu1HLEAlJPBbuggh7EFhb/PCjWwGMaj?=
 =?us-ascii?Q?4yYiNqYCmwYQHWZJfZ5g51UZODupSjeE9ucPV7XqUTRKe4oZMfI0lbGdiSY1?=
 =?us-ascii?Q?m7JIJK6r/sc0baMgybj0tmpsCLkFrq9C+F0N77B5CGJTdPvTRaYDJ4CobxyP?=
 =?us-ascii?Q?wqrrvkb9JEMg2JlRCYWLryt4dOIOyqszQEzoezZuuiQLJz5si1RK5AbWXnNT?=
 =?us-ascii?Q?/hMstsiM320yM4CJGcZx6nGvFpxaI0OppIR3xmCUOuIkDYHcotQFIE/iXTuM?=
 =?us-ascii?Q?3gt9HQwwZydLflICGwhcetXjYE42yo69iF3pZxUD/1O9Gzt89kmgwQ4vUKeK?=
 =?us-ascii?Q?YHMLUMQglr5EPJa7eZXRRUeau8r4ncIxsIC1R1JV4iqaOrgAPbQreQ/1CbY/?=
 =?us-ascii?Q?CJNKByEyaU0FEYJTb8UhhjnzakOpd67bmdSlyLcyN457WKvptYCKEkRKIVob?=
 =?us-ascii?Q?BOxX4O0QB5Vz/NprRCSh4+fpCbCvHgsjaF1FtP4xQd4rEj9Y3qfWrU793rHK?=
 =?us-ascii?Q?S9jV8buL119jc9hpScHcJoBDaxkWlsIV1npRivIbpOYNHdAJ1eSguetswkOK?=
 =?us-ascii?Q?aWVYDtA48P/cC/2Lbir5JOtz0Kga9WNIj/Dvi5c8kOY3/pdIFQsMiaHIIUks?=
 =?us-ascii?Q?bbXuT6nFZc1DBcccLPWMHHUkxA3gWXQYVeJANi/4kmLH3FkM5rN3Y1mYs7Sx?=
 =?us-ascii?Q?MpEpRPer0rjDO7LLjTjci9uvtoueqdoG8J1y6NZ/PHa3PkfrVrwqIdIMckuJ?=
 =?us-ascii?Q?k0JsMt58CHapfejjNd3RzjX/A8u4FFmwSeowHZ/bSQVqBmw+hy0OozshbSsW?=
 =?us-ascii?Q?ttyY7qXZWcWppC0CgEPv1IoteN/680OeZ4FjZABAAaLVvYVIj0wWKCaxLOex?=
 =?us-ascii?Q?8iOOBLAzxZByfHfZho6rZRYU2Lnn+JkzYlkSuP7kevt9HDrqAzGvINGh27TG?=
 =?us-ascii?Q?EM5kO1vrlzHZu/4pXf1LtKqvqeuMTWLthyMBAfles8rZfvtAlHRidNtpXe/N?=
 =?us-ascii?Q?e5eiSNObXpof2DTKCHemyjhSaHq7NgqPnsIDgy49rgYzwXq+9/0cPYEmzcH5?=
 =?us-ascii?Q?Hk/2y73XNnnPkT7U8ZSPsykIfozRKhFAbuKpDwqORa32GEMdVXsSj62N9aG8?=
 =?us-ascii?Q?1XEfQhkSPcjXDbvEIsfP66AAGX8XBgdFfCJCIuoA2WWVsKB8jKS40nVika+l?=
 =?us-ascii?Q?MQ=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: thomsonreuters.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BL0PR03MB4001.namprd03.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: d5607a9a-11b3-4ddb-1498-08dc7a9fb881
X-MS-Exchange-CrossTenant-originalarrivaltime: 22 May 2024 20:42:39.3593
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 62ccb864-6a1a-4b5d-8e1c-397dec1a8258
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: z0wtO7rlwAxz1FpNggTc6UsBId7dTNtoZlSonwLjy7XxhJDwYT7K0x4L/YNr0+64JOKMMnfwoWH0A316VTy/RLkS4bw8hoGl6vUPYYtr1IOqj7mP1GqPiRMkQowJseE+
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM6PR03MB5354
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/BL0PR03MB4001869EF0A4859686FDCF16FAEB2%40BL0PR03MB4001.namprd03.prod.outlook.com>
Precedence: bulk

The query and execution plan are shown below.  My question is related to th=
e result set the optimizer is choosing to build the hash table from.  My un=
derstanding is for a hash join you want to build the hash table out of the =
smaller result set.  If you look at the execution plan below you can see th=
e optimizer estimates 1000 rows from the seq scan of table collection and 1=
20,000 rows from the seq scan of table docloc_test but is building the hash=
 buckets from those 120000 rows rather than from the 1000 rows.  In our cas=
e under certain volume that causes that to spill to temp and under high loa=
d it hangs up the Aurora Postgres database.  But if it were to build the ha=
sh table out of those 1000 rows it would fit in work_mem so no problems.  W=
hy is it picking the larger result set?

Another funky thing here-  here are the table definitions:

pgcci01ap=3D> \d CCI.COLLECTION

        Column        |              Type              | Collation | Nullab=
le |                Default
----------------------+--------------------------------+-----------+-------=
---+----------------------------------------
 collection_name      | character varying(40)          |           | not nu=
ll |
 l_stage              | numeric(11,0)                  |           |       =
   |
 p_stage              | numeric(11,0)                  |           |       =
   |
 t_stage              | numeric(11,0)                  |           |       =
   |
 last_upd_datetime    | timestamp(6) without time zone |           | not nu=
ll |
 last_upd_inits       | character varying(30)          |           | not nu=
ll |
 owner_name           | character varying(30)          |           |       =
   |
 password             | character varying(30)          |           |       =
   |
 email_address        | character varying(2000)        |           |       =
   |
 available_flag       | character(1)                   |           |       =
   |
 collection_id        | numeric(11,0)                  |           | not nu=
ll |
 collection_type      | character varying(20)          |           |       =
   |
 retrieval_password   | character varying(40)          |           |       =
   |
 partner_coll_name    | character varying(40)          |           |       =
   |
 relation2partner     | character varying(20)          |           |       =
   |
 reload_flag          | character(1)                   |           |       =
   | 'N'::bpchar
 partner_id           | character varying(40)          |           |       =
   |
 content_timezone     | character varying(40)          |           | not nu=
ll | 'America/Chicago'::character varying
 token_type           | character varying(30)          |           |       =
   |
 cc_collection_dest   | character varying(40)          |           |       =
   |
 auto_reclaim_enabled | character(1)                   |           | not nu=
ll | 'N'::bpchar
 collection_family    | character varying(40)          |           | not nu=
ll | '<COLLECTION NAME>'::character varying
 access_password      | character(40)                  |           |       =
   |
 mic_group            | character varying(40)          |           |       =
   |
 mic_type             | character varying(10)          |           |       =
   |
 retrieval_source     | character varying(40)          |           | not nu=
ll | 'DOC1'::character varying
Indexes:
    "xpkcollection" PRIMARY KEY, btree (collection_name)
    "xak1collection" UNIQUE CONSTRAINT, btree (collection_id)
    "xie1collection" btree (relation2partner)
    "xie2collection" btree (collection_family, collection_name)
    "xie3collection" btree (mic_group)
Referenced by:
    TABLE "cci.index_update_proc" CONSTRAINT "rfk12_index_update_proc" FORE=
IGN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.authority_update_proc" CONSTRAINT "rfk1_authority_update_pro=
c" FOREIGN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.collection_event" CONSTRAINT "rfk1_collection_event" FOREIGN=
 KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.collection_pit" CONSTRAINT "rfk1_collection_pit" FOREIGN KEY=
 (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.collection_stage" CONSTRAINT "rfk1_collection_stage" FOREIGN=
 KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.csloc_update_proc" CONSTRAINT "rfk1_csloc_update_proc" FOREI=
GN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.docloc_update_proc" CONSTRAINT "rfk1_docloc_update_proc" FOR=
EIGN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.index_set_mrg" CONSTRAINT "rfk1_index_set_mrg" FOREIGN KEY (=
collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.index_set_stats" CONSTRAINT "rfk1_index_set_stats" FOREIGN K=
EY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.index_system_attr" CONSTRAINT "rfk1_index_system_attr" FOREI=
GN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.load_update_proc" CONSTRAINT "rfk1_load_update_proc" FOREIGN=
 KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.metadoc_update_proc" CONSTRAINT "rfk1_metadoc_update_proc" F=
OREIGN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.mm_update_process" CONSTRAINT "rfk1_mm_update_process" FOREI=
GN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.reclaim" CONSTRAINT "rfk1_reclaim" FOREIGN KEY (collection_n=
ame) REFERENCES cci.collection(collection_name)
    TABLE "cci.rel_grp_upd_proc" CONSTRAINT "rfk1_rel_grp_upd_proc" FOREIGN=
 KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.toc_update_process" CONSTRAINT "rfk1_toc_update_process" FOR=
EIGN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.view_definition" CONSTRAINT "rfk1_view_definition" FOREIGN K=
EY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.dcsloc_collection_stats" CONSTRAINT "rfk1dcsloc_collection_s=
tats" FOREIGN KEY (collection_name) REFERENCES cci.collection(collection_na=
me)
    TABLE "cci.doc_data_domain" CONSTRAINT "rfk1doc_data_domain" FOREIGN KE=
Y (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.doc_update_process" CONSTRAINT "rfk2_doc_update_process" FOR=
EIGN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.dcsloc_partition_map" CONSTRAINT "rfk2dcsloc_partition_map" =
FOREIGN KEY (collection_name) REFERENCES cci.collection(collection_name)
    TABLE "cci.meta_update_proc" CONSTRAINT "rfk3_metadoc_update_proc" FORE=
IGN KEY (collection_name) REFERENCES cci.collection(collection_name)

pgcci01ap=3D> \d docloc_test

      Column       |              Type              | Collation | Nullable =
|   Default
-------------------+--------------------------------+-----------+----------=
+-------------
 collection_name   | character varying(40)          |           | not null =
|
 stage_id          | numeric(11,0)                  |           | not null =
|
 begin_stage_id    | numeric(11,0)                  |           |          =
|
 last_upd_datetime | timestamp(6) without time zone |           |          =
|
 last_upd_inits    | character varying(30)          |           |          =
|
 docloc_check_type | character(1)                   |           | not null =
| 'S'::bpchar
 replicate_done    | character(1)                   |           | not null =
| 'N'::bpchar
 docloc_id         | numeric(11,0)                  |           |          =
|
Indexes:
    "xpkdocloc" PRIMARY KEY, btree (collection_name, stage_id)
Foreign-key constraints:
    "rfk1_docloc" FOREIGN KEY (collection_name, stage_id) REFERENCES cci.co=
llection_stage(collection_name, stage_id)

pgcci01ap=3D> \d CCI.COLLECTION_PIT

      Column       |              Type              | Collation | Nullable =
| Default
-------------------+--------------------------------+-----------+----------=
+---------
 collection_name   | character varying(40)          |           | not null =
|
 pit_id            | numeric(11,0)                  |           | not null =
|
 stage_code        | character(1)                   |           | not null =
|
 stage_id          | numeric(11,0)                  |           | not null =
|
 last_upd_datetime | timestamp(6) without time zone |           | not null =
|
 last_upd_inits    | character varying(30)          |           | not null =
|
Indexes:
    "xpkcollection_pit" PRIMARY KEY, btree (collection_name, stage_code, pi=
t_id, stage_id)
    "xak1collection_pit" UNIQUE, btree (collection_name, stage_code, pit_id=
, stage_id, last_upd_datetime DESC, last_upd_inits DESC)
    "xak2collection_pit" btree (collection_name, stage_code, stage_id)
Foreign-key constraints:
    "rfk1_collection_pit" FOREIGN KEY (collection_name) REFERENCES cci.coll=
ection(collection_name)
Triggers:
    td_collection_pit AFTER DELETE ON cci.collection_pit FOR EACH ROW EXECU=
TE FUNCTION cci."td_collection_pit$collection_pit"()


When running some tests I forgot to create the PK on table docloc_test.  Wh=
en the PK was not on the table the optimizer decided to create the hash tab=
le off the 1000 rows from collection.  But as soon as I put the PK on that =
table it then decides to use docloc_test to build the hash table.  I can un=
derstand how the PK missing or not could impact the execution plan (full sc=
anning the table and hash join vs nested looping to it) but in both cases d=
ocloc_test and collection were hash joined and the difference was which res=
ult set was used to build the hash table.  I cannot come up with any theory=
 on why the existence of non-existence of this PK would impact which result=
 set the hash table was built from.  In both cases the row estimates from c=
ollection and docloc_test were exactly the same in both plans (1000 from co=
llection, 120000 fro mdocloc_test).


explain (analyze, buffers)
SELECT  DOCLOC.BEGIN_STAGE_ID, DOCLOC.COLLECTION_NAME, DOCLOC.DOCLOC_CHECK_=
TYPE, DOCLOC.DOCLOC_ID, DOCLOC.LAST_UPD_DATETIME, DOCLOC.LAST_UPD_INITS,
DOCLOC.REPLICATE_DONE, DOCLOC.STAGE_ID, COLLECTION.ACCESS_PASSWORD, COLLECT=
ION.AUTO_RECLAIM_ENABLED, COLLECTION.AVAILABLE_FLAG, COLLECTION.CC_COLLECTI=
ON_DEST,
COLLECTION.COLLECTION_FAMILY, COLLECTION.COLLECTION_ID, COLLECTION.COLLECTI=
ON_NAME, COLLECTION.COLLECTION_TYPE, COLLECTION.CONTENT_TIMEZONE, COLLECTIO=
N.EMAIL_ADDRESS,
COLLECTION.LAST_UPD_DATETIME, COLLECTION.LAST_UPD_INITS, COLLECTION.L_STAGE=
, COLLECTION.MIC_GROUP, COLLECTION.MIC_TYPE, COLLECTION.OWNER_NAME,
COLLECTION.PARTNER_COLL_NAME, COLLECTION.PARTNER_ID, COLLECTION.PASSWORD, C=
OLLECTION.P_STAGE, COLLECTION.RELATION2PARTNER, COLLECTION.RELOAD_FLAG,
COLLECTION.RETRIEVAL_PASSWORD, COLLECTION.RETRIEVAL_SOURCE, COLLECTION.TOKE=
N_TYPE, COLLECTION.T_STAGE
FROM DOCLOC_test docloc, CCI.COLLECTION
WHERE  COLLECTION.COLLECTION_ID IN (2188,15418,1427,1425,4584,1424,1429,142=
6,1638,1639,1640,1641,1642,1684,1685,1686,1428,13727,7421,7422,2189,5145,25=
99,2992,9245,9246,9247,9248,9249,9250,9251,9252,5121,19614,-6322,928,1544,-=
14765,929,-6323,930,931,932,1542,3890,6594,8986,-6279,19631,1466,19639,1467=
,1468,1475,1540,3892,-6348,
-6358,4753,1978,1977,1966,2687,2686,2674,-6350,3060,3600,426,3654,2718,1824=
,445,496,556,432,421,423,471,16152,16362,16154,16155,16156,16157,16799,507,=
573,574,404,524,743,15388,694,3537,1408,10097,9376,9375,10098,9377,8559,855=
7,1599,1963,1976,8551,8555,8848,8552,8554,8734,8735,10306,8739,8736,
8737,9253,9254,9255,9256,9257,9258,9063,9194,9065,9066,9067,9068,9069,9070,=
9071,9072,9177,9178,9179,9180,9181,9182,9183,9184,9093,4222,3048,3116,6971,=
6972,6973,6974,2675,8387,2360,2672,2673,2681,2682,2683,1528,2671,2676,7438,=
2678,2677,2679,2685,2684,2361,2680,2688,3533,10605,349,385,396,631,
2541,1396,1979,3022,1980,138,12,6835,10609,7164,5307,6863,6864,20444,695,18=
15,514,600,601,602,603,604,605,3069,2321,790,446,490,6520,2006,3157,3158,39=
17,419,461,437,555,748,464,427,3539,16158,8738,8733,18,3628,3626,3627,4805,=
4806,5054,3630,3629,3631,4807,3634,4714,240,13710,7227,8909,8910,
8911,10730,8912,8951,2019,7315,7078,7930,7928,7929,693,7261,7262,239,242,89=
52,7228,330,328,7079,4812,4813,6975,14410,6976,6977,6978,6979,6980,6981,698=
2,6983,6984,6985,6986,6987,6988,6989,6990,6991,6992,6993,6994,6995,6996,699=
7,6998,6999,7000,7001,7002,7003,7004,7828,7829,4479,6544,10706,
10707,6545,4010,4011,6598,6847,6850,6848,6851,2892,6484,6486,6485,4009,4012=
,3040,3039,10746,8233,4476,4477,7255,6889,5139,7464,7465,7466,8388,7469,161=
59,16160,752,3540,831,3147,3148,3149,3137,5481,9161,1825,2028,9230,460,642,=
1827,8950,751,2833,3162,1990,1523,755,2013,754,1829,467,753,474,
2793,703,1632,412,4814,4815,4816,540,541,1406,2020,6795,7220,9554,9555,9556=
,2999,17387,17388,17443,17444,10682,10681,10683,10686,10684,10687,10685,106=
88,20,2510,155,21,22,26,27,28,29,30,31,32,33,7005,7006,7007,7008,7009,7010,=
7011,7125,7013,7014,7015,7016,7017,7018,7019,7020,6970,1512,2439,
17844,2959,16211,2506,1450,3211,1598,2037,3587,8563,8564,8565,8566,7468,839=
3,7467,8276,8392,3575,3576,9576,10516,3557,6900,1819,1809,1808,1805,1804,18=
07,1806,8558,10610,16806,16807,16808,16809,16810,2523,16811,16812,2522,3907=
,15,17,3817,3798,3810,3794,3821,3808,837,758,15389,757,814,548,
1419,3163,2957,15498,15499,2539,10295,16381,2032,1315,716,1421,16800,762,18=
33,8612,2039,17684,17685,15500,1834,1566,425,468,34,35,36,37,38,39,40,41,42=
,43,44,45,46,47,48,49,19880,379,19881,19884,19885,383,19886,19888,203,50,51=
,52,53,54,55,8567,8568,8569,8570,8571,8572,8573,8574,8575,8576,
8577,8578,8579,8580,8581,8582,8583,8584,8585,8586,8587,8588,8589,8590,8591,=
8592,8593,8594,8595,8596,8953,3811,7196,3784,3785,7216,7211,3800,9175,4701,=
3781,7215,7210,3813,3828,3789,7217,7212,3818,3792,3832,3829,3804,3807,7218,=
7213,5102,3809,7214,5095,3830,10034,4988,2924,2925,4553,4554,7221,
7222,7869,8271,8275,8410,16161,16162,8411,9428,414,476,935,20533,20534,2066=
8,20679,20680,710,1401,433,2040,1838,3164,1853,8835,20681,2844,3165,16163,1=
6164,771,56,2499,2501,235,57,58,59,60,61,62,63,141,202,2502,190,191,192,189=
,140,534,535,2508,139,533,536,199,200,2500,304,305,306,307,8597,
8598,8599,8600,8601,8602,8785,3586,3585,1612,1613,1614,1616,1617,1647,1648,=
1649,1652,1820,16375,13728,14411,2046,2047,2186,2263,2318,2432,2448,2442,24=
87,2525,2527,2535,2537,2544,22013,22014,22015,22016,769,2794,2200,484,1949,=
7360,8896,1812,3139,7115,2660,6912,659,3071,8647,711,1962,4002,16801,
705,6911,774,16166,16167,4495,778,7205,706,672,3054,449,2958,308,309,310,31=
1,312,313,314,315,316,317,318,319,15400,15401,15402,15403,16685,15405,15406=
,15407,15408,15409,15410,15411,15412,15413,15414,16210,233,2507,2691,2692,2=
695,2801,2805,2806,2818,2905,2845,2987,2995,3026,3049,3050,3052,3066,
3091,3095,3186,3526,3649,3888,3884,3886,4288,4378,4517,4916,4917,5003,8561,=
21903,21911,21927,14644,1411,707,463,15501,1950,405,671,3055,15394,1170,116=
8,3152,9163,16382,16217,1844,417,2444,1841,2035,781,16363,2721,684,784,1057=
1,782,462,646,3056,443,479,2007,3167,3168,118,2509,17019,17020,17021,
17022,17023,17445,17446,14395,17447,17448,17449,1447,247,3908,2187,1646,251=
1,3974,3975,3982,3983,3984,3985,3986,3987,3988,3989,3990,3991,3992,21880,21=
881,21885,21886,21887,21889,21890,21891,21939,21942,13714,21943,21945,21946=
,21962,4556,16218,4557,6539,4555,7452,1410,420,454,16169,16170,16171,
16172,16173,1872,16174,785,1847,1454,16175,-703,406,525,526,788,470,742,194=
7,7195,793,3153,503,563,564,2834,444,3993,3994,3995,3996,3997,3998,3999,429=
7,4298,4299,4300,4301,4302,4303,4434,4435,4436,4437,4438,4439,4440,4441,444=
2,4443,4444,4445,4446,4447,4448,4449,4450,4451,4452,4453,21940,21941,
21975,21977,21978,21979,21981,18232,8054,8055,8060,8058,8059,8056,8057,1823=
3,18234,18235,18236,18237,8042,8043,8044,8045,8046,8047,8048,8049,8050,8051=
,8052,21985,21986,21969,21982,21983,4481,2720,741,16176,15395,3602,409,1811=
,16365,599,3072,4044,799,1869,3141,403,480)
AND COLLECTION.COLLECTION_NAME=3DDOCLOC.COLLECTION_NAME
AND DOCLOC.STAGE_ID=3D
    (SELECT MAX (STAGE_ID)
       FROM CCI.COLLECTION_PIT
      WHERE COLLECTION_PIT.COLLECTION_NAME=3DCOLLECTION.COLLECTION_NAME
        AND COLLECTION_PIT.PIT_ID<=3D2147483647
        AND COLLECTION_PIT.STAGE_CODE=3D'F');

The plan below was executed with work_mem at 12 MB so it's not spilling to =
temp but this is only for my session.  At 4MB it will spill to temp.

Hash Join  (cost=3D4302.50..5658.10 rows=3D1 width=3D529) (actual time=3D67=
.959..81.185 rows=3D496 loops=3D1)
   Hash Cond: (((collection.collection_name)::text =3D (docloc.collection_n=
ame)::text) AND ((SubPlan 2) =3D docloc.stage_id))
   Buffers: shared hit=3D7735
   ->  Seq Scan on collection  (cost=3D2.50..1040.08 rows=3D1000 width=3D47=
8) (actual time=3D0.058..4.327 rows=3D1000 loops=3D1)
         Filter: (collection_id =3D ANY ('{2188,15418,1427,1425,4584,1424,1=
429,1426,1638,1639,1640,1641,1642,1684,1685,1686,1428,13727,7421,7422,2189,=
5145,2599,2992,9245,9246,9247,9248,9249,9250,9251,9252,5
121,19614,-6322,928,1544,-14765,929,-6323,930,931,932,1542,3890,6594,8986,-=
6279,19631,1466,19639,1467,1468,1475,1540,3892,-6348,-6358,4753,1978,1977,1=
966,2687,2686,2674,-6350,3060,3600,426,3654,2718,1824,
445,496,556,432,421,423,471,16152,16362,16154,16155,16156,16157,16799,507,5=
73,574,404,524,743,15388,694,3537,1408,10097,9376,9375,10098,9377,8559,8557=
,1599,1963,1976,8551,8555,8848,8552,8554,8734,8735,103
06,8739,8736,8737,9253,9254,9255,9256,9257,9258,9063,9194,9065,9066,9067,90=
68,9069,9070,9071,9072,9177,9178,9179,9180,9181,9182,9183,9184,9093,4222,30=
48,3116,6971,6972,6973,6974,2675,8387,2360,2672,2673,2
681,2682,2683,1528,2671,2676,7438,2678,2677,2679,2685,2684,2361,2680,2688,3=
533,10605,349,385,396,631,2541,1396,1979,3022,1980,138,12,6835,10609,7164,5=
307,6863,6864,20444,695,1815,514,600,601,602,603,604,6
05,3069,2321,790,446,490,6520,2006,3157,3158,3917,419,461,437,555,748,464,4=
27,3539,16158,8738,8733,18,3628,3626,3627,4805,4806,5054,3630,3629,3631,480=
7,3634,4714,240,13710,7227,8909,8910,8911,10730,8912,8
951,2019,7315,7078,7930,7928,7929,693,7261,7262,239,242,8952,7228,330,328,7=
079,4812,4813,6975,14410,6976,6977,6978,6979,6980,6981,6982,6983,6984,6985,=
6986,6987,6988,6989,6990,6991,6992,6993,6994,6995,6996
,6997,6998,6999,7000,7001,7002,7003,7004,7828,7829,4479,6544,10706,10707,65=
45,4010,4011,6598,6847,6850,6848,6851,2892,6484,6486,6485,4009,4012,3040,30=
39,10746,8233,4476,4477,7255,6889,5139,7464,7465,7466,
8388,7469,16159,16160,752,3540,831,3147,3148,3149,3137,5481,9161,1825,2028,=
9230,460,642,1827,8950,751,2833,3162,1990,1523,755,2013,754,1829,467,753,47=
4,2793,703,1632,412,4814,4815,4816,540,541,1406,2020,6
795,7220,9554,9555,9556,2999,17387,17388,17443,17444,10682,10681,10683,1068=
6,10684,10687,10685,10688,20,2510,155,21,22,26,27,28,29,30,31,32,33,7005,70=
06,7007,7008,7009,7010,7011,7125,7013,7014,7015,7016,7
017,7018,7019,7020,6970,1512,2439,17844,2959,16211,2506,1450,3211,1598,2037=
,3587,8563,8564,8565,8566,7468,8393,7467,8276,8392,3575,3576,9576,10516,355=
7,6900,1819,1809,1808,1805,1804,1807,1806,8558,10610,1
6806,16807,16808,16809,16810,2523,16811,16812,2522,3907,15,17,3817,3798,381=
0,3794,3821,3808,837,758,15389,757,814,548,1419,3163,2957,15498,15499,2539,=
10295,16381,2032,1315,716,1421,16800,762,1833,8612,203
9,17684,17685,15500,1834,1566,425,468,34,35,36,37,38,39,40,41,42,43,44,45,4=
6,47,48,49,19880,379,19881,19884,19885,383,19886,19888,203,50,51,52,53,54,5=
5,8567,8568,8569,8570,8571,8572,8573,8574,8575,8576,85
77,8578,8579,8580,8581,8582,8583,8584,8585,8586,8587,8588,8589,8590,8591,85=
92,8593,8594,8595,8596,8953,3811,7196,3784,3785,7216,7211,3800,9175,4701,37=
81,7215,7210,3813,3828,3789,7217,7212,3818,3792,3832,3
829,3804,3807,7218,7213,5102,3809,7214,5095,3830,10034,4988,2924,2925,4553,=
4554,7221,7222,7869,8271,8275,8410,16161,16162,8411,9428,414,476,935,20533,=
20534,20668,20679,20680,710,1401,433,2040,1838,3164,18
53,8835,20681,2844,3165,16163,16164,771,56,2499,2501,235,57,58,59,60,61,62,=
63,141,202,2502,190,191,192,189,140,534,535,2508,139,533,536,199,200,2500,3=
04,305,306,307,8597,8598,8599,8600,8601,8602,8785,3586
,3585,1612,1613,1614,1616,1617,1647,1648,1649,1652,1820,16375,13728,14411,2=
046,2047,2186,2263,2318,2432,2448,2442,2487,2525,2527,2535,2537,2544,22013,=
22014,22015,22016,769,2794,2200,484,1949,7360,8896,181
2,3139,7115,2660,6912,659,3071,8647,711,1962,4002,16801,705,6911,774,16166,=
16167,4495,778,7205,706,672,3054,449,2958,308,309,310,311,312,313,314,315,3=
16,317,318,319,15400,15401,15402,15403,16685,15405,154
06,15407,15408,15409,15410,15411,15412,15413,15414,16210,233,2507,2691,2692=
,2695,2801,2805,2806,2818,2905,2845,2987,2995,3026,3049,3050,3052,3066,3091=
,3095,3186,3526,3649,3888,3884,3886,4288,4378,4517,491
6,4917,5003,8561,21903,21911,21927,14644,1411,707,463,15501,1950,405,671,30=
55,15394,1170,1168,3152,9163,16382,16217,1844,417,2444,1841,2035,781,16363,=
2721,684,784,10571,782,462,646,3056,443,479,2007,3167,
3168,118,2509,17019,17020,17021,17022,17023,17445,17446,14395,17447,17448,1=
7449,1447,247,3908,2187,1646,2511,3974,3975,3982,3983,3984,3985,3986,3987,3=
988,3989,3990,3991,3992,21880,21881,21885,21886,21887,
21889,21890,21891,21939,21942,13714,21943,21945,21946,21962,4556,16218,4557=
,6539,4555,7452,1410,420,454,16169,16170,16171,16172,16173,1872,16174,785,1=
847,1454,16175,-703,406,525,526,788,470,742,1947,7195,
793,3153,503,563,564,2834,444,3993,3994,3995,3996,3997,3998,3999,4297,4298,=
4299,4300,4301,4302,4303,4434,4435,4436,4437,4438,4439,4440,4441,4442,4443,=
4444,4445,4446,4447,4448,4449,4450,4451,4452,4453,2194
0,21941,21975,21977,21978,21979,21981,18232,8054,8055,8060,8058,8059,8056,8=
057,18233,18234,18235,18236,18237,8042,8043,8044,8045,8046,8047,8048,8049,8=
050,8051,8052,21985,21986,21969,21982,21983,4481,2720,
741,16176,15395,3602,409,1811,16365,599,3072,4044,799,1869,3141,403,480}'::=
numeric[]))
         Rows Removed by Filter: 15110
         Buffers: shared hit=3D598
   ->  Hash  (cost=3D2500.00..2500.00 rows=3D120000 width=3D51) (actual tim=
e=3D67.466..67.467 rows=3D120000 loops=3D1)
         Buckets: 131072  Batches: 1  Memory Usage: 11485kB
         Buffers: shared hit=3D1300
         ->  Seq Scan on docloc_test docloc  (cost=3D0.00..2500.00 rows=3D1=
20000 width=3D51) (actual time=3D0.004..22.313 rows=3D120000 loops=3D1)
               Buffers: shared hit=3D1300
   SubPlan 2
     ->  Result  (cost=3D6.25..6.26 rows=3D1 width=3D32) (actual time=3D0.0=
05..0.005 rows=3D1 loops=3D1496)
           Buffers: shared hit=3D5837
           InitPlan 1 (returns $1)
             ->  Limit  (cost=3D0.42..6.25 rows=3D1 width=3D6) (actual time=
=3D0.005..0.005 rows=3D1 loops=3D1496)
                   Buffers: shared hit=3D5837
                   ->  Index Scan Backward using xak2collection_pit on coll=
ection_pit  (cost=3D0.42..64.50 rows=3D11 width=3D6) (actual time=3D0.004..=
0.004 rows=3D1 loops=3D1496)
                         Index Cond: (((collection_name)::text =3D (collect=
ion.collection_name)::text) AND (stage_code =3D 'F'::bpchar) AND (stage_id =
IS NOT NULL))
                         Filter: (pit_id <=3D '2147483647'::numeric)
                         Buffers: shared hit=3D5837
 Planning:
   Buffers: shared hit=3D78
 Planning Time: 2.287 ms
 Execution Time: 83.361 ms

Thanks in advance.
This e-mail is for the sole use of the intended recipient and contains info=
rmation that may be privileged and/or confidential. If you are not an inten=
ded recipient, please notify the sender by return e-mail and delete this e-=
mail and any attachments. Certain required legal entity disclosures can be =
accessed on our website: https://www.thomsonreuters.com/en/resources/disclo=
sures.html