Bad performance with cascaded deletes

Поиск
Список
Период
Сортировка
От Haug Bürger
Тема Bad performance with cascaded deletes
Дата
Msg-id 5a4cfb77-6095-5230-8019-49f99c9ecefa@zalando.de
обсуждение исходный текст
Ответы Re: Bad performance with cascaded deletes
Re: Bad performance with cascaded deletes
Список pgsql-general
I have an issue with delete performance I can't explain.

Delete on zpg_data.session  (cost=190.51..4491.20 rows=500 width=46)
...
Planning time: 0.222 ms
 Trigger RI_ConstraintTrigger_a_16481 for constraint
availability_cache_ac_session_id_fkey: time=350.116 calls=500

For me this reads like the delete takes 0.2ms and the cascaded delete
takes 350ms. There is a primary key (index) on the availability_cache
table and if I delete it manually deleting with an id is pretty fast.
For me it looks like the trigger doesn't use the primary key/index.

Any ideas why the trigger is slow or how to get a plan for the trigger?

Thanks for help
        Haug


Table "zpg_data.session"
              Column               |           Type           | Nullable
|   Default    | Storage  |
-----------------------------------+--------------------------+----------+--------------+----------+
 id                                | uuid                     | not null
|              | plain    |
 ... rows deleted ...
Indexes:
    "pk_session" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "zpg_data.availability_cache" CONSTRAINT
"availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
REFERENCES zpg_data.session(id) ON DELETE CASCADE


Table "zpg_data.availability_cache"
    Column     |           Type           | Nullable |   Default   |
Storage  |
---------------+--------------------------+----------+-------------+----------+
 ac_session_id | uuid                     | not null |             |
plain    |
 ac_created    | timestamp with time zone | not null |             |
plain    |
 ac_content    | jsonb                    | not null | '{}'::jsonb |
extended |
Indexes:
    "pk_availability_cache" PRIMARY KEY, btree (ac_session_id)
Foreign-key constraints:
    "availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
REFERENCES zpg_data.session(id) ON DELETE CASCADE



В списке pgsql-general по дате отправления:

Предыдущее
От: Merlin Moncure
Дата:
Сообщение: Re: Multiple PostgreSQL instances on one machine
Следующее
От: "a"
Дата:
Сообщение: Does pgAgent support chinese, japanese characters?