'PostgreSQL - optimizing a part of a nested loop with heavy disk usage
I've uploaded the query (ORM-generated) and the EXPLAIN results into a plan visualizer here, also attaching them below:
Query
SELECT n.id, n.create_date, n.is_bookmarked, n.is_read, n.is_subscribed_sms, n.metadata, n.notification_id, n.update_date, n.user_login, n0.id, n0.actions, n0.available_since, n0.available_until, n0.channels_order, n0.create_date, n0.detailed_url, n0.expires_after, n0.external_entity_data, n0.external_entity_id, n0.external_entity_type_id, n0.mark_as_read_after, n0.notification_type_id, n0.preview, n0.searchable_text, n0.source_type, n0.title, n0.update_date, n0.urgent, n1.id, n1.code, n1.is_support_mobile, n1.title, n1.icon_background_color, n1.icon_color, n1.icon_name
FROM notification_user AS n
INNER JOIN notification AS n0 ON n.notification_id = n0.id
LEFT JOIN notification_type AS n1 ON n0.notification_type_id = n1.id
WHERE ((((((n.user_login = 'login') AND (n0.notification_type_id IS NOT NULL)) AND (n0.available_since <= '2022-03-11T10:41:19.7257456Z')) AND (n0.available_since >= '2020-01-31T21:00:00.0000000Z')) AND (n0.available_since < '2022-03-11T10:41:16.0790000Z')) AND (n1.id = ANY (ARRAY[8, 9, 10, 12, 2, 1, 11, 3]) OR ((n1.id IS NULL) AND (array_position(ARRAY[8, 9, 10, 12, 2, 1, 11, 3], NULL) IS NOT NULL)))) AND (((n0.expires_after IS NULL) OR (n0.expires_after >= '2022-03-11T10:41:19.7257456Z')) OR (n0.external_entity_type_id = 3))
ORDER BY n0.available_since DESC, n.id
LIMIT 20 OFFSET 20EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) output
[
{
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Startup Cost": 14231.32,
"Total Cost": 14231.37,
"Plan Rows": 20,
"Plan Width": 1495,
"Actual Startup Time": 1849.524,
"Actual Total Time": 1849.531,
"Actual Rows": 20,
"Actual Loops": 1,
"Output": ["n.id", "n.create_date", "n.is_bookmarked", "n.is_read", "n.is_subscribed_sms", "n.metadata", "n.notification_id", "n.update_date", "n.user_login", "n0.id", "n0.actions", "n0.available_since", "n0.available_until", "n0.channels_order", "n0.create_date", "n0.detailed_url", "n0.expires_after", "n0.external_entity_data", "n0.external_entity_id", "n0.external_entity_type_id", "n0.mark_as_read_after", "n0.notification_type_id", "n0.preview", "n0.searchable_text", "n0.source_type", "n0.title", "n0.update_date", "n0.urgent", "n1.id", "n1.code", "n1.is_support_mobile", "n1.title", "n1.icon_background_color", "n1.icon_color", "n1.icon_name"],
"Shared Hit Blocks": 16060,
"Shared Read Blocks": 2412,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 1735.434,
"I/O Write Time": 0.000,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 14231.27,
"Total Cost": 14234.07,
"Plan Rows": 1122,
"Plan Width": 1495,
"Actual Startup Time": 1849.518,
"Actual Total Time": 1849.526,
"Actual Rows": 40,
"Actual Loops": 1,
"Output": ["n.id", "n.create_date", "n.is_bookmarked", "n.is_read", "n.is_subscribed_sms", "n.metadata", "n.notification_id", "n.update_date", "n.user_login", "n0.id", "n0.actions", "n0.available_since", "n0.available_until", "n0.channels_order", "n0.create_date", "n0.detailed_url", "n0.expires_after", "n0.external_entity_data", "n0.external_entity_id", "n0.external_entity_type_id", "n0.mark_as_read_after", "n0.notification_type_id", "n0.preview", "n0.searchable_text", "n0.source_type", "n0.title", "n0.update_date", "n0.urgent", "n1.id", "n1.code", "n1.is_support_mobile", "n1.title", "n1.icon_background_color", "n1.icon_color", "n1.icon_name"],
"Sort Key": ["n0.available_since DESC", "n.id"],
"Sort Method": "top-N heapsort",
"Sort Space Used": 158,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 16060,
"Shared Read Blocks": 2412,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 1735.434,
"I/O Write Time": 0.000,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1.00,
"Total Cost": 14195.80,
"Plan Rows": 1122,
"Plan Width": 1495,
"Actual Startup Time": 4.942,
"Actual Total Time": 1838.010,
"Actual Rows": 3917,
"Actual Loops": 1,
"Output": ["n.id", "n.create_date", "n.is_bookmarked", "n.is_read", "n.is_subscribed_sms", "n.metadata", "n.notification_id", "n.update_date", "n.user_login", "n0.id", "n0.actions", "n0.available_since", "n0.available_until", "n0.channels_order", "n0.create_date", "n0.detailed_url", "n0.expires_after", "n0.external_entity_data", "n0.external_entity_id", "n0.external_entity_type_id", "n0.mark_as_read_after", "n0.notification_type_id", "n0.preview", "n0.searchable_text", "n0.source_type", "n0.title", "n0.update_date", "n0.urgent", "n1.id", "n1.code", "n1.is_support_mobile", "n1.title", "n1.icon_background_color", "n1.icon_color", "n1.icon_name"],
"Inner Unique": true,
"Join Filter": "(n0.notification_type_id = n1.id)",
"Rows Removed by Join Filter": 25709,
"Shared Hit Blocks": 16054,
"Shared Read Blocks": 2412,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 1735.434,
"I/O Write Time": 0.000,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1.00,
"Total Cost": 14073.05,
"Plan Rows": 1122,
"Plan Width": 1390,
"Actual Startup Time": 4.910,
"Actual Total Time": 1828.730,
"Actual Rows": 3917,
"Actual Loops": 1,
"Output": ["n.id", "n.create_date", "n.is_bookmarked", "n.is_read", "n.is_subscribed_sms", "n.metadata", "n.notification_id", "n.update_date", "n.user_login", "n0.id", "n0.actions", "n0.available_since", "n0.available_until", "n0.channels_order", "n0.create_date", "n0.detailed_url", "n0.expires_after", "n0.external_entity_data", "n0.external_entity_id", "n0.external_entity_type_id", "n0.mark_as_read_after", "n0.notification_type_id", "n0.preview", "n0.searchable_text", "n0.source_type", "n0.title", "n0.update_date", "n0.urgent"],
"Inner Unique": true,
"Shared Hit Blocks": 16053,
"Shared Read Blocks": 2412,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 1735.434,
"I/O Write Time": 0.000,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "notification_user__user_login_is_bookmarked__index",
"Relation Name": "notification_user",
"Schema": "public",
"Alias": "n",
"Startup Cost": 0.57,
"Total Cost": 4326.67,
"Plan Rows": 4236,
"Plan Width": 75,
"Actual Startup Time": 4.827,
"Actual Total Time": 1737.971,
"Actual Rows": 3979,
"Actual Loops": 1,
"Output": ["n.id", "n.create_date", "n.is_bookmarked", "n.is_read", "n.is_subscribed_sms", "n.metadata", "n.notification_id", "n.update_date", "n.user_login"],
"Index Cond": "(n.user_login = 'login'::text)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 142,
"Shared Read Blocks": 2404,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 1723.787,
"I/O Write Time": 0.000
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "notification__id__seq",
"Relation Name": "notification",
"Schema": "public",
"Alias": "n0",
"Startup Cost": 0.42,
"Total Cost": 2.30,
"Plan Rows": 1,
"Plan Width": 1315,
"Actual Startup Time": 0.021,
"Actual Total Time": 0.021,
"Actual Rows": 1,
"Actual Loops": 3979,
"Output": ["n0.id", "n0.actions", "n0.available_since", "n0.available_until", "n0.channels_order", "n0.create_date", "n0.detailed_url", "n0.expires_after", "n0.external_entity_data", "n0.external_entity_id", "n0.external_entity_type_id", "n0.mark_as_read_after", "n0.notification_type_id", "n0.preview", "n0.searchable_text", "n0.source_type", "n0.title", "n0.update_date", "n0.urgent"],
"Index Cond": "(n0.id = n.notification_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "((n0.notification_type_id IS NOT NULL) AND (n0.available_since <= '2022-03-16 10:41:19.725746'::timestamp without time zone) AND (n0.available_since >= '2020-01-31 21:00:00'::timestamp without time zone) AND (n0.available_since < '2022-03-16 10:41:16.079'::timestamp without time zone) AND ((n0.expires_after IS NULL) OR (n0.expires_after >= '2022-03-15 10:45:19.725746'::timestamp without time zone) OR (n0.external_entity_type_id = 3)))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 15911,
"Shared Read Blocks": 8,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 11.647,
"I/O Write Time": 0.000
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 1.20,
"Plan Rows": 8,
"Plan Width": 105,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.001,
"Actual Rows": 8,
"Actual Loops": 3917,
"Output": ["n1.id", "n1.code", "n1.is_support_mobile", "n1.title", "n1.icon_background_color", "n1.icon_color", "n1.icon_name"],
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "notification_type",
"Schema": "public",
"Alias": "n1",
"Startup Cost": 0.00,
"Total Cost": 1.16,
"Plan Rows": 8,
"Plan Width": 105,
"Actual Startup Time": 0.013,
"Actual Total Time": 0.018,
"Actual Rows": 8,
"Actual Loops": 1,
"Output": ["n1.id", "n1.code", "n1.is_support_mobile", "n1.title", "n1.icon_background_color", "n1.icon_color", "n1.icon_name"],
"Filter": "(n1.id = ANY ('{8,9,10,12,2,1,11,3}'::integer[]))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000
}
]
}
]
}
]
}
]
},
"Planning Time": 110.488,
"Triggers": [
],
"Execution Time": 1849.726
}
]EDIT: added explain (analyze, format text, buffers) output
Limit (cost=13935.44..13935.49 rows=20 width=1495) (actual time=1067.395..1067.401 rows=20 loops=1)
Buffers: shared hit=16024 read=2482
I/O Timings: read=966.699
-> Sort (cost=13935.39..13938.10 rows=1085 width=1495) (actual time=1067.389..1067.397 rows=40 loops=1)
" Sort Key: n0.available_since DESC, n.id"
Sort Method: top-N heapsort Memory: 150kB
Buffers: shared hit=16024 read=2482
I/O Timings: read=966.699
-> Nested Loop (cost=1.00..13901.10 rows=1085 width=1495) (actual time=6.554..1057.998 rows=3891 loops=1)
Join Filter: (n0.notification_type_id = n1.id)
Rows Removed by Join Filter: 25533
Buffers: shared hit=16024 read=2482
I/O Timings: read=966.699
-> Nested Loop (cost=1.00..13782.41 rows=1085 width=1390) (actual time=6.531..1051.030 rows=3891 loops=1)
Buffers: shared hit=16023 read=2482
I/O Timings: read=966.699
-> Index Scan using notification_user__user_login_is_bookmarked__index on notification_user n (cost=0.57..4234.98 rows=4146 width=75) (actual time=6.488..965.443 rows=3987 loops=1)
Index Cond: (user_login = 'login'::text)
Buffers: shared hit=86 read=2468
I/O Timings: read=955.017
-> Index Scan using notification__id__seq on notification n0 (cost=0.42..2.30 rows=1 width=1315) (actual time=0.020..0.020 rows=1 loops=3987)
Index Cond: (id = n.notification_id)
Filter: ((notification_type_id IS NOT NULL) AND (available_since <= '2022-03-11 10:41:19.725746'::timestamp without time zone) AND (available_since >= '2020-01-31 21:00:00'::timestamp without time zone) AND (available_since < '2022-03-11 10:41:16.079'::timestamp without time zone) AND ((expires_after IS NULL) OR (expires_after >= '2022-03-11 10:41:19.725746'::timestamp without time zone) OR (external_entity_type_id = 3)))
Rows Removed by Filter: 0
Buffers: shared hit=15937 read=14
I/O Timings: read=11.682
-> Materialize (cost=0.00..1.20 rows=8 width=105) (actual time=0.000..0.000 rows=8 loops=3891)
Buffers: shared hit=1
-> Seq Scan on notification_type n1 (cost=0.00..1.16 rows=8 width=105) (actual time=0.013..0.015 rows=8 loops=1)
" Filter: (id = ANY ('{8,9,10,12,2,1,11,3}'::integer[]))"
Buffers: shared hit=1
Planning Time: 0.806 ms
Execution Time: 1067.545 msnotification_user table DDL
create table notification_user
(
id bigserial not null
constraint notification_user__id__seq
primary key,
notification_id integer not null
constraint notification_user__notification_id__fkey
references notification
on delete cascade,
user_login text not null,
is_read boolean default false not null,
is_subscribed_sms boolean default false not null,
is_active boolean default true not null,
metadata text,
create_date timestamp not null,
update_date timestamp not null,
is_bookmarked boolean default false not null,
constraint notification_user__notification_id__user_login__key
unique (notification_id, user_login)
);
create index notification_user__notification_id__index
on notification_user (notification_id);
create index notification_user__is_read_user_login_notification_id__index
on notification_user (is_read desc, user_login desc, notification_id asc);
create index notification_user__create_date__index
on notification_user (create_date desc);
create index notification_user__user_login_is_bookmarked__index
on notification_user (user_login, is_bookmarked);notification table DDL
create table notification
(
id serial not null
constraint notification__id__seq
primary key,
external_entity_type_id smallint not null
constraint notification__external_entity_type_id__fkey
references external_entity_type,
external_entity_id text,
external_entity_data text,
title text,
preview text,
create_date timestamp not null,
update_date timestamp not null,
source_type smallint not null
constraint notification__source_type_id__fkey
references source_type,
channels_order text not null,
urgent boolean default false,
available_until timestamp,
available_since timestamp default now() not null,
expires_after timestamp,
notification_type_id integer
constraint notification__notification_type_id__fkey
references notification_type,
detailed_url text,
searchable_text text,
mark_as_read_after timestamp default (now() + '30 days'::interval) not null,
actions jsonb
);
create index notification__available_until__index
on notification (available_until);
create index notification__external_entity_id__entity_type_id__key
on notification (external_entity_type_id, external_entity_id);
create index notification__external_entity_type_id_available_since__index
on notification (notification_type_id asc, available_since desc, available_until desc);
create index notification__notification_type_id_available_since_expires_afte
on notification (notification_type_id asc, available_since desc, expires_after desc);
create unique index notification__notnull_external_entity_id__entity_type_id__idx
on notification (external_entity_id, external_entity_type_id)
where ((external_entity_id IS NOT NULL) AND (external_entity_type_id <> 2));
create index notification__mark_as_read_after__index
on notification (mark_as_read_after desc);notification_type table DDL
create table notification_type
(
id serial not null
constraint notification_type__id__seq
primary key,
title text not null
constraint notification_type__title__unique
unique,
icon_name text,
icon_color text,
icon_background_color text,
code text default ''::text not null
constraint notification_type__code__unique
unique,
is_support_mobile boolean default false not null
);Retrieving 3979 rows in the outer part of the nested loop takes forever, and that's because the required records are all over the place, resulting in a lot of disk reads. Moreover, the required rows from notification_user are almost never found in buffers and have to be retrieved from disk.
In this query, LIMIT and OFFSET are used for paging. The output of the query is only 20 rows after applying LIMIT, however, all the 3979 (or more, depending on the user in question) rows have to be read from disk.
So far, I have two ideas how to optimize the performance of this query:
- Cluster
notification_userby user_login, so that the records for the outer part of the nested loop take fewer disk reads to retrieve. - Denormalize data to remove the joins of
notification_useronnotificationandnotification_type. If I understand correctly, it would then be possible to create an index supporting both the WHERE and the ORDER BY clause, so that during execution the rows would be fetched in index order, stopping after the LIMIT condition is satisfied, without reading all the 3979 rows from disk.
Are my ideas valid? Maybe I'm missing an easier way of optimizing the query?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|