Getting an index scan to be a parallel index scan

[Alex Kaiser <alextkaiser@xxxxxxxxx>]

Hello,

I'm trying to get the following query to use a plan with parallelism, but I haven't been successful and would like some advice.

The schema and table that I'm using is this:

CREATE TABLE testing(
   id INT,
   info INT,
   data_one TEXT,
   data_two TEXT,
   primary key(id, info)
);

INSERT INTO testing(id, info, data_one, data_two)
SELECT idx, idx, md5(random()::text), md5(random()::text)
FROM generate_series(1,10000000) idx;

Then the query that I'm trying to run is this (I'll include the full query at the very end of the email because it is long:

select * from testing where id in (1608377,5449811, ... <1000 random ids> ,4654284,3558460);

Essentially I have a list of 1000 ids and I would like the rows for all of those ids.

This seems like it would be pretty easy to parallelize, if you have X threads then you would split the list of IDs into 1000/X sub lists and give one to each thread to go find the rows for ids in the given list.  Even when I use the following configs I don't get a query plan that actually uses any parallelism:

psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# show max_parallel_workers;
 max_parallel_workers
----------------------
 8
(1 row)

postgres=# set max_parallel_workers_per_gather = 8;
SET
postgres=# set parallel_setup_cost = 0;
SET
postgres=# set parallel_tuple_cost = 0;
SET
postgres=# set force_parallel_mode = on;
SET
postgres=# explain select * from testing where id in (1608377,5449811, ... <removed for brevity> ... ,4654284,3558460);

                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.43..6138.81 rows=1000 width=74)
   Workers Planned: 1
   Single Copy: true
   ->  Index Scan using testing_pkey on testing  (cost=0.43..6138.81 rows=1000 width=74)
         Index Cond: (id = ANY ('{1608377,5449811 ... <removed for brevity> ... 4654284,3558460}'::integer[]))
(5 rows)

postgres=# explain (analyze, buffers) select * from testing where id in (1608377,5449811, ... <removed for brevity> ... ,4654284,3558460);

                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.43..6138.81 rows=1000 width=74) (actual time=22.388..59.860 rows=1000 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   Buffers: shared hit=4003
   ->  Index Scan using testing_pkey on testing  (cost=0.43..6138.81 rows=1000 width=74) (actual time=0.443..43.660 rows=1000 loops=1)
         Index Cond: (id = ANY ('{1608377,5449811 ... <removed for brevity> ... 4654284,3558460}'::integer[]))
         Buffers: shared hit=4003
 Planning Time: 3.101 ms
 Execution Time: 60.211 ms
(10 rows)

postgres=# explain select * from testing;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather  (cost=0.00..153334.10 rows=10000050 width=74)
   Workers Planned: 5
   ->  Parallel Seq Scan on testing  (cost=0.00..153334.10 rows=2000010 width=74)
(3 rows)

That last query is just to show that I can get parallel plans, so they aren't completely turned off.

Is there a particular reason why this query can't be parallelized? Or is there some other way I could structure the query to get it to use parallelism?

I've tried this both on PG 15.1 (In docker, which seems to be on Debian 15.1) and PG 14.5 (on Centos 7) and got the same results

Thanks,

Alex Kaiser

Full query:

select * from testing where id in (1608377,5449811,5334677,5458230,2053195,3572313,1949724,3559988,5061560,8479775,6604845,1531946,8578236,1767138,1887562,9224796,801839,1389073,2070024,3378634,5935175,253322,6503217,492190,1646143,6073879,6344884,3120926,6077454,7988246,2359088,2758185,2277417,6144637,7869743,450645,2675170,307844,2752378,9765759,7604173,4702773,9447882,6403407,1020813,2421819,2246889,6118484,5675269,38400,989987,5226654,2910389,9741575,5909526,8752890,1429931,3598345,9541469,6728532,2454806,6470370,6338418,2525642,2286146,9319587,5821710,4138188,8677346,2188096,3242293,9711468,8308979,6505437,5620847,5870305,5177061,7519783,1441852,8264516,7637571,1994901,3979976,8828452,6327321,4377585,6055558,2620337,9944860,7822890,664424,8832299,8564521,4978015,5910646,8527205,3573524,996558,1270265,7774940,1747145,104339,6867262,9128122,1303267,3810412,2694329,7145818,6719318,3789062,9870348,986684,5603862,1698361,7732472,2816324,1337682,5012390,2309943,1691809,3480539,49005,6857269,9555513,2599309,2515895,4568931,641192,781186,4762944,13013,4987725,8990541,5654081,193138,4012985,2884209,5352762,9816619,1363209,3019900,8276055,2129378,1121730,7607112,5210575,3288097,1489630,1163497,7136711,9799048,375373,8046412,8724195,6005442,1290573,5721078,1214636,7569919,4654551,8618870,7709458,9852972,9717197,5704549,4163520,9558516,5443577,24670,332576,6877103,5932612,8298990,6309522,8041687,5977063,9500416,6432058,4937450,9923650,9117734,7237497,1798290,4124950,2185197,9948176,1094346,6746478,7304769,5568030,3796416,8891995,1053559,1821980,1185072,2349200,2219299,2969613,2472087,2450905,3121489,9638165,4790546,3720200,1311820,1296827,1138950,7784270,3824064,6915212,7383886,6855810,3491033,256301,9997854,2214084,9878366,5682387,5710729,8856125,9335563,3901871,2085478,5444947,4838361,9332499,1225090,3004836,9119361,5476573,9425201,9613762,9108411,4271769,6614784,3201217,8138778,1219241,4984103,6557882,2197275,3579784,5011159,7465713,760962,6200169,9687904,9045984,3827388,8586783,9949942,4918807,1309167,3406506,2453149,1061703,8054158,6778320,1431668,4145674,331232,6461486,6929178,5155683,5003625,9836477,6152755,2343676,2988832,6746977,2399198,8124075,8757743,4311457,5031384,8400655,1912444,6677221,5574997,1386860,1031616,3689530,4131063,5438418,944326,6217568,3395754,8937413,9269528,3699673,8552533,7437048,1024909,4343149,1434220,6593217,6142852,9110998,6207558,921357,2186560,6091282,1928657,4302412,6325582,1337393,6427695,3469914,4356086,8892231,8384082,1477346,3822408,5268755,4070623,3119427,3290973,4265753,817119,4504091,2401305,1925450,429200,1094436,9602884,5245982,1824411,432238,596900,8421662,8595645,2424955,1782602,1894324,427312,6048285,5864834,1348501,955343,6950739,8252446,3828615,9670815,3706371,3717929,7814353,1757583,8490290,4413043,2322689,4891500,5054674,4600353,1281555,3863893,1162106,2958640,6006984,4302963,1117738,8642737,5409180,9556862,841143,5045278,3748140,8894409,2506817,4273288,2633581,3119707,9952893,2750853,5474210,9249846,5639610,83338,9908504,8465361,2074546,7720208,5654917,7144433,8071670,3197270,1756937,9289716,6653496,4772491,7468146,1582580,2386228,5539203,6113389,5099513,9876191,9628095,3183250,6775459,7665608,6794804,8653394,4434664,8513441,5103707,8053446,6073965,2622184,4532773,6334178,5336613,3266043,8146834,7920939,1870993,8202151,309347,748345,6260993,1923670,377350,580449,3369377,2396135,661803,1731830,3729992,8501495,8212247,8515391,8718631,4730537,3122036,6299099,1923435,615308,1863293,6995898,5760160,2666671,9125446,3641934,6430855,489597,7183510,4181075,4815452,8985924,2344090,3416311,8092533,6306505,426770,5383875,4362857,3212107,5146937,2293104,6022662,7250711,4970184,4239079,1302390,8935997,1533922,1393172,5048505,4293843,1570827,9805238,1420916,293318,6162275,9177640,89886,1543620,3113059,1726434,3340563,1719843,9570231,2501492,2949354,2036931,8557586,1691786,6073593,3495457,416982,1373202,3858682,6765954,9991676,9190916,222078,8272108,9779778,6417060,2312865,3283936,3978241,7360141,3681005,3208006,7322741,1390421,3998891,5168998,7500754,4350760,724402,2576055,1365770,8550804,529521,2631191,333968,7544501,8130917,7154053,7885496,5928191,9471764,6755786,8272211,9432888,8840290,1228823,4915460,1801542,5852244,358500,7775207,7769606,5831998,4249440,1307330,4463268,766442,9131985,9780620,6820832,2601339,8317405,1679354,3419739,4819118,7326443,4510262,3015014,7192154,6284079,4207593,236283,4464714,7062157,7028124,9523370,7911438,2671064,1290471,9669065,5520807,5938961,4575373,9253011,7962875,8783002,6512827,8263442,6729440,3942648,856559,5202945,4928362,3282835,7887470,9975130,7615773,4030926,6176507,4497481,6033126,8621176,4504739,500044,2278118,9346590,6744253,7017476,4682119,3657000,5095471,174918,4551074,6687135,8296926,2622254,8752505,991505,8631264,8088985,5785268,1926815,2574783,6431649,8982423,8142710,824511,1875290,5054562,1437928,2075485,1949035,3757345,2528250,3307412,1779505,2096270,8807006,2685238,6559635,2027260,7526005,7616809,8731914,6472225,8846633,6619892,8782922,8631158,9069894,8547921,1293574,6272547,9859811,5509842,5516969,974646,3242662,2794043,5569866,2520950,5133422,9998183,5874455,4938074,5455495,9439197,7571865,2250902,1610594,9624168,1041235,2889120,6083148,3913825,4455711,405261,4303490,5588906,7985761,890989,7957500,13751,3022733,1380315,4471197,7128770,8145719,1786111,5209933,3062919,3753422,8123022,3230853,6095301,5093459,817527,2151655,9266058,9472989,9925539,1615290,8411945,95723,8567772,7870496,4487771,5124509,2453780,3946342,5859762,596133,8612152,3616196,2317853,6221780,5234609,1429272,5190050,1756430,4596457,5402935,5318101,3655060,1256006,4843877,3148982,5386241,4538154,7817465,3904008,2144081,5551025,2749786,4748282,6185119,2091766,2701159,5191374,1218345,3542677,4075715,6222181,4159050,6540911,3119637,6367663,2682116,9943058,1115652,5939513,6070897,3798441,408171,9264198,2727531,9187981,2837304,333856,5538241,8714618,6736394,313999,3015204,861772,7326900,961309,1722967,4652654,7328448,3670361,9081414,362096,9292335,1684179,1284622,3312337,1824664,7767797,5533043,5793208,1725413,6214729,9992784,8418622,6493664,8776426,1426161,1031983,3715268,6505887,8305875,7013880,7144356,9729782,436564,8608028,1010584,70717,2873837,7856269,2316654,7170184,6723773,7698527,9252650,5040660,7181806,5377517,5424349,3805788,4033651,4294239,5355707,6900075,5625668,3410262,4013203,481183,62184,8797500,970495,6625255,7254913,7662343,8987287,2610657,7294315,2724733,4649950,6509042,5306803,8816473,1173624,170600,1668636,3774797,3439784,7700452,9720665,7032018,8549446,9971526,9109279,9765304,5229101,5563539,6800753,5298323,5622436,5774485,6651444,1375607,7729739,7534311,7677402,9028109,9022462,9169017,6708403,8618359,7862319,4164876,5267625,5752478,3394094,2743359,7883411,3192807,6908084,2511599,9077668,2223928,9051932,5693857,4006603,364537,3964003,695520,2486464,2451789,524608,2937878,3432943,2987441,6847474,3349875,1847131,4010301,4885624,1193549,7902402,8756424,1890613,9598187,5647783,5375794,1835320,2363315,7101994,9646975,2582592,6539719,8914453,5196939,8161107,3899236,3050366,3449634,2616291,1669386,8632847,493803,8630172,7503179,6089968,8019732,9133326,1778968,6843066,6618579,2994096,8618807,9159460,303658,33203,6218402,4193805,338210,8828259,3770193,5646522,1959199,7231533,9087536,5524141,8049095,831964,2876993,119133,2008356,4142233,1763463,3510804,144448,8034613,6689542,6209014,5200398,7821812,7806829,3007319,371296,6503646,7713090,2140125,4895835,5475298,2381570,1813346,5893364,1287930,9494416,3264004,4379806,7156907,9199443,8766138,1521584,2700616,8516805,5936484,8717735,3035350,6076409,9913722,3638170,5015296,1824135,1546175,3240878,7591542,5853806,2678731,8194246,3846118,9304679,1055867,2073446,2082338,3043546,7440437,2437338,7237400,4411273,7560449,7042633,1236595,1900140,3129298,5580344,8006821,1554224,7064671,5722874,1873303,4876629,7638248,1434123,461213,2892216,9979823,1764459,1218933,1091006,8106607,4654284,3558460);