Worst performance multiple left join big tables after restore DB

Question

Problem

Database: PostgreSQL 13.8 (Debian 13.8-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
After migrate from DigitalOcean to strong Dedicated Servers we noticed slow performance in some part of the application. Tried vacuum, analyze, reindex. Not tried full vacuum because of lock tables.

Tables size

dieting_dietplan = 119936
dieting_portion = 189444545
dieting_meal = 41467323
dieting_dayevent = 18225193
dieting_daymenu = 4024864
patients_patient = 181563

Tables

Patient:

                                        Table "public.patients_patient"
   Column   |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 id         | uuid                     |           | not null |         | plain   |              | 
 created_at | timestamp with time zone |           |          |         | plain   |              | 
 updated_at | timestamp with time zone |           |          |         | plain   |              | 
 user_id    | integer                  |           |          |         | plain   |              | 
Indexes:
    "patients_patient_pkey" PRIMARY KEY, btree (id)
    "patients_patient_e8701ad4" btree (user_id)

Portion:

                                          Table "public.dieting_portion"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id            | uuid                     |           | not null |         | plain    |              | 
 created_at    | timestamp with time zone |           |          |         | plain    |              | 
 updated_at    | timestamp with time zone |           |          |         | plain    |              | 
 position      | integer                  |           | not null |         | plain    |              | 
 analyzer_data | jsonb                    |           | not null |         | extended |              | 
 product_id    | integer                  |           | not null |         | plain    |              | 
 product_name  | character varying(100)   |           | not null |         | extended |              | 
 amount        | numeric(8,2)             |           | not null |         | main     |              | 
 amount_label  | character varying(100)   |           | not null |         | extended |              | 
 raw           | boolean                  |           | not null |         | plain    |              | 
 meal_id       | uuid                     |           | not null |         | plain    |              | 
 user_id       | integer                  |           |          |         | plain    |              | 
Indexes:
    "dieting_portion_pkey" PRIMARY KEY, btree (id)
    "dieting_por_positio_f064a0_idx" btree ("position", product_name)
    "dieting_por_positio_f064a0_idx_ccnew" btree ("position", product_name)
    "dieting_portion_6894eaee" btree (meal_id)
    "dieting_portion_6894eaee_ccnew" btree (meal_id)
    "dieting_portion_e8701ad4" btree (user_id)
    "dieting_portion_e8701ad4_ccnew" btree (user_id)
    "dieting_portion_f5625f45" btree (product_name)
    "dieting_portion_f5625f45_ccnew" btree (product_name)
    "dieting_portion_pkey_ccnew" UNIQUE, btree (id)
    "dieting_portion_position_f183396b_uniq" btree ("position")
    "dieting_portion_position_f183396b_uniq_ccnew" btree ("position")
    "dieting_portion_product_id_1ac06c8f_uniq" btree (product_id)
    "dieting_portion_product_id_1ac06c8f_uniq_ccnew" btree (product_id)
    "dieting_portion_product_name_fe8ddc5f_like" btree (product_name varchar_pattern_ops)
    "dieting_portion_product_name_fe8ddc5f_like_ccnew" btree (product_name varchar_pattern_ops)

Meal:

                                            Table "public.dieting_meal"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id            | uuid                     |           | not null |         | plain    |              | 
 created_at    | timestamp with time zone |           |          |         | plain    |              | 
 updated_at    | timestamp with time zone |           |          |         | plain    |              | 
 position      | integer                  |           | not null |         | plain    |              | 
 deleted_at    | timestamp with time zone |           |          |         | plain    |              | 
 analyzer_data | jsonb                    |           | not null |         | extended |              | 
 name          | character varying(200)   |           | not null |         | extended |              | 
 recipe        | text                     |           | not null |         | extended |              | 
 meal_type     | smallint                 |           | not null |         | plain    |              | 
 portion_count | smallint                 |           | not null |         | plain    |              | 
 product_names | jsonb                    |           | not null |         | extended |              | 
 meal_attrs    | jsonb                    |           | not null |         | extended |              | 
 day_event_id  | uuid                     |           | not null |         | plain    |              | 
 parent_id     | uuid                     |           |          |         | plain    |              | 
 user_id       | integer                  |           |          |         | plain    |              | 
 source        | uuid                     |           |          |         | plain    |              | 
 checksum      | character varying(32)    |           | not null |         | extended |              | 
Indexes:
    "dieting_meal_pkey" PRIMARY KEY, btree (id)
    "dieting_mea_positio_75f21e_idx" btree ("position", name)
    "dieting_mea_positio_75f21e_idx_ccnew" btree ("position", name)
    "dieting_meal_1ad1fc7d" btree (day_event_id)
    "dieting_meal_1ad1fc7d_ccnew" btree (day_event_id)
    "dieting_meal_226190d9" btree (checksum)
    "dieting_meal_226190d9_ccnew" btree (checksum)
    "dieting_meal_6be37982" btree (parent_id)
    "dieting_meal_6be37982_ccnew" btree (parent_id)
    "dieting_meal_8546dfa4" btree (portion_count)
    "dieting_meal_8546dfa4_ccnew" btree (portion_count)
    "dieting_meal_a9c6ef03" btree (meal_attrs)
    "dieting_meal_a9c6ef03_ccnew" btree (meal_attrs)
    "dieting_meal_b068931c" btree (name)
    "dieting_meal_b068931c_ccnew" btree (name)
    "dieting_meal_checksum_46f2e953_like" btree (checksum varchar_pattern_ops)
    "dieting_meal_checksum_46f2e953_like_ccnew" btree (checksum varchar_pattern_ops)
    "dieting_meal_e8701ad4" btree (user_id)
    "dieting_meal_e8701ad4_ccnew" btree (user_id)
    "dieting_meal_name_1d47f664_like" btree (name varchar_pattern_ops)
    "dieting_meal_name_1d47f664_like_ccnew" btree (name varchar_pattern_ops)
    "dieting_meal_pkey_ccnew" UNIQUE, btree (id)
    "dieting_meal_position_a8c5fbb7_uniq" btree ("position")
    "dieting_meal_position_a8c5fbb7_uniq_ccnew" btree ("position")

Day event:

                                            Table "public.dieting_dayevent"
      Column       |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id                | uuid                     |           | not null |         | plain    |              | 
 created_at        | timestamp with time zone |           |          |         | plain    |              | 
 updated_at        | timestamp with time zone |           |          |         | plain    |              | 
 analyzer_data     | jsonb                    |           | not null |         | extended |              | 
 ref_eng           | smallint                 |           |          |         | plain    |              | 
 ref_cep           | smallint                 |           |          |         | plain    |              | 
 ref_pep           | smallint                 |           |          |         | plain    |              | 
 ref_fep           | smallint                 |           |          |         | plain    |              | 
 nutrient_refs     | jsonb                    |           | not null |         | extended |              | 
 name              | character varying(100)   |           | not null |         | extended |              | 
 start_at          | time without time zone   |           | not null |         | plain    |              | 
 end_at            | time without time zone   |           |          |         | plain    |              | 
 event_type        | smallint                 |           | not null |         | plain    |              | 
 meal_energy_share | smallint                 |           | not null |         | plain    |              | 
 day_menu_id       | uuid                     |           | not null |         | plain    |              | 
 user_id           | integer                  |           |          |         | plain    |              | 
 base_day_event_id | uuid                     |           |          |         | plain    |              | 
Indexes:
    "dieting_dayevent_pkey" PRIMARY KEY, btree (id)
    "dieting_day_start_a_d8efc8_idx" btree (start_at, end_at)
    "dieting_day_start_a_d8efc8_idx_ccnew" btree (start_at, end_at)
    "dieting_dayevent_38e26cbe" btree (day_menu_id)
    "dieting_dayevent_38e26cbe_ccnew" btree (day_menu_id)
    "dieting_dayevent_base_day_event_id_13afee34" btree (base_day_event_id)
    "dieting_dayevent_base_day_event_id_13afee34_ccnew" btree (base_day_event_id)
    "dieting_dayevent_e8701ad4" btree (user_id)
    "dieting_dayevent_e8701ad4_ccnew" btree (user_id)
    "dieting_dayevent_event_type_2258b92e" btree (event_type)
    "dieting_dayevent_event_type_2258b92e_ccnew" btree (event_type)
    "dieting_dayevent_pkey_ccnew" UNIQUE, btree (id)
    "dieting_dayevent_start_at_6f8df923" btree (start_at)
    "dieting_dayevent_start_at_6f8df923_ccnew" btree (start_at)

Day menu:

                                                 Table "public.dieting_daymenu"
           Column           |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
----------------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id                         | uuid                     |           | not null |         | plain    |              | 
 created_at                 | timestamp with time zone |           |          |         | plain    |              | 
 updated_at                 | timestamp with time zone |           |          |         | plain    |              | 
 position                   | integer                  |           |          |         | plain    |              | 
 analyzer_data              | jsonb                    |           | not null |         | extended |              | 
 ref_eng                    | smallint                 |           |          |         | plain    |              | 
 ref_cep                    | smallint                 |           |          |         | plain    |              | 
 ref_pep                    | smallint                 |           |          |         | plain    |              | 
 ref_fep                    | smallint                 |           |          |         | plain    |              | 
 nutrient_refs              | jsonb                    |           | not null |         | extended |              | 
 name                       | character varying(100)   |           | not null |         | extended |              | 
 awakening_at               | time without time zone   |           |          |         | plain    |              | 
 sleeping_at                | time without time zone   |           |          |         | plain    |              | 
 first_meal_after_awakening | smallint                 |           | not null |         | plain    |              | 
 last_meal_before_sleeping  | smallint                 |           | not null |         | plain    |              | 
 diet_plan_id               | uuid                     |           |          |         | plain    |              | 
 user_id                    | integer                  |           |          |         | plain    |              | 
 date                       | date                     |           |          |         | plain    |              | 
 base_day_menu_id           | uuid                     |           |          |         | plain    |              | 
 is_bought                  | boolean                  |           | not null |         | plain    |              | 
 is_in_userbase             | boolean                  |           | not null |         | plain    |              | 
Indexes:
    "dieting_daymenu_pkey" PRIMARY KEY, btree (id)
    "dieting_day_positio_df3d34_idx" btree ("position", name)
    "dieting_daymenu_b068931c" btree (name)
    "dieting_daymenu_base_day_menu_id_3d8de1de" btree (base_day_menu_id)
    "dieting_daymenu_date_a2e9ba0a" btree (date)
    "dieting_daymenu_date_index" btree (date) WHERE date IS NULL
    "dieting_daymenu_date_index_2" btree (date)
    "dieting_daymenu_date_index_3" btree (date, user_id)
    "dieting_daymenu_e8701ad4" btree (user_id)
    "dieting_daymenu_f3738446" btree (diet_plan_id)
    "dieting_daymenu_name_094b5f38_like" btree (name varchar_pattern_ops)
    "dieting_daymenu_position_name_index" btree ("position", name)
    "dieting_daymenu_position_name_index2" btree ("position", name)

Diet plan:

    Table "public.dieting_dietplan"
      Column       |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id                | uuid                     |           | not null |         | plain    |              | 
 created_at        | timestamp with time zone |           |          |         | plain    |              | 
 updated_at        | timestamp with time zone |           |          |         | plain    |              | 
 deleted_at        | timestamp with time zone |           |          |         | plain    |              | 
 ref_eng           | smallint                 |           |          |         | plain    |              | 
 ref_cep           | smallint                 |           |          |         | plain    |              | 
 ref_pep           | smallint                 |           |          |         | plain    |              | 
 ref_fep           | smallint                 |           |          |         | plain    |              | 
 nutrient_refs     | jsonb                    |           | not null |         | extended |              | 
 name              | character varying(200)   |           | not null |         | extended |              | 
 description       | text                     |           | not null |         | extended |              | 
 nutrefset_id      | uuid                     |           |          |         | plain    |              | 
 patient_id        | uuid                     |           |          |         | plain    |              | 
 user_id           | integer                  |           |          |         | plain    |              | 
 copying_day_menus | boolean                  |           | not null |         | plain    |              | 
 copying_diet_plan | boolean                  |           | not null |         | plain    |              | 
Indexes:
    "dieting_dietplan_pkey" PRIMARY KEY, btree (id)
    "dieting_die_created_c3b833_idx" btree (created_at DESC)
    "dieting_dietplan_9f065c57" btree (patient_id)
    "dieting_dietplan_b068931c" btree (name)
    "dieting_dietplan_c06d7788" btree (nutrefset_id)
    "dieting_dietplan_e8701ad4" btree (user_id)
    "dieting_dietplan_name_7114c662_like" btree (name varchar_pattern_ops)

Settings

https://pastebin.com/fRDW8PiV

Invalid indexes (maybe this is problem)

SELECT pg_class.relname 
FROM pg_class, pg_index 
WHERE pg_index.indisvalid = false 
AND pg_index.indexrelid = pg_class.oid;
           relname            
------------------------------
 pg_toast_6861016_index_ccnew
 pg_toast_6861058_index_ccnew

Query

Generated by web framework Django automatically. It was working fine before the database was restored. I would like to change the query code as a last resort:

SELECT 
  "dieting_daymenu"."id", 
  "dieting_daymenu"."created_at", 
  "dieting_daymenu"."updated_at", 
  "dieting_daymenu"."position", 
  "dieting_daymenu"."analyzer_data", 
  "dieting_daymenu"."date", 
  "dieting_daymenu"."base_day_menu_id", 
  "dieting_daymenu"."ref_eng", 
  "dieting_daymenu"."ref_cep", 
  "dieting_daymenu"."ref_pep", 
  "dieting_daymenu"."ref_fep", 
  "dieting_daymenu"."nutrient_refs", 
  "dieting_daymenu"."user_id", 
  "dieting_daymenu"."diet_plan_id", 
  "dieting_daymenu"."name", 
  "dieting_daymenu"."awakening_at", 
  "dieting_daymenu"."sleeping_at", 
  "dieting_daymenu"."first_meal_after_awakening", 
  "dieting_daymenu"."last_meal_before_sleeping", 
  "dieting_daymenu"."is_bought", 
  "dieting_daymenu"."is_in_userbase", 
  "dieting_dietplan"."id", 
  "dieting_dietplan"."created_at", 
  "dieting_dietplan"."updated_at", 
  "dieting_dietplan"."deleted_at", 
  "dieting_dietplan"."ref_eng", 
  "dieting_dietplan"."ref_cep", 
  "dieting_dietplan"."ref_pep", 
  "dieting_dietplan"."ref_fep", 
  "dieting_dietplan"."nutrient_refs", 
  "dieting_dietplan"."patient_id", 
  "dieting_dietplan"."user_id", 
  "dieting_dietplan"."nutrefset_id", 
  "dieting_dietplan"."name", 
  "dieting_dietplan"."description", 
  "dieting_dietplan"."copying_day_menus", 
  "dieting_dietplan"."copying_diet_plan" 
FROM 
  "dieting_daymenu" 
  LEFT OUTER JOIN "dieting_dietplan" ON (
    "dieting_daymenu"."diet_plan_id" = "dieting_dietplan"."id"
  ) 
  LEFT OUTER JOIN "patients_patient" ON (
    "dieting_dietplan"."patient_id" = "patients_patient"."id"
  ) 
WHERE 
  (
    (
      "dieting_dietplan"."user_id" = 55 
      OR "patients_patient"."user_id" = 55 
      OR "dieting_daymenu"."user_id" = 55
    ) 
    AND "dieting_daymenu"."date" IS NULL
  ) 
ORDER BY 
  "dieting_daymenu"."position" ASC, 
  "dieting_daymenu"."name" ASC 
LIMIT 
  10;

Query explain

Randomly, sometimes it will download faster, sometimes over a minute:
First try: https://explain.dalibo.com/plan/gd09b5g1abbgcb8g
Second try: https://explain.dalibo.com/plan/c666e8df1b863dga
Third try: https://explain.dalibo.com/plan/57f00d9eh500197f

Update question

Below explain analyze buffers:

 Limit  (cost=1.27..11262.25 rows=10 width=2082) (actual time=166744.058..213933.918 rows=10 loops=1)
   Buffers: shared hit=1269034 read=442242 dirtied=54
   I/O Timings: read=210465.272
   ->  Nested Loop Left Join  (cost=1.27..2028103.71 rows=1801 width=2082) (actual time=166744.056..213933.910 rows=10 loops=1)
         Filter: ((dieting_dietplan.user_id = 55) OR (patients_patient.user_id = 55) OR (dieting_daymenu.user_id = 55))
         Rows Removed by Filter: 134581
         Buffers: shared hit=1269034 read=442242 dirtied=54
         I/O Timings: read=210465.272
         ->  Nested Loop Left Join  (cost=0.85..1848414.36 rows=182054 width=2082) (actual time=0.044..213298.617 rows=134591 loops=1)
               Buffers: shared hit=757143 read=441447 dirtied=53
               I/O Timings: read=210420.153
               ->  Index Scan using dieting_daymenu_position_name_index2 on dieting_daymenu  (cost=0.43..1671952.27 rows=182054 width=1888) (actual time=0.033..211640.389 rows=134591 loops=1)
                     Filter: (date IS NULL)
                     Rows Removed by Filter: 553628
                     Buffers: shared hit=243472 read=440290 dirtied=53
                     I/O Timings: read=209578.602
               ->  Index Scan using dieting_dietplan_pkey on dieting_dietplan  (cost=0.42..0.97 rows=1 width=194) (actual time=0.011..0.011 rows=1 loops=134591)
                     Index Cond: (id = dieting_daymenu.diet_plan_id)
                     Buffers: shared hit=513671 read=1157
                     I/O Timings: read=841.551
         ->  Index Scan using patients_patient_pkey on patients_patient  (cost=0.42..0.97 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=134591)
               Index Cond: (id = dieting_dietplan.patient_id)
               Buffers: shared hit=511890 read=795
               I/O Timings: read=45.118
 Planning:
   Buffers: shared hit=33
 Planning Time: 0.525 ms
 Execution Time: 213933.964 ms

I don't have analytics from the previous server. The settings of the previous server are similar to the current one.

Answer 1

Duplicate indexes

For starters, it certainly does not help that you duplicated most of your indexes. Your setup is struggling with insufficient cache as indicated by the large numbers of read's in the query plan. Drop dupes before you do anything else. Those add nothing but cost.

"dieting_por_positio_f064a0_idx"       btree ("position", product_name)
"dieting_por_positio_f064a0_idx_ccnew" btree ("position", product_name)  -- dupe
"dieting_portion_6894eaee"       btree (meal_id)
"dieting_portion_6894eaee_ccnew" btree (meal_id)  -- dupe
...

General difficulty

Queries with ORDER BY on some columns, filter on others and and a small LIMIT are always tricky. See:

• Optimize a query with small LIMIT, predicate on one column and order by another
• Can spatial index help a "range - order by - limit" query

With insufficient or misleading statistics Postgres may reckon it can just traverse an index that fits the sort order and find the few rows required soon enough, but then it turns out to be a long journey. Oftentimes it helps to increase the statistics target on key columns, or tinker with some other settings. See:

• Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

Better index

You have this index:

"dieting_daymenu_date_index" btree (date) WHERE date IS NULL

And this one even three times (which adds to the problem):

"dieting_day_positio_df3d34_idx"       btree ("position", name)
"dieting_daymenu_position_name_index"  btree ("position", name)
"dieting_daymenu_position_name_index2" btree ("position", name)

One of the latter triple gets used, but the query plan reveals the problem with it:

           ->  Index Scan using dieting_daymenu_position_name_index2 on dieting_daymenu  (cost=0.43..1671952.27 rows=182054 width=1888) (actual time=0.033..211640.389 rows=134591 loops=1)
                 Filter: (date IS NULL)
                 Rows Removed by Filter: 553628

Meaning, over half a million index tuples are read from the index, but filtered after visiting the heap because they failed the condition (date IS NULL). One big waste of time.

The one index on table dieting_daymenu you really need:

CREATE INDEX dieting_daymenu_position_name_with_date ON dieting_daymenu (position, name)
WHERE date IS NULL;  -- !!!

That should solve the main problem.

"Ugly OR"

With the above index, the next problem may or may not go away already:

     Filter: ((dieting_dietplan.user_id = 55) OR (patients_patient.user_id = 55) OR (dieting_daymenu.user_id = 55))
     Rows Removed by Filter: 134581

If not, you could split up that "ugly OR" into three separate SELECT and UNION them together. Each can then be optimized individually, often leading to dramatically improved performance. See:

• Why is an OR statement slower than UNION?
• PostgreSQL slow performance using "or" operator

(It's an odd query to begin with, that has to check in any / all of the three tables for user_id = 55. Smells like a design problem, and you should only have to check in one ...)

SELECT dm.id, dm.created_at  -- etc.
     , dp.id, dp.created_at  -- etc.
FROM (
   (
   SELECT dm.id, dm.position, dm.name
   FROM   dieting_daymenu dm
   WHERE  dm.date IS NULL
   AND    dm.user_id = 55
   ORDER  BY dm.position, dm.name
   LIMIT  10
   )
   UNION
   (
   SELECT dm.id, dm.position, dm.name
   FROM   dieting_daymenu dm
   JOIN   dieting_dietplan dp ON dp.id = dm.diet_plan_id
   WHERE  dm.date IS NULL
   AND    dp.user_id = 55
   ORDER  BY dm.position, dm.name
   LIMIT  10
   )
   UNION
   (
   SELECT dm.id, dm.position, dm.name
   FROM   dieting_daymenu dm
   JOIN   patients_patient pp ON pp.id = dm.patient_id
   WHERE  dm.date IS NULL
   AND    pp.user_id = 55
   ORDER  BY dm.position, dm.name
   LIMIT  10
   )
   ORDER  BY position, name
   LIMIT  10
   ) sub
JOIN   dieting_daymenu dm USING (id)
LEFT   JOIN dieting_dietplan dp ON dp.id = dm.diet_plan_id;

In this case, depending on the selectivity of user_id in each table, you ideally have one ore more of these indexes (instead of the one suggested above):

For the 1st subselect:

• dieting_daymenu (user_id, position, name) WHERE date IS NULL

For the 2nd subselect:

• dieting_daymenu (diet_plan_id, position, name) WHERE date IS NULL
• dieting_dietplan(user_id, id)

For the 3rd subselect:

• dieting_daymenu (patient_id, position, name) WHERE date IS NULL
• patients_patient(user_id, id)