Thread: BUG #16741: Error: subplan "SubPlan 1" was not initialized

The following bug has been logged on the website:

Bug reference:      16741
Logged by:          Radek Salač
Email address:      radek@salac.org
PostgreSQL version: 13.1
Operating system:   Debian
Description:

When running the query (whole script can be found here:
https://drive.google.com/file/d/1DaAZaAXVYm-Uo0mb7vxPEi6Ns5_NGd7s/view?usp=sharing)

SELECT 
1
FROM       tmp_rs_cp "cp" 
LEFT JOIN  tmp_rs_cfi  ON cp.id = tmp_rs_cfi.cp_id 
INNER JOIN tmp_rs_cpb "cpb" ON true 

ORDER BY   ( 
  SELECT 1
  FROM   tmp_rs_cpis 
  WHERE  cp.id = tmp_rs_cpis.contract_part_id 
) ASC LIMIT 10;

I'm getting error: 

ERROR: subplan "SubPlan 1" was not initialized
CONTEXT: parallel worker

In PG 13.0 it failed without meaningfull error so we updated but it's still
not working


The explain plan in my case is (JSON format):


[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Startup Cost": 2100.44,
      "Total Cost": 2431.82,
      "Plan Rows": 10,
      "Plan Width": 8,
      "Output": ["1", "((SubPlan 1))"],
      "Plans": [
        {
          "Node Type": "Nested Loop",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Join Type": "Inner",
          "Startup Cost": 2100.44,
          "Total Cost": 1690024934.78,
          "Plan Rows": 51000000,
          "Plan Width": 8,
          "Output": ["1", "(SubPlan 1)"],
          "Inner Unique": false,
          "Plans": [
            {
              "Node Type": "Nested Loop",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Join Type": "Left",
              "Startup Cost": 2100.44,
              "Total Cost": 12392.90,
              "Plan Rows": 20000,
              "Plan Width": 16,
              "Output": ["cp.id"],
              "Inner Unique": false,
              "Plans": [
                {
                  "Node Type": "Gather Merge",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Startup Cost": 2100.29,
                  "Total Cost": 4379.70,
                  "Plan Rows": 20000,
                  "Plan Width": 16,
                  "Output": ["cp.id"],
                  "Workers Planned": 1,
                  "Plans": [
                    {
                      "Node Type": "Sort",
                      "Parent Relationship": "Outer",
                      "Parallel Aware": false,
                      "Startup Cost": 1100.28,
                      "Total Cost": 1129.69,
                      "Plan Rows": 11765,
                      "Plan Width": 16,
                      "Output": ["cp.id", "((SubPlan 1))"],
                      "Sort Key": ["((SubPlan 1))"],
                      "Plans": [
                        {
                          "Node Type": "Index Only Scan",
                          "Parent Relationship": "Outer",
                          "Parallel Aware": true,
                          "Scan Direction": "Forward",
                          "Index Name": "tmp_rs_cp_id_idx",
                          "Relation Name": "tmp_rs_cp",
                          "Schema": "public",
                          "Alias": "cp",
                          "Startup Cost": 0.29,
                          "Total Cost": 304.83,
                          "Plan Rows": 11765,
                          "Plan Width": 16,
                          "Output": ["cp.id", "(SubPlan 1)"],
                          "Plans": [
                            {
                              "Node Type": "Seq Scan",
                              "Parent Relationship": "SubPlan",
                              "Subplan Name": "SubPlan 1",
                              "Parallel Aware": false,
                              "Relation Name": "tmp_rs_cpis",
                              "Schema": "public",
                              "Alias": "tmp_rs_cpis",
                              "Startup Cost": 0.00,
                              "Total Cost": 33.12,
                              "Plan Rows": 9,
                              "Plan Width": 4,
                              "Output": ["1"],
                              "Filter": "(cp.id =
tmp_rs_cpis.contract_part_id)"
                            }
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "Node Type": "Index Only Scan",
                  "Parent Relationship": "Inner",
                  "Parallel Aware": false,
                  "Scan Direction": "Forward",
                  "Index Name": "tmp_rs_cfi_cp_id_idx",
                  "Relation Name": "tmp_rs_cfi",
                  "Schema": "public",
                  "Alias": "tmp_rs_cfi",
                  "Startup Cost": 0.15,
                  "Total Cost": 0.31,
                  "Plan Rows": 9,
                  "Plan Width": 16,
                  "Output": ["tmp_rs_cfi.cp_id"],
                  "Index Cond": "(tmp_rs_cfi.cp_id = cp.id)"
                }
              ]
            },
            {
              "Node Type": "Materialize",
              "Parent Relationship": "Inner",
              "Parallel Aware": false,
              "Startup Cost": 0.00,
              "Total Cost": 48.25,
              "Plan Rows": 2550,
              "Plan Width": 0,
              "Plans": [
                {
                  "Node Type": "Seq Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Relation Name": "tmp_rs_cpb",
                  "Schema": "public",
                  "Alias": "cpb",
                  "Startup Cost": 0.00,
                  "Total Cost": 35.50,
                  "Plan Rows": 2550,
                  "Plan Width": 0
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

PG Bug reporting form <noreply@postgresql.org> writes:
> I'm getting error:

> ERROR: subplan "SubPlan 1" was not initialized
> CONTEXT: parallel worker

Yeah, this is a known issue that we haven't yet fixed, cf [1].

I believe you should be able to work around it for now
by turning off enable_incremental_sort.

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/622580997.37108180.1604080457319.JavaMail.zimbra%40siscobra.com.br

On Mon, Nov 23, 2020 at 5:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > I'm getting error:
>
> > ERROR: subplan "SubPlan 1" was not initialized
> > CONTEXT: parallel worker
>
> Yeah, this is a known issue that we haven't yet fixed, cf [1].
>
> I believe you should be able to work around it for now
> by turning off enable_incremental_sort.
>
>                         regards, tom lane
>
> [1] https://www.postgresql.org/message-id/flat/622580997.37108180.1604080457319.JavaMail.zimbra%40siscobra.com.br


That might workaround it for 13/13.1, though the attached plan has a
sort, not an incremental sort, and so it's possible that won't help
(disabling parallel query for this particular query would then
resolve, but that's a fairly large sledgehammer).

A fix is being discussed in [1].

James

1: https://www.postgresql.org/message-id/flat/CAAaqYe8cK3g5CfLC4w7bs%3DhC0mSksZC%3DH5M8LSchj5e5OxpTAg%40mail.gmail.com