Обсуждение: Getting an index scan to be a parallel index scan
Hello,
INSERT INTO testing(id, info, data_one, data_two)
SELECT idx, idx, md5(random()::text), md5(random()::text)
FROM generate_series(1,10000000) idx;
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)
);
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)
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);
Em qua., 1 de fev. de 2023 às 02:39, Alex Kaiser <alextkaiser@gmail.com> escreveu:
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);
Can you try:
select * from testing where id any = (values(1608377),(5449811),(5334677) ... <removed for brevity> ... ,(4654284),(3558460));
Or alternately you can use EXTEND STATISTICS to improve Postgres planner choice.
regards,
Ranier Vilela
On Wed, 1 Feb 2023 at 18:39, Alex Kaiser <alextkaiser@gmail.com> wrote: > postgres=# set force_parallel_mode = on; There's been a bit of debate about that GUC and I'm wondering how you came to the conclusion that it might help you. Can you share details of how you found out about it and what made you choose to set it to "on"? David
Rainier,
I tried using the any syntax (had to modify your query slightly) and it didn't result in any change in the query plan.
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
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[]))
I've never messed around with extended statistics, but I'm not sure how they would help here. From what I've read they seem to help when your query is restricting over multiple columns. Since this query is only on one column I'm not sure what a good "CREATE STATISTICS ..." command to run would be to improve the query plan. Any suggestions?
David,
As for how I found 'force_parallel_mode', I think I found it first here: https://postgrespro.com/list/thread-id/2574997 and then I also saw it when I was searching for 'parallel' on https://postgresqlco.nf .
It's not that I think the parameter would help my query, it was really as a last resort to try and force the query to be parallel. Without that parameter, it just does a normal index scan (see the result below). My thinking with using that parameter was to see if I could force a parallel query plan just to see if maybe the planner just thought the parallel plan would be more expensive. So I was surprised to see that even with that parameter turned on it doesn't actually do anything in parallel. Here is the plan with that parameter turned off:
postgres=# set force_parallel_mode = off;
SET
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
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[]))
(2 rows)
Thanks,
Alex Kaiser
I tried using the any syntax (had to modify your query slightly) and it didn't result in any change in the query plan.
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
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[]))
I've never messed around with extended statistics, but I'm not sure how they would help here. From what I've read they seem to help when your query is restricting over multiple columns. Since this query is only on one column I'm not sure what a good "CREATE STATISTICS ..." command to run would be to improve the query plan. Any suggestions?
David,
As for how I found 'force_parallel_mode', I think I found it first here: https://postgrespro.com/list/thread-id/2574997 and then I also saw it when I was searching for 'parallel' on https://postgresqlco.nf .
It's not that I think the parameter would help my query, it was really as a last resort to try and force the query to be parallel. Without that parameter, it just does a normal index scan (see the result below). My thinking with using that parameter was to see if I could force a parallel query plan just to see if maybe the planner just thought the parallel plan would be more expensive. So I was surprised to see that even with that parameter turned on it doesn't actually do anything in parallel. Here is the plan with that parameter turned off:
postgres=# set force_parallel_mode = off;
SET
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
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[]))
(2 rows)
Thanks,
Alex Kaiser
On Wed, Feb 1, 2023 at 3:30 AM David Rowley <dgrowleyml@gmail.com> wrote:
On Wed, 1 Feb 2023 at 18:39, Alex Kaiser <alextkaiser@gmail.com> wrote:
> postgres=# set force_parallel_mode = on;
There's been a bit of debate about that GUC and I'm wondering how you
came to the conclusion that it might help you. Can you share details
of how you found out about it and what made you choose to set it to
"on"?
David
On Wed, Feb 01, 2023 at 11:22:47AM -0800, Alex Kaiser wrote: > I've never messed around with extended statistics, but I'm not sure how > they would help here. From what I've read they seem to help when your query > is restricting over multiple columns. Since this query is only on one > column I'm not sure what a good "CREATE STATISTICS ..." command to run > would be to improve the query plan. Any suggestions? They wouldn't help. It seems like that was a guess. > As for how I found 'force_parallel_mode', I think I found it first here: > https://postgrespro.com/list/thread-id/2574997 and then I also saw it when > I was searching for 'parallel' on https://postgresqlco.nf . Yeah. force_parallel_mode is meant for debugging, only, and we're wondering how people end up trying to use it for other purposes. http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html Did you try adjusting min_parallel_index_scan_size / min_parallel_table_scan_size ? -- Justin
On Wed, Feb 1, 2023 at 6:39 PM Alex Kaiser <alextkaiser@gmail.com> wrote:
> 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
into1000/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
followingconfigs I don't get a query plan that actually uses any parallelism:
It sounds like the plan you are imagining is something like:
Gather
Nested Loop Join
Outer side: <partial scan of your set of constant values>
Inner side: Index scan of your big table
Such a plan would only give the right answer if each process has a
non-overlapping subset of the constant values to probe the index with,
and together they have the whole set. Hypothetically, a planner could
chop that set up beforehand and and give a different subset to each
process (just as you could do that yourself using N connections and
separate queries), but that might be unfair: one process might find
lots of matches, and the others might find none, because of the
distribution of data. So you'd ideally want some kind of "work
stealing" scheme, where each worker can take more values to probe from
whenever it needs more, so that they all keep working until the values
run out. We don't have a thing that can do that. You might imagine
that a CTE could do it, so WITH keys_to_look_up AS (VALUES (1), (2),
...) SELECT ... JOIN ON ..., but that also doesn't work because we
don't have a way to do "partial" scans of CTEs either (though someone
could invent that). Likewise for temporary tables: they are invisible
to parallel workers, so they can't help us. I have contemplated
"partial function scans" for set-returning functions, where a function
could be given a bit of shared memory and various other infrastructure
to be able to be "parallel aware" (= able to coordinate across
processes so that each process gets a subset of the data), and one
could imagine that that would allow various solutions to the problem,
but that's vapourware.
But you can get a plan like that if you insert all those values into a
regular table, depending on various settings, stats and
min_parallel_table_scan_size (try 0, I guess that'll definitely do
it). Which probably isn't the answer you wanted to hear.
Justin,
I did try changing min_parallel_index_scan_size / min_parallel_table_scan_size and didn't see any change (the below is with force_parallel_mode = off):
postgres=# set min_parallel_index_scan_size = 0;
SET
postgres=# set min_parallel_table_scan_size = 0;
SET
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
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[]))
(2 rows)
As for 'force_parallel_mode', while this isn't "debugging PG", it isn't something that I would actually turn on production, just something I was playing with to see the cost of parallel queries when the planner might not think they are the most efficient.
Thomas,
Thanks for the explanation. Yes, that is the query plan I was imagining. I do see how chopping it up could result in an unfair distribution. But my counter to that would be that wouldn't chopping it up still be better than not. If things do happen to work out to be fair, now it's X times as fast, if things are very unfair, then you haven't really lost much (besides the parallel overhead) compared to the non-parallel query. Or maybe it should be possible to do the parallel query if there were some statistics (either normal ones or extended ones) that told the planner that the result would probably be fair?
Though I do agree that the "work stealing" option would be the most efficient, but would be a lot more complicated to code up.
I tried out inserting into a separate table, and as you guessed that worked. For my production scenario that isn't really feasible, but still cool to see it work.
postgres=# create table ids(
probe_id int PRIMARY KEY
);
insert into ids(probe_id) values (774494);
insert into ids(probe_id) values (9141914);
...
postgres=# select count(*) from ids;
count
-------
1000
(1 row)
postgres=# explain select * from testing where id in (select * from ids);
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=0.43..3504.67 rows=1000 width=74)
Workers Planned: 2
-> Nested Loop (cost=0.43..3504.67 rows=417 width=74)
-> Parallel Seq Scan on ids (cost=0.00..9.17 rows=417 width=4)
-> Index Scan using testing_pkey on testing (cost=0.43..8.37 rows=1 width=74)
Index Cond: (id = ids.probe_id)
(6 rows)
Thanks,
Alex Kaiser
I did try changing min_parallel_index_scan_size / min_parallel_table_scan_size and didn't see any change (the below is with force_parallel_mode = off):
postgres=# set min_parallel_index_scan_size = 0;
SET
postgres=# set min_parallel_table_scan_size = 0;
SET
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
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[]))
(2 rows)
As for 'force_parallel_mode', while this isn't "debugging PG", it isn't something that I would actually turn on production, just something I was playing with to see the cost of parallel queries when the planner might not think they are the most efficient.
Thomas,
Thanks for the explanation. Yes, that is the query plan I was imagining. I do see how chopping it up could result in an unfair distribution. But my counter to that would be that wouldn't chopping it up still be better than not. If things do happen to work out to be fair, now it's X times as fast, if things are very unfair, then you haven't really lost much (besides the parallel overhead) compared to the non-parallel query. Or maybe it should be possible to do the parallel query if there were some statistics (either normal ones or extended ones) that told the planner that the result would probably be fair?
Though I do agree that the "work stealing" option would be the most efficient, but would be a lot more complicated to code up.
I tried out inserting into a separate table, and as you guessed that worked. For my production scenario that isn't really feasible, but still cool to see it work.
postgres=# create table ids(
probe_id int PRIMARY KEY
);
insert into ids(probe_id) values (774494);
insert into ids(probe_id) values (9141914);
...
postgres=# select count(*) from ids;
count
-------
1000
(1 row)
postgres=# explain select * from testing where id in (select * from ids);
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=0.43..3504.67 rows=1000 width=74)
Workers Planned: 2
-> Nested Loop (cost=0.43..3504.67 rows=417 width=74)
-> Parallel Seq Scan on ids (cost=0.00..9.17 rows=417 width=4)
-> Index Scan using testing_pkey on testing (cost=0.43..8.37 rows=1 width=74)
Index Cond: (id = ids.probe_id)
(6 rows)
Thanks,
Alex Kaiser
On Wed, Feb 1, 2023 at 1:52 PM Thomas Munro <thomas.munro@gmail.com> wrote:
On Wed, Feb 1, 2023 at 6:39 PM Alex Kaiser <alextkaiser@gmail.com> wrote:
> 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:
It sounds like the plan you are imagining is something like:
Gather
Nested Loop Join
Outer side: <partial scan of your set of constant values>
Inner side: Index scan of your big table
Such a plan would only give the right answer if each process has a
non-overlapping subset of the constant values to probe the index with,
and together they have the whole set. Hypothetically, a planner could
chop that set up beforehand and and give a different subset to each
process (just as you could do that yourself using N connections and
separate queries), but that might be unfair: one process might find
lots of matches, and the others might find none, because of the
distribution of data. So you'd ideally want some kind of "work
stealing" scheme, where each worker can take more values to probe from
whenever it needs more, so that they all keep working until the values
run out. We don't have a thing that can do that. You might imagine
that a CTE could do it, so WITH keys_to_look_up AS (VALUES (1), (2),
...) SELECT ... JOIN ON ..., but that also doesn't work because we
don't have a way to do "partial" scans of CTEs either (though someone
could invent that). Likewise for temporary tables: they are invisible
to parallel workers, so they can't help us. I have contemplated
"partial function scans" for set-returning functions, where a function
could be given a bit of shared memory and various other infrastructure
to be able to be "parallel aware" (= able to coordinate across
processes so that each process gets a subset of the data), and one
could imagine that that would allow various solutions to the problem,
but that's vapourware.
But you can get a plan like that if you insert all those values into a
regular table, depending on various settings, stats and
min_parallel_table_scan_size (try 0, I guess that'll definitely do
it). Which probably isn't the answer you wanted to hear.
On Thu, Feb 2, 2023 at 1:54 PM Alex Kaiser <alextkaiser@gmail.com> wrote:
> Thanks for the explanation. Yes, that is the query plan I was imagining. I do see how chopping it up could result in
anunfair distribution. But my counter to that would be that wouldn't chopping it up still be better than not. If things
dohappen to work out to be fair, now it's X times as fast, if things are very unfair, then you haven't really lost much
(besidesthe parallel overhead) compared to the non-parallel query. Or maybe it should be possible to do the parallel
queryif there were some statistics (either normal ones or extended ones) that told the planner that the result would
probablybe fair?
Maybe, but unfairness multiplies if it's part of a larger plan; what
if the output of those nodes is the input to much more work, but now
THAT work is being done by one process? But yeah, statistics could
help with that. I'm vaguely aware that other systems that do more
partition-based parallelism spend a lot of effort on that sort of
thinking.
> Though I do agree that the "work stealing" option would be the most efficient, but would be a lot more complicated to
codeup.
Yeah. I probably used the wrong word; what I was describing is
(something like) page-based parallelism, where input gets chopped up
into arbitrary chunks and handed out to consumers on demand, but we
don't know anything about the values in those chunks; that allows for
many interesting kind of plans, and it's nice because it's fair.
Another kind of parallelism is partition-based, which PostgreSQL can
do in a limited sense: we can send workers into different partitions
of a table (what we can't do is partition the table on-the-fly, which
is central to most parallelism in some other systems). Let's see:
CREATE TABLE testing(
id INT,
info INT,
data_one TEXT,
data_two TEXT,
primary key(id, info)
) partition by hash (id);
create table testing_p0 partition of testing for values with (modulus
2, remainder 0);
create table testing_p1 partition of testing for values with (modulus
2, remainder 1);
INSERT INTO testing(id, info, data_one, data_two)
SELECT idx, idx, md5(random()::text), md5(random()::text)
FROM generate_series(1,10000000) idx;
analyze;
explain select count(*) from testing where id in
(1608377,5449811,5334677,5458230,2053195,3572313,1949724,3559988,5061560,8479775,
...);
Aggregate
-> Append
-> Index Only Scan using testing_p0_pkey on testing_p0 testing_1
-> Index Only Scan using testing_p1_pkey on testing_p1 testing_2
Hmph. I can't seem to convince it to use Parallel Append. I think it
might be because the planner is not smart enough to chop down the =ANY
lists to match the partitions. One sec...
Ok I hacked my copy of PostgreSQL to let me set parallel_setup_costs
to negative numbers, and then I told it that parallelism is so awesome
that it makes your queries cost -1000000 timerons before they even
start. Now I see a plan like:
Gather (cost=-999999.57..-987689.45 rows=2000 width=74)
Workers Planned: 2
-> Parallel Append (cost=0.43..12110.55 rows=832 width=74)
-> Parallel Index Scan using testing_p0_pkey on testing_p0 testing_1
-> Parallel Index Scan using testing_p1_pkey on testing_p1 testing_2
But it's probing every index for every one of the values in the big
list, not just the ones that have a non-zero chance of finding a
match, which is a waste of cycles. I think if the planner were
smarter about THAT (as it is for plain old "="), then the costing
would have chosen parallelism naturally by cost.
But it's probably not as cool as page-based parallelism, because
parallelism is limited by your partitioning scheme.
If I had more timerons myself, I'd like to try to make parallel
function scans, or parallel CTE scans, work...
On Thu, 2 Feb 2023 at 14:49, Thomas Munro <thomas.munro@gmail.com> wrote: > If I had more timerons myself, I'd like to try to make parallel > function scans, or parallel CTE scans, work... I've not really looked in detail but I thought parallel VALUES scan might be easier than those two. David
Okay after reading http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html I do see that I was using force_parallel_mode incorectly and wouldn't have gotten what I wanted even if the original query was possible to parallelize.
> Maybe, but unfairness multiplies if it's part of a larger plan
Ah, I didn't think of that, and it's a good point.
> Ok I hacked my copy of PostgreSQL to let me set parallel_setup_costs
> to negative numbers ...
Thanks for taking the time to do that and look into that. I don't actually think it's worth the confusion to allow this in general, but I was thinking that setting "force_parallel_mode = on" would essentially be doing something equivalent to this (though I now see that is wrong).
> But it's probing every index for every one of the values in the big
> list, not just the ones that have a non-zero chance of finding a
> match, which is a waste of cycles.
In my case, this would actually be quite helpful because the real bottleneck when I run this in production is time spent waiting for IO. I was hoping to spread that IO wait time over multiple threads, and wouldn't really care about the few extra wasted CPU cycles. But I can't actually do this as I can't set parallel_setup_costs to be negative, so I wouldn't be able to get PG to choose the parallel plan even if I did partition the table.
> If I had more timerons myself ...
If only we all had more timerons ... :)
Thanks,
Alex Kaiser
> Maybe, but unfairness multiplies if it's part of a larger plan
Ah, I didn't think of that, and it's a good point.
> Ok I hacked my copy of PostgreSQL to let me set parallel_setup_costs
> to negative numbers ...
Thanks for taking the time to do that and look into that. I don't actually think it's worth the confusion to allow this in general, but I was thinking that setting "force_parallel_mode = on" would essentially be doing something equivalent to this (though I now see that is wrong).
> But it's probing every index for every one of the values in the big
> list, not just the ones that have a non-zero chance of finding a
> match, which is a waste of cycles.
In my case, this would actually be quite helpful because the real bottleneck when I run this in production is time spent waiting for IO. I was hoping to spread that IO wait time over multiple threads, and wouldn't really care about the few extra wasted CPU cycles. But I can't actually do this as I can't set parallel_setup_costs to be negative, so I wouldn't be able to get PG to choose the parallel plan even if I did partition the table.
> If I had more timerons myself ...
If only we all had more timerons ... :)
Thanks,
Alex Kaiser
On Wed, Feb 1, 2023 at 6:12 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Thu, 2 Feb 2023 at 14:49, Thomas Munro <thomas.munro@gmail.com> wrote:
> If I had more timerons myself, I'd like to try to make parallel
> function scans, or parallel CTE scans, work...
I've not really looked in detail but I thought parallel VALUES scan
might be easier than those two.
David