Postgresql 15 query optimizer refuses to parallelize a query on a partitioned table

Question

I have a table "weather" that stores weather parameters for 45 years of entire Europe area with 400 million of record partitioned by range in an Oracle database managed through 19c instance and the table has been duplicated using oracle_fdw foreign wrapper in a PostgreSQL 15 instance, so now that table is local to PostgreSQL instance. Both tables are preloaded in memory in keep pool for Oracle and shared buffers in PostgreSQL using pg_prewarm.

I wrote a query that generates a ranking map of temperature max in a selected interval for every year. Now execution time for one month interval is 48s in PostgreSQL and 130s in Oracle, but I can force the Oracle query optimizer to parallel execution that sounds logic with a partitioned table using hint /*+ PARALLEL */, in this way execution time goes down from 130s to 7s, but there isn't any possibility to force PostgreSQL query optimizer to do the same. We know there aren't hints in PostgreSQL but I played with related configuration parameters to force a parallel strategy for the query, useless. Do You know if there is possible strategy to force PostgreSQL optimizer to parallelize the query ?

The PostgreSQL query:

SELECT  idgrid idfeature,value_
FROM (SELECT idgrid,year,RANK() OVER (PARTITION BY idgrid ORDER BY CASE WHEN '£(orderby)' = '0' THEN value_ ELSE -1 * value_ END) value_ 
      FROM (SELECT  idgrid,
                   year,
                   avg (w.temperature_avg)
                   value_ 
            FROM   weather w,(SELECT  year,
                                         TO_DATE(year||start_,'yyyymmdd') start_,
                                         TO_DATE((year + CASE WHEN start_ > end_ THEN 1 ELSE 0 END)||end_,'yyyymmdd') end_ 
                                  FROM (SELECT  SUBSTR(REPLACE('2022-05-01','-',''),5) start_,SUBSTR(REPLACE('2022-05-31','-',''),5) end_,year 
                                        FROM GENERATE_SERIES(1979,EXTRACT( year FROM sysdate())::integer) year 
                                       ) q
                                  WHERE TO_DATE((year + CASE WHEN start_ > end_ THEN 1 ELSE 0 END)||end_,'yyyymmdd') <= TRUNC(sysdate())
                                 ) t
            WHERE day BETWEEN t.start_ AND t.end_
            GROUP BY idgrid,year
           ) w
      ) w 
WHERE year = EXTRACT(year FROM TO_DATE(REPLACE('2022-05-01','-',''),'yyyymmdd'));

The plan:

 Subquery Scan on w  (cost=63589863.39..63604357.59 rows=1705 width=16) (actual time=46617.355..47834.720 rows=22799 loops=1)
   Output: w.idgrid, w.value_
   Filter: ((w.year)::numeric = EXTRACT(year FROM to_date('20220501'::text, 'yyyymmdd'::text)))
   Rows Removed by Filter: 980399
   ->  WindowAgg  (cost=63589863.39..63597536.79 rows=341040 width=52) (actual time=46617.324..47453.601 rows=1003198 loops=1)
         Output: w_1.idgrid, w_1.year, rank() OVER (?), (('-1'::numeric * w_1.value_))
         ->  Sort  (cost=63589863.39..63590715.99 rows=341040 width=44) (actual time=46617.265..46850.567 rows=1003198 loops=1)
               Output: w_1.idgrid, (('-1'::numeric * w_1.value_)), w_1.year
               Sort Key: w_1.idgrid, (('-1'::numeric * w_1.value_))
               Sort Method: external merge  Disk: 37056kB
               ->  Subquery Scan on w_1  (cost=56361567.54..63558522.53 rows=341040 width=44) (actual time=37590.732..45768.215 rows=1003198 loops=1)
                     Output: w_1.idgrid, ('-1'::numeric * w_1.value_), w_1.year
                     ->  HashAggregate  (cost=56361567.54..63554259.53 rows=341040 width=44) (actual time=37590.725..45575.127 rows=1003198 loops=1)
                           Output: w_2.idgrid, year.year, avg(w_2.temperature_avg)
                           Group Key: w_2.idgrid, year.year
                           Planned Partitions: 4  Batches: 21  Memory Usage: 65593kB  Disk Usage: 1015616kB
                           ->  Nested Loop  (cost=0.47..31585448.96 rows=613412607 width=18) (actual time=2374.190..29600.547 rows=31098470 loops=1)
                                 Output: w_2.idgrid, year.year, w_2.temperature_avg
                                 ->  Function Scan on pg_catalog.generate_series year  (cost=0.01..1.36 rows=15 width=4) (actual time=2374.031..2374.808 rows=44 loops=1)
                                       Output: year.year
                                       Function Call: generate_series(1979, (EXTRACT(year FROM sysdate()))::integer)
                                       Filter: (to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text) <= trunc((sysdate())::timestamp without time zone, 'DDD'::text))
                                       Rows Removed by Filter: 1
                                 ->  Append  (cost=0.46..1696754.75 rows=40894176 width=18) (actual time=0.090..572.757 rows=706783 loops=44)
                                       ->  Index Scan using weather_obs_grid_p1979_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1979 w_3  (cost=0.46..32925.04 rows=924705 width=18) (actual time=0.045..551.046 rows=706749 loops=1)
                                             Output: w_3.idgrid, w_3.temperature_avg, w_3.day
                                             Index Cond: ((w_3.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_3.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1980_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1980 w_4  (cost=0.46..32565.89 rows=927201 width=18) (actual time=0.042..536.988 rows=706749 loops=1)
                                             Output: w_4.idgrid, w_4.temperature_avg, w_4.day
                                             Index Cond: ((w_4.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_4.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1981_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1981 w_5  (cost=0.46..32532.61 rows=924586 width=18) (actual time=0.037..536.488 rows=706749 loops=1)
                                             Output: w_5.idgrid, w_5.temperature_avg, w_5.day
                                             Index Cond: ((w_5.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_5.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1982_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1982 w_6  (cost=0.46..32503.67 rows=924554 width=18) (actual time=0.036..534.594 rows=706749 loops=1)
                                             Output: w_6.idgrid, w_6.temperature_avg, w_6.day
                                             Index Cond: ((w_6.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_6.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1983_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1983 w_7  (cost=0.46..32444.75 rows=924543 width=18) (actual time=0.048..534.565 rows=706749 loops=1)
                                             Output: w_7.idgrid, w_7.temperature_avg, w_7.day
                                             Index Cond: ((w_7.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_7.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1984_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1984 w_8  (cost=0.46..32351.98 rows=927149 width=18) (actual time=0.036..534.993 rows=706749 loops=1)
                                             Output: w_8.idgrid, w_8.temperature_avg, w_8.day
                                             Index Cond: ((w_8.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_8.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1985_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1985 w_9  (cost=0.46..32853.73 rows=924534 width=18) (actual time=0.033..582.760 rows=706749 loops=1)
                                             Output: w_9.idgrid, w_9.temperature_avg, w_9.day
                                             Index Cond: ((w_9.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_9.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1986_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1986 w_10  (cost=0.46..33156.81 rows=924581 width=18) (actual time=0.096..525.748 rows=706749 loops=1)
                                             Output: w_10.idgrid, w_10.temperature_avg, w_10.day
                                             Index Cond: ((w_10.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_10.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1987_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1987 w_11  (cost=0.46..33248.66 rows=924619 width=18) (actual time=0.043..521.231 rows=706749 loops=1)
                                             Output: w_11.idgrid, w_11.temperature_avg, w_11.day
                                             Index Cond: ((w_11.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_11.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1988_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1988 w_12  (cost=0.46..33403.99 rows=927100 width=18) (actual time=0.055..575.633 rows=706749 loops=1)
                                             Output: w_12.idgrid, w_12.temperature_avg, w_12.day
                                             Index Cond: ((w_12.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_12.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1989_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1989 w_13  (cost=0.46..33455.89 rows=924643 width=18) (actual time=0.057..515.625 rows=706749 loops=1)
                                             Output: w_13.idgrid, w_13.temperature_avg, w_13.day
                                             Index Cond: ((w_13.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_13.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1990_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1990 w_14  (cost=0.46..33470.87 rows=924577 width=18) (actual time=0.058..547.083 rows=706749 loops=1)
                                             Output: w_14.idgrid, w_14.temperature_avg, w_14.day
                                             Index Cond: ((w_14.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_14.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1991_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1991 w_15  (cost=0.46..33325.20 rows=924629 width=18) (actual time=0.063..548.030 rows=706749 loops=1)
                                             Output: w_15.idgrid, w_15.temperature_avg, w_15.day
                                             Index Cond: ((w_15.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_15.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1992_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1992 w_16  (cost=0.46..33514.54 rows=927248 width=18) (actual time=0.056..533.874 rows=706749 loops=1)
                                             Output: w_16.idgrid, w_16.temperature_avg, w_16.day
                                             Index Cond: ((w_16.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_16.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1993_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1993 w_17  (cost=0.46..33432.95 rows=924645 width=18) (actual time=0.047..525.468 rows=706749 loops=1)
                                             Output: w_17.idgrid, w_17.temperature_avg, w_17.day
                                             Index Cond: ((w_17.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_17.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1994_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1994 w_18  (cost=0.46..33361.19 rows=924488 width=18) (actual time=0.037..521.450 rows=706749 loops=1)
                                             Output: w_18.idgrid, w_18.temperature_avg, w_18.day
                                             Index Cond: ((w_18.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_18.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1995_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1995 w_19  (cost=0.46..34106.76 rows=924623 width=18) (actual time=0.045..524.438 rows=706749 loops=1)
                                             Output: w_19.idgrid, w_19.temperature_avg, w_19.day
                                             Index Cond: ((w_19.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_19.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1996_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1996 w_20  (cost=0.46..34150.05 rows=927151 width=18) (actual time=0.036..521.144 rows=706749 loops=1)
                                             Output: w_20.idgrid, w_20.temperature_avg, w_20.day
                                             Index Cond: ((w_20.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_20.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1997_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1997 w_21  (cost=0.46..34077.92 rows=924561 width=18) (actual time=0.059..505.392 rows=706749 loops=1)
                                             Output: w_21.idgrid, w_21.temperature_avg, w_21.day
                                             Index Cond: ((w_21.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_21.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1998_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1998 w_22  (cost=0.46..34027.87 rows=924860 width=18) (actual time=0.049..522.386 rows=706959 loops=1)
                                             Output: w_22.idgrid, w_22.temperature_avg, w_22.day
                                             Index Cond: ((w_22.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_22.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p1999_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p1999 w_23  (cost=0.46..34006.84 rows=924782 width=18) (actual time=0.034..567.772 rows=706966 loops=1)
                                             Output: w_23.idgrid, w_23.temperature_avg, w_23.day
                                             Index Cond: ((w_23.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_23.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2000_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2000 w_24  (cost=0.46..34114.54 rows=927302 width=18) (actual time=0.046..597.921 rows=706966 loops=1)
                                             Output: w_24.idgrid, w_24.temperature_avg, w_24.day
                                             Index Cond: ((w_24.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_24.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2001_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2001 w_25  (cost=0.46..34053.55 rows=924877 width=18) (actual time=0.061..521.195 rows=706966 loops=1)
                                             Output: w_25.idgrid, w_25.temperature_avg, w_25.day
                                             Index Cond: ((w_25.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_25.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2002_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2002 w_26  (cost=0.46..34061.02 rows=924836 width=18) (actual time=0.036..521.394 rows=706966 loops=1)
                                             Output: w_26.idgrid, w_26.temperature_avg, w_26.day
                                             Index Cond: ((w_26.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_26.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2003_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2003 w_27  (cost=0.46..34007.73 rows=924846 width=18) (actual time=0.028..594.699 rows=706966 loops=1)
                                             Output: w_27.idgrid, w_27.temperature_avg, w_27.day
                                             Index Cond: ((w_27.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_27.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2004_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2004 w_28  (cost=0.46..34234.81 rows=927221 width=18) (actual time=0.115..529.867 rows=706768 loops=1)
                                             Output: w_28.idgrid, w_28.temperature_avg, w_28.day
                                             Index Cond: ((w_28.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_28.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2005_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2005 w_29  (cost=0.46..34151.58 rows=924577 width=18) (actual time=0.044..526.587 rows=706769 loops=1)
                                             Output: w_29.idgrid, w_29.temperature_avg, w_29.day
                                             Index Cond: ((w_29.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_29.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2006_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2006 w_30  (cost=0.46..34153.21 rows=924614 width=18) (actual time=0.038..532.100 rows=706769 loops=1)
                                             Output: w_30.idgrid, w_30.temperature_avg, w_30.day
                                             Index Cond: ((w_30.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_30.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2007_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2007 w_31  (cost=0.46..34188.24 rows=924623 width=18) (actual time=0.032..531.111 rows=706764 loops=1)
                                             Output: w_31.idgrid, w_31.temperature_avg, w_31.day
                                             Index Cond: ((w_31.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_31.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2008_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2008 w_32  (cost=0.46..34298.52 rows=927174 width=18) (actual time=0.047..524.210 rows=706769 loops=1)
                                             Output: w_32.idgrid, w_32.temperature_avg, w_32.day
                                             Index Cond: ((w_32.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_32.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2009_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2009 w_33  (cost=0.46..34197.59 rows=924650 width=18) (actual time=0.028..517.573 rows=706769 loops=1)
                                             Output: w_33.idgrid, w_33.temperature_avg, w_33.day
                                             Index Cond: ((w_33.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_33.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2010_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2010 w_34  (cost=0.46..34208.68 rows=924578 width=18) (actual time=0.042..532.888 rows=706769 loops=1)
                                             Output: w_34.idgrid, w_34.temperature_avg, w_34.day
                                             Index Cond: ((w_34.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_34.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2011_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2011 w_35  (cost=0.46..34130.55 rows=924633 width=18) (actual time=0.034..542.809 rows=706769 loops=1)
                                             Output: w_35.idgrid, w_35.temperature_avg, w_35.day
                                             Index Cond: ((w_35.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_35.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2012_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2012 w_36  (cost=0.46..34274.88 rows=927143 width=18) (actual time=0.038..559.891 rows=706769 loops=1)
                                             Output: w_36.idgrid, w_36.temperature_avg, w_36.day
                                             Index Cond: ((w_36.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_36.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2013_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2013 w_37  (cost=0.46..34201.06 rows=924543 width=18) (actual time=0.036..525.227 rows=706749 loops=1)
                                             Output: w_37.idgrid, w_37.temperature_avg, w_37.day
                                             Index Cond: ((w_37.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_37.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2014_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2014 w_38  (cost=0.46..34213.90 rows=924551 width=18) (actual time=0.060..563.243 rows=706750 loops=1)
                                             Output: w_38.idgrid, w_38.temperature_avg, w_38.day
                                             Index Cond: ((w_38.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_38.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2015_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2015 w_39  (cost=0.46..34149.88 rows=924598 width=18) (actual time=0.034..531.485 rows=706749 loops=1)
                                             Output: w_39.idgrid, w_39.temperature_avg, w_39.day
                                             Index Cond: ((w_39.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_39.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2016_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2016 w_40  (cost=0.46..34209.81 rows=927106 width=18) (actual time=0.034..547.414 rows=706750 loops=1)
                                             Output: w_40.idgrid, w_40.temperature_avg, w_40.day
                                             Index Cond: ((w_40.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_40.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2017_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2017 w_41  (cost=0.46..34135.90 rows=924628 width=18) (actual time=0.036..540.524 rows=706767 loops=1)
                                             Output: w_41.idgrid, w_41.temperature_avg, w_41.day
                                             Index Cond: ((w_41.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_41.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2018_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2018 w_42  (cost=0.46..34198.60 rows=924566 width=18) (actual time=0.053..535.149 rows=706754 loops=1)
                                             Output: w_42.idgrid, w_42.temperature_avg, w_42.day
                                             Index Cond: ((w_42.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_42.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2019_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2019 w_43  (cost=0.46..34244.49 rows=924614 width=18) (actual time=0.035..530.408 rows=706769 loops=1)
                                             Output: w_43.idgrid, w_43.temperature_avg, w_43.day
                                             Index Cond: ((w_43.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_43.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2020_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2020 w_44  (cost=0.46..34625.68 rows=927111 width=18) (actual time=0.031..533.647 rows=706749 loops=1)
                                             Output: w_44.idgrid, w_44.temperature_avg, w_44.day
                                             Index Cond: ((w_44.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_44.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2021_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2021 w_45  (cost=0.46..34198.67 rows=924618 width=18) (actual time=0.030..529.111 rows=706749 loops=1)
                                             Output: w_45.idgrid, w_45.temperature_avg, w_45.day
                                             Index Cond: ((w_45.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_45.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2022_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2022 w_46  (cost=0.46..34483.13 rows=924545 width=18) (actual time=0.044..546.610 rows=706749 loops=1)
                                             Output: w_46.idgrid, w_46.temperature_avg, w_46.day
                                             Index Cond: ((w_46.day >= to_date(((year.year)::text || '0501'::text), 'yyyymmdd'::text)) AND (w_46.day <= to_date((((year.year + 0))::text || '0531'::text), 'yyyymmdd'::text)))
                                       ->  Index Scan using weather_obs_grid_p2023_day_idgrid_idx on cache_eur_cgms21.weather_obs_grid_p2023 w_47  (cost=0.45..6630.61 rows=182442 width=18) (never executed)

I truncated the plan text but it continues with other two rows as above — Tony Zucchini, Mar 14 '23 at 18:36
Postgres does not provide a `sysdate()` function. And there is also no `trunc()` function that works on dates. Are you using some kind of fork? — , Mar 14 '23 at 19:19
`EXTRACT(year FROM TO_DATE(REPLACE('2022-05-01','-',''),'yyyymmdd'))` can be simplified to `2022` — , Mar 14 '23 at 19:24
Disk Usage: 1015616kB, this means the database doesn't have access to enough RAM when it is needed. What is your setting for work_mem and did you try other settings? And could you share the "postgresql" version? Because of sysdate() that is not a standard postgresql function — Frank Heikens, Mar 14 '23 at 20:36
Dear Horse, thanks for your suggestions but the original query contains place holders replaced by APIs with values from a web application. — Tony Zucchini, Mar 14 '23 at 22:30
There is little we can say with a query plan where relevant parts have been left out. You could post a link to explain.depesz.com. Also, add to the question the active values of `max_parallel_workers` and `max_parallel_workers_per_gather` and all other performance related parameters set to non-default values. These would be visible in `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)` output. The statistics on `weather` seem off. The selective filter in the outmost `WHERE` condition is applied to late, try to push it deeper into the query. Please simplify that baroque date arithmetic; this is unreadable. — Laurenz Albe, May 04 '23 at 15:34

score 0 · Answer 1 · answered Mar 14 '23 at 21:11

0

When you said

I played with related configuration parameters to force a parallel strategy for the query, useless.

did you refer to these params?

set enable_partitionwise_aggregate = on;

set max_parallel_workers = 32;
set max_parallel_workers_per_gather = 32;
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set min_parallel_table_scan_size = 0;
set min_parallel_index_scan_size = 0;

set parallel_leader_participation = off;

Also, are you using any UDF not marked parallel safe?

answered Mar 14 '23 at 21:11

Florian Klein

8,692
1
32
42

It is a good point: to make portable queries between Oracle and Postgresql enviroment I used Orafce extension that export Oracle functions in Postgresql environment. All these functions are parallel unsafe, so now I will rewrite the query using native functions. – Tony Zucchini Mar 14 '23 at 22:58
you can cheat and wrap those unsafe functions in UDFs marked parallel safe as well (or if you're really a nihilist temporarily `update pg_proc set proparallel = 's' where ...` - just for testing of course) – Florian Klein Mar 15 '23 at 11:30
I made all immutable and stable functions under oracle schema created by orafce extension as PARALLEL SAFE and I set work_mem = '1024MB'. I gain only 6 seconds and only sorting in memory instead using disk, but when I rewrote the query using native functions, execution time goes down from 42 to 8s. Anyway query has not yet parallelized. – Tony Zucchini Mar 15 '23 at 19:14
surprising to see such a high diff b/w orafce and native, because the functions you use are either written in C or in SQL, which should be trivially inlinable (https://github.com/orafce/orafce/blob/master/orafce--4.2.sql#L486) But maybe the orafce C versions are really slow, IDK. Anyway, postgres is now on par with oracle without parallelisation, so problem solved? ;) – Florian Klein Mar 15 '23 at 20:00
as a very last resort, and for testing purpose only, you could force postgres to enable parallel mode https://www.postgresql.org/docs/15/runtime-config-developer.html#GUC-FORCE-PARALLEL-MODE, but I have no idea if that will force a parallel query plan – Florian Klein Mar 15 '23 at 20:07
1

Yes Florian now performances are good as in Oracle but I'd like to know how to force parallelization in Postgres like Oracle and this point still appears a bit dark. – Tony Zucchini Mar 15 '23 at 22:49
I rewrote the query again with these settings: Settings: effective_cache_size = '1536GB', effective_io_concurrency = '450', enable_partitionwise_aggregate = 'on', enable_partitionwise_join = 'on', max_parallel _workers = '80', max_parallel_workers_per_gather = '20', min_parallel_index_scan_size = '0', min_parallel_table_scan_size = '0', parallel_setup_cost = '0', paralle l_tuple_cost = '0', random_page_cost = '1.1', search_path = '"$user", public, oracle, topology', work_mem = '1GB' – Tony Zucchini Mar 16 '23 at 16:12
now I saw a parallel scan on a materialized CTE that returns the intervals for every year and there is only one worker assigned to the index scan of 45 partitions when in Oracle this task has been high parallelized while Postgresql planner refuses to adopt what seems a trivial strategy for reading a partitioned table. – Tony Zucchini Mar 16 '23 at 16:17
I tried ALTER TABLE weather SET (parallel_workers = 20); but the DDL command has been refused as not valid option, so it appears that parallelization doesn't work yet for partitioned tables. Am I wrong ? if not this is a huge issue in DW environments. – Tony Zucchini Mar 16 '23 at 18:51
I'm sorry I couldn't help more, but here is a part of the docs talking about this subject https://www.postgresql.org/docs/current/parallel-plans.html#PARALLEL-APPEND although I'm not sure how to interpret it. I'm seeing many blogs talking about postgres 11 introducing partition parallelism but no explicit explanation https://severalnines.com/blog/postgresql-query-parallelism-action/ – Florian Klein Mar 17 '23 at 08:23
did you try to add `VERBOSE` to your `explain analyze`? It should spit out more information about partitions and parallelism. – Florian Klein Mar 17 '23 at 08:25
concerning `ALTER TABLE weather SET (parallel_workers = 20); `, pretty sure that's normal to be refused, it's not a valid TTL. Just use `SET` or `SET LOCAL` (or in config file) to change this GUC per session/transaction/globally. No way to define it per table (although it might be possible to define it per database/globally with `ALTER DATABASE SET` or `ALTER SYSTEM SET`) – Florian Klein Mar 17 '23 at 08:28
An other thing to look at concerning "partition-wise joins" is wether you include the partition key in your join conditions – Florian Klein Mar 17 '23 at 08:31
as described in https://www.enterprisedb.com/blog/partition-wise-joins-divide-and-conquer-joins-between-partitioned-table – Florian Klein Mar 17 '23 at 08:46
1

I posted the setting configurations for planner how you can in previous comment with all options activated for explain: timing, summary, analyze, verbose settings,buffers and with enable_partitionwise_join = 'on', partition key is day and how you can see in the plan, there is an index scan for each partition using related partitioned index. For what I read it seems that parallel append has not been enabled yet for partitioned tables. – Tony Zucchini Mar 17 '23 at 10:10

Postgresql 15 query optimizer refuses to parallelize a query on a partitioned table

1 Answers1