Big performance slowdown from 11.2 to 13.3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 

Hello,

 

We have a data warehouse working on Postgres V11.2. We have a query that is pretty beefy that has been taking under 5mn to run consistently every day for about 2 years as part of a data warehouse ETL process. It’s a pivot over 55 values on a table with some 15M rows. The total table size is over 2GB (table+indices+other).

 

 

CREATE TABLE assessmenticcqa_raw

(

    iccqar_iccassmt_fk integer NOT NULL, -- foreign key to assessment

    iccqar_ques_code character varying(255) COLLATE pg_catalog."default" NOT NULL, -- question code

    iccqar_ans_val character varying(255) COLLATE pg_catalog."default" NOT NULL,   -- answer value

    "lastUpdated" timestamp with time zone NOT NULL DEFAULT now(),

    CONSTRAINT fk_assessmenticcqa_raw_assessment FOREIGN KEY (iccqar_iccassmt_fk)

        REFERENCES assessmenticc_fact (iccassmt_pk) MATCH SIMPLE

        ON UPDATE CASCADE

        ON DELETE RESTRICT

)

 

TABLESPACE pg_default;

 

CREATE UNIQUE INDEX assessmenticcqa_raw_idx_iccqar_assmt_ques

    ON assessmenticcqa_raw USING btree

    (iccqar_iccassmt_fk ASC NULLS LAST, iccqar_ques_code COLLATE pg_catalog."default" ASC NULLS LAST)

    TABLESPACE pg_default;

 

CREATE INDEX assessmenticcqa_raw_idx_iccqar_lastupdated

    ON assessmenticcqa_raw USING btree

    ("lastUpdated" ASC NULLS LAST)

    TABLESPACE pg_default;

 

 

The query that does the pivot is:

 

 

WITH t AS (

         SELECT assessmenticcqa_raw.iccqar_iccassmt_fk AS iccqa_iccassmt_fk,

            assessmenticcqa_raw.iccqar_ques_code,

            max(assessmenticcqa_raw.iccqar_ans_val::text) AS iccqar_ans_val

           FROM assessmenticcqa_raw

          WHERE assessmenticcqa_raw.iccqar_ques_code::text = ANY (ARRAY['DEBRIDEMENT DATE'::character varying::text

                                                                      , 'DEBRIDEMENT THIS VISIT'::character varying::text

                                                                      , 'DEBRIDEMENT TYPE'::character varying::text

                                                                      , 'DEPTH (CM)'::character varying::text

                                                                      , 'DEPTH DESCRIPTION'::character varying::text

                                                                      , … 55 total columns to pivot

                                                                 ])

          GROUP BY assessmenticcqa_raw.iccqar_iccassmt_fk, assessmenticcqa_raw.iccqar_ques_code

        )

SELECT t.iccqa_iccassmt_fk,

    max(t.iccqar_ans_val) AS iccqar_ans_val,

    tilda.todate(max(t.iccqar_ans_val) FILTER (WHERE t.iccqar_ques_code::text = 'DEBRIDEMENT DATE'::text)::character varying, NULL::date) AS "iccqa_DEBRIDEMENT_DATE",

    max(t.iccqar_ans_val) FILTER (WHERE t.iccqar_ques_code::text = 'DEBRIDEMENT THIS VISIT'::text) AS "iccqa_DEBRIDEMENT_THIS_VISIT",

    max(t.iccqar_ans_val) FILTER (WHERE t.iccqar_ques_code::text = 'DEBRIDEMENT TYPE'::text) AS "iccqa_DEBRIDEMENT_TYPE",

    tilda.tofloat(max(t.iccqar_ans_val) FILTER (WHERE t.iccqar_ques_code::text = 'DEPTH (CM)'::text)::character varying, NULL::real) AS "iccqa_DEPTH_CM",

    max(t.iccqar_ans_val) FILTER (WHERE t.iccqar_ques_code::text = 'DEPTH DESCRIPTION'::text) AS "iccqa_DEPTH_DESCRIPTION",

    … 55 total columns being pivotted

   FROM t

  GROUP BY t.iccqa_iccassmt_fk;

 

 

 

This query has been working flawlessly without so much as a glitch every day for the last 2 years or so with of course an increasing amount of data every day (the data grows at about 15-20 thousand records per day). I know the query is not incremental but at under 5mn, it’s simple and works well and can handle inconsistent updates on the data source we use which is pretty dirty.

 

The problem I am facing is that we are trying to move to Postgres V13.3 and this query (and several others like it) is now taking 10x longer (3,000 seconds vs 300 seconds) which makes it completely unacceptable. I created a V13 instance following standard practices with pg_upgrade. I have V11 and V13 working side by side on the exact same hardware: the VM is an 8-core (16 threads) 64GB windows server 2012 R2 machine with SSD storage. I have vacuumed both V11 and V13 databases full freeze analyze. The V13 is an exact backup of the V11 database content-wise. The postgres.conf is the same too and hasn’t been touched in years:

 

 

      "effective_cache_size": "52GB",

      "from_collapse_limit": "24",

      "jit": "off",

      "jit_above_cost": "2e+08",

      "jit_inline_above_cost": "5e+08",

      "jit_optimize_above_cost": "5e+08",

      "join_collapse_limit": "24",

      "max_parallel_workers": "20",

      "max_parallel_workers_per_gather": "8",

      "random_page_cost": "1.1",

      "temp_buffers": "4GB",

      "work_mem": "384MB"

 

 

I have done all my testing with either of the database on while the other was off (shutting down the DB) to make sure there wasn’t any weird interaction. I have read some articles about major changes between 11 and 13 (some of which occurred in 12). In particular, information about the JIT sometimes causing trouble, and the way some CTEs can now be inlined and which can also cause trouble.

  • As you can see from the config above, I have disabled the JIT to make this more comparable with 11 and eliminate that possible source of issues.
  • I have also tried different versions of the query (MATERIALIZED vs NOT MATERIALIZED) with little impact.

 

The plans are pretty much identical too. I checked line by line and couldn’t see anything much different (note that I have a view over this query). Here is the V13 version of the plan:

"[

  {

    "Plan": {

      "Node Type": "Subquery Scan",

      "Parallel Aware": false,

      "Alias": "assessmenticcqapivotview",

      "Startup Cost": 1785087.62,

      "Total Cost": 1785100.62,

      "Plan Rows": 200,

      "Plan Width": 1228,

      "Output": [

        "assessmenticcqapivotview.iccqa_iccassmt_fk",

        "assessmenticcqapivotview.\"iccqa_DEBRIDEMENT_DATE\"",

        "assessmenticcqapivotview.\"iccqa_DEBRIDEMENT_THIS_VISIT\"",

        "assessmenticcqapivotview.\"iccqa_DEBRIDEMENT_TYPE\"",

        "assessmenticcqapivotview.\"iccqa_DEPTH_CM\"",

        "assessmenticcqapivotview.\"iccqa_DEPTH_DESCRIPTION\"",

        "assessmenticcqapivotview.\"iccqa_DOES_PATIENT_HAVE_PAIN_ASSOCIATED_WITH_THIS_WOUND\"",

        "assessmenticcqapivotview.\"iccqa_DRAIN_PRESENT\"",

        "assessmenticcqapivotview.\"iccqa_DRAIN_TYPE\"",

        "assessmenticcqapivotview.\"iccqa_EDGE_SURROUNDING_TISSUE_MACERATION\"",

        "assessmenticcqapivotview.\"iccqa_EDGES\"",

        "assessmenticcqapivotview.\"iccqa_EPITHELIALIZATION\"",

        "assessmenticcqapivotview.\"iccqa_EXUDATE_AMOUNT\"",

        "assessmenticcqapivotview.\"iccqa_EXUDATE_TYPE\"",

        "assessmenticcqapivotview.\"iccqa_GRANULATION_TISSUE\"",

        "assessmenticcqapivotview.\"iccqa_INDICATE_OTHER_TYPE_OF_WOUND_CLOSURE\"",

        "assessmenticcqapivotview.\"iccqa_INDICATE_TYPE\"",

        "assessmenticcqapivotview.\"iccqa_INDICATE_WOUND_CLOSURE\"",

        "assessmenticcqapivotview.\"iccqa_IS_THIS_A_CLOSED_SURGICAL_WOUND_OR_SUSPECTED_DEEP_TISSUE\"",

        "assessmenticcqapivotview.\"iccqa_LENGTH_CM\"",

        "assessmenticcqapivotview.\"iccqa_MEASUREMENTS_TAKEN\"",

        "assessmenticcqapivotview.\"iccqa_NECROTIC_TISSUE_AMOUNT\"",

        "assessmenticcqapivotview.\"iccqa_NECROTIC_TISSUE_TYPE\"",

        "assessmenticcqapivotview.\"iccqa_ODOR\"",

        "assessmenticcqapivotview.\"iccqa_OTHER_COMMENTS_REGARDING_DEBRIDEMENT_TYPE\"",

        "assessmenticcqapivotview.\"iccqa_OTHER_COMMENTS_REGARDING_DRAIN_TYPE\"",

        "assessmenticcqapivotview.\"iccqa_OTHER_COMMENTS_REGARDING_PAIN_INTERVENTIONS\"",

        "assessmenticcqapivotview.\"iccqa_OTHER_COMMENTS_REGARDING_PAIN_QUALITY\"",

        "assessmenticcqapivotview.\"iccqa_OTHER_COMMENTS_REGARDING_REASON_MEASUREMENTS_NOT_TAKEN\"",

        "assessmenticcqapivotview.\"iccqa_PAIN_FREQUENCY\"",

        "assessmenticcqapivotview.\"iccqa_PAIN_INTERVENTIONS\"",

        "assessmenticcqapivotview.\"iccqa_PAIN_QUALITY\"",

        "assessmenticcqapivotview.\"iccqa_PERIPHERAL_TISSUE_EDEMA\"",

        "assessmenticcqapivotview.\"iccqa_PERIPHERAL_TISSUE_INDURATION\"",

        "assessmenticcqapivotview.\"iccqa_REASON_MEASUREMENTS_NOT_TAKEN\"",

        "assessmenticcqapivotview.\"iccqa_RESPONSE_TO_PAIN_INTERVENTIONS\"",

        "assessmenticcqapivotview.\"iccqa_SHAPE\"",

        "assessmenticcqapivotview.\"iccqa_SIGNS_AND_SYMPTOMS_OF_INFECTION\"",

        "assessmenticcqapivotview.\"iccqa_SKIN_COLOR_SURROUNDING_WOUND\"",

        "assessmenticcqapivotview.\"iccqa_STATE\"",

        "assessmenticcqapivotview.\"iccqa_SURFACE_AREA_SQ_CM\"",

        "assessmenticcqapivotview.\"iccqa_TOTAL_NECROTIC_TISSUE_ESCHAR\"",

        "assessmenticcqapivotview.\"iccqa_TOTAL_NECROTIC_TISSUE_SLOUGH\"",

        "assessmenticcqapivotview.\"iccqa_TUNNELING\"",

        "assessmenticcqapivotview.\"iccqa_TUNNELING_SIZE_CM_LOCATION_12_3_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_TUNNELING_SIZE_CM_LOCATION_3_6_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_TUNNELING_SIZE_CM_LOCATION_6_9_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_TUNNELING_SIZE_CM_LOCATION_9_12_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_UNDERMINING\"",

        "assessmenticcqapivotview.\"iccqa_UNDERMINING_SIZE_CM_LOCATION_12_3_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_UNDERMINING_SIZE_CM_LOCATION_3_6_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_UNDERMINING_SIZE_CM_LOCATION_6_9_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_UNDERMINING_SIZE_CM_LOCATION_9_12_O_CLOCK\"",

        "assessmenticcqapivotview.\"iccqa_WIDTH_CM\"",

        "assessmenticcqapivotview.\"iccqa_WOUND_PAIN_LEVEL_WHERE_0_NO_PAIN_AND_10_WORST_POS\""

      ],

      "Plans": [

        {

          "Node Type": "Aggregate",

          "Strategy": "Hashed",

          "Partial Mode": "Simple",

          "Parent Relationship": "Subquery",

          "Parallel Aware": false,

          "Startup Cost": 1785087.62,

          "Total Cost": 1785098.62,

          "Plan Rows": 200,

          "Plan Width": 1260,

          "Output": [

            "t.iccqa_iccassmt_fk",

            "NULL::text",

            "tilda.todate((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DEBRIDEMENT DATE'::text)))::character varying, NULL::date)",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DEBRIDEMENT THIS VISIT'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DEBRIDEMENT TYPE'::text))",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DEPTH (CM)'::text)))::character varying, NULL::real)",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DEPTH DESCRIPTION'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DOES PATIENT HAVE PAIN ASSOCIATED WITH THIS WOUND?'::text))",

            "tilda.toint((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DRAIN PRESENT'::text)))::character varying, NULL::integer)",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'DRAIN TYPE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'EDGE / SURROUNDING TISSUE - MACERATION'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'EDGES'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'EPITHELIALIZATION'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'EXUDATE AMOUNT'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'EXUDATE TYPE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'GRANULATION TISSUE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'INDICATE OTHER TYPE OF WOUND CLOSURE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'INDICATE TYPE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'INDICATE WOUND CLOSURE'::text))",

            "tilda.toint((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'IS THIS A CLOSED SURGICAL WOUND OR SUSPECTED DEEP TISSUE INJURY?'::text)))::character varying, NULL::integer)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'LENGTH (CM)'::text)))::character varying, NULL::real)",

            "tilda.toint((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'MEASUREMENTS TAKEN'::text)))::character varying, NULL::integer)",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'NECROTIC TISSUE AMOUNT'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'NECROTIC TISSUE TYPE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'ODOR'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'OTHER COMMENTS REGARDING DEBRIDEMENT TYPE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'OTHER COMMENTS REGARDING DRAIN TYPE'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'OTHER COMMENTS REGARDING PAIN INTERVENTIONS'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'OTHER COMMENTS REGARDING PAIN QUALITY'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'OTHER COMMENTS REGARDING REASON MEASUREMENTS NOT TAKEN'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'PAIN FREQUENCY'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'PAIN INTERVENTIONS'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'PAIN QUALITY'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'PERIPHERAL TISSUE EDEMA'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'PERIPHERAL TISSUE INDURATION'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'REASON MEASUREMENTS NOT TAKEN'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'RESPONSE TO PAIN INTERVENTIONS'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'SHAPE'::text))",

            "tilda.toint((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'SIGNS AND SYMPTOMS OF INFECTION'::text)))::character varying, NULL::integer)",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'SKIN COLOR SURROUNDING WOUND'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'STATE'::text))",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'SURFACE AREA (SQ CM)'::text)))::character varying, NULL::real)",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'TOTAL NECROTIC TISSUE ESCHAR'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'TOTAL NECROTIC TISSUE SLOUGH'::text))",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'TUNNELING'::text))",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'TUNNELING SIZE(CM)/LOCATION - 12 - 3 O''CLOCK'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'TUNNELING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'TUNNELING SIZE(CM)/LOCATION - 6 - 9 O''CLOCK'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'TUNNELING SIZE(CM)/LOCATION - 9 - 12 O''CLOCK'::text)))::character varying, NULL::real)",

            "max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'UNDERMINING'::text))",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'UNDERMINING SIZE(CM)/LOCATION - 12 - 3 O''CLOCK'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'UNDERMINING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'UNDERMINING SIZE(CM)/LOCATION - 6 - 9 O''CLOCK'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'UNDERMINING SIZE(CM)/LOCATION - 9 - 12 O''CLOCK'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'WIDTH (CM)'::text)))::character varying, NULL::real)",

            "tilda.tofloat((max(t.iccqar_ans_val) FILTER (WHERE ((t.iccqar_ques_code)::text = 'WOUND PAIN LEVEL, WHERE 0 = \"NO PAIN\" AND 10 = \"WORST POSSIBLE PAIN\"'::text)))::character varying, NULL::real)"

          ],

          "Group Key": [

            "t.iccqa_iccassmt_fk"

          ],

          "Planned Partitions": 0,

          "Plans": [

            {

              "Node Type": "Aggregate",

              "Strategy": "Hashed",

              "Partial Mode": "Simple",

              "Parent Relationship": "InitPlan",

              "Subplan Name": "CTE t",

             "Parallel Aware": false,

              "Startup Cost": 1360804.75,

              "Total Cost": 1374830.63,

              "Plan Rows": 1402588,

              "Plan Width": 56,

              "Output": [

                "assessmenticcqa_raw.iccqar_iccassmt_fk",

                "assessmenticcqa_raw.iccqar_ques_code",

                "max((assessmenticcqa_raw.iccqar_ans_val)::text)"

              ],

              "Group Key": [

                "assessmenticcqa_raw.iccqar_iccassmt_fk",

                "assessmenticcqa_raw.iccqar_ques_code"

              ],

              "Planned Partitions": 0,

              "Plans": [

                {

                  "Node Type": "Seq Scan",

                  "Parent Relationship": "Outer",

                  "Parallel Aware": false,

                  "Relation Name": "assessmenticcqa_raw",

                  "Schema": "public",

                  "Alias": "assessmenticcqa_raw",

                  "Startup Cost": 0,

                  "Total Cost": 1256856.62,

                  "Plan Rows": 13859750,

                  "Plan Width": 38,

                  "Output": [

                    "assessmenticcqa_raw.iccqar_iccassmt_fk",

                    "assessmenticcqa_raw.iccqar_ques_code",

                    "assessmenticcqa_raw.iccqar_ques_type",

                    "assessmenticcqa_raw.iccqar_ans_val",

                    "assessmenticcqa_raw.created",

                    "assessmenticcqa_raw.\"lastUpdated\"",

                    "assessmenticcqa_raw.deleted"

                  ],

                  "Filter": "((assessmenticcqa_raw.iccqar_ques_code)::text = ANY ('{\"DEBRIDEMENT DATE\",\"DEBRIDEMENT THIS VISIT\",\"DEBRIDEMENT TYPE\",\"DEPTH (CM)\",\"DEPTH DESCRIPTION\",\"DOES PATIENT HAVE PAIN ASSOCIATED WITH THIS WOUND?\",\"DRAIN PRESENT\",\"DRAIN TYPE\",\"EDGE / SURROUNDING TISSUE - MACERATION\",EDGES,EPITHELIALIZATION,\"EXUDATE AMOUNT\",\"EXUDATE TYPE\",\"GRANULATION TISSUE\",\"INDICATE OTHER TYPE OF WOUND CLOSURE\",\"INDICATE TYPE\",\"INDICATE WOUND CLOSURE\",\"IS THIS A CLOSED SURGICAL WOUND OR SUSPECTED DEEP TISSUE INJURY?\",\"LENGTH (CM)\",\"MEASUREMENTS TAKEN\",\"NECROTIC TISSUE AMOUNT\",\"NECROTIC TISSUE TYPE\",ODOR,\"OTHER COMMENTS REGARDING DEBRIDEMENT TYPE\",\"OTHER COMMENTS REGARDING DRAIN TYPE\",\"OTHER COMMENTS REGARDING PAIN INTERVENTIONS\",\"OTHER COMMENTS REGARDING PAIN QUALITY\",\"OTHER COMMENTS REGARDING REASON MEASUREMENTS NOT TAKEN\",\"PAIN FREQUENCY\",\"PAIN INTERVENTIONS\",\"PAIN QUALITY\",\"PERIPHERAL TISSUE EDEMA\",\"PERIPHERAL TISSUE INDURATION\",\"REASON MEASUREMENTS NOT TAKEN\",\"RESPONSE TO PAIN INTERVENTIONS\",SHAPE,\"SIGNS AND SYMPTOMS OF INFECTION\",\"SKIN COLOR SURROUNDING WOUND\",STATE,\"SURFACE AREA (SQ CM)\",\"TOTAL NECROTIC TISSUE ESCHAR\",\"TOTAL NECROTIC TISSUE SLOUGH\",TUNNELING,\"TUNNELING SIZE(CM)/LOCATION - 12 - 3 O''CLOCK\",\"TUNNELING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK\",\"TUNNELING SIZE(CM)/LOCATION - 6 - 9 O''CLOCK\",\"TUNNELING SIZE(CM)/LOCATION - 9 - 12 O''CLOCK\",UNDERMINING,\"UNDERMINING SIZE(CM)/LOCATION - 12 - 3 O''CLOCK\",\"UNDERMINING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK\",\"UNDERMINING SIZE(CM)/LOCATION - 6 - 9 O''CLOCK\",\"UNDERMINING SIZE(CM)/LOCATION - 9 - 12 O''CLOCK\",\"WIDTH (CM)\",\"WOUND PAIN LEVEL, WHERE 0 = \\\"NO PAIN\\\" AND 10 = \\\"WORST POSSIBLE PAIN\\\"\"}'::text[]))"

                }

              ]

            },

            {

              "Node Type": "CTE Scan",

              "Parent Relationship": "Outer",

              "Parallel Aware": false,

              "CTE Name": "t",

              "Alias": "t",

              "Startup Cost": 0,

              "Total Cost": 28051.76,

              "Plan Rows": 1402588,

              "Plan Width": 552,

              "Output": [

                "t.iccqa_iccassmt_fk",

                "t.iccqar_ques_code",

                "t.iccqar_ans_val"

              ]

            }

          ]

        }

      ]

    },

    "Settings": {

         "version":13.3

      "effective_cache_size": "52GB",

      "from_collapse_limit": "24",

      "jit": "off",

      "jit_above_cost": "2e+08",

      "jit_inline_above_cost": "5e+08",

      "jit_optimize_above_cost": "5e+08",

      "join_collapse_limit": "24",

      "max_parallel_workers": "20",

      "max_parallel_workers_per_gather": "8",

      "random_page_cost": "1.1",

      "temp_buffers": "4GB",

      "work_mem": "384MB"

    },

    "Planning Time": 0.784

  }

]"

 

 

I am out of my wits as to what is causing such a massive slowdown and how I could fix it.

 

Any idea out there?

 

Thank you!

Laurent Hasson

 

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux