I just tested the following SQL statement on our test installation:
SELECT calls,mean_exec_time,query FROM pg_stat_statements WHERE userid = 'nikita_noark5_test_user'::regrole AND mean_exec_time > 300 ORDER BY calls DESC;
I got the following two queries:
708 | 563.1086918968934 | select re1_0.system_id,re1_1.dtype,re1_1.created_by,re1_1.created_date,re1_1.last_modified_by,re1_1.last_modified_date,re1_1.organisation,re1_1.version,re1_0.archived_by,re1_0.archived_date,re1_0.description,re1_0.document_medium_code,re1_0.document_medium_code_name,re1_0.public_title,re1_0.record_id,re1_0.record_class_id,re1_0.record_classified_id,re1_0.record_disposal_id,re1_0.record_file_id,re1_0.record_screening_id,re1_0.record_series_id,re1_0.title,re1_2.document_date,re1_2.due_date,re1_2.freedom_assessment_date,re1_2.loaned_to,re1_2.loaned_date,re1_2.number_of_attachments,re1_2.received_date,re1_2.sent_date,re1_3.document_date,re1_3.due_date,re1_3.freedom_assessment_date,re1_3.loaned_to,re1_3.loaned_date,re1_3.number_of_attachments,re1_3.received_date,re1_3.record_date,re1_3.record_sequence_number,re1_3.record_year,re1_3.records_management_unit,re1_3.system_id,re1_3.registry_entry_number,re1_3.registry_entry_status_code,re1_3.registry_entry_status_code_name,re1_3.registry_entry_type_code,re1_3.registry_entry_type_code_name,re1_3.sent_date,re1_4.administrative_unit,re1_4.case_handler,re1_4.meeting_case_type,re1_4.meeting_record_status,re1_4.meeting_record_type,re1_4.reference_from_meeting_registration_system_id,re1_4.reference_to_meeting_registration_system_id from as_record_entity re1_0 join system_id_entity re1_1 on re1_0.system_id=re1_1.system_id left join sa_record_note re1_2 on re1_0.system_id=re1_2.system_id left join sa_registry_entry re1_3 on re1_0.system_id=re1_3.system_id left join mu_meeting_record re1_4 on re1_0.system_id=re1_4.system_id where re1_1.organisation=$1 offset $2 rows fetch first $3 rows only
20 | 603.93798345 | select re1_0.system_id,re1_1.dtype,re1_1.created_by,re1_1.created_date,re1_1.last_modified_by,re1_1.last_modified_date,re1_1.organisation,re1_1.version,re1_0.archived_by,re1_0.archived_date,re1_0.description,re1_0.document_medium_code,re1_0.document_medium_code_name,re1_0.public_title,re1_0.record_id,re1_0.record_class_id,re1_0.record_classified_id,re1_0.record_disposal_id,re1_0.record_file_id,re1_0.record_screening_id,re1_0.record_series_id,re1_0.title,re1_2.document_date,re1_2.due_date,re1_2.freedom_assessment_date,re1_2.loaned_to,re1_2.loaned_date,re1_2.number_of_attachments,re1_2.received_date,re1_2.sent_date,re1_3.document_date,re1_3.due_date,re1_3.freedom_assessment_date,re1_3.loaned_to,re1_3.loaned_date,re1_3.number_of_attachments,re1_3.received_date,re1_3.record_date,re1_3.record_sequence_number,re1_3.record_year,re1_3.records_management_unit,re1_3.system_id,re1_3.registry_entry_number,re1_3.registry_entry_status_code,re1_3.registry_entry_status_code_name,re1_3.registry_entry_type_code,re1_3.registry_entry_type_code_name,re1_3.sent_date,re1_4.administrative_unit,re1_4.case_handler,re1_4.meeting_case_type,re1_4.meeting_record_status,re1_4.meeting_record_type,re1_4.reference_from_meeting_registration_system_id,re1_4.reference_to_meeting_registration_system_id from as_record_entity re1_0 join system_id_entity re1_1 on re1_0.system_id=re1_1.system_id left join sa_record_note re1_2 on re1_0.system_id=re1_2.system_id left join sa_registry_entry re1_3 on re1_0.system_id=re1_3.system_id left join mu_meeting_record re1_4 on re1_0.system_id=re1_4.system_id where re1_1.organisation=$1 offset $2 rows fetch first $3 rows only
Running explain with $1 as '', $2 ans 0 and $3 as 10, I got this for the first one:
QUERY PLAN ---------------------------------------------------------------------- Limit (cost=1000.42..64154.41 rows=1 width=6638) -> Nested Loop Left Join (cost=1000.42..64154.41 rows=1 width=6638) Join Filter: (re1_0.system_id = re1_4.system_id) -> Nested Loop Left Join (cost=1000.42..64154.39 rows=1 width=4026) Join Filter: (re1_0.system_id = re1_3.system_id) -> Nested Loop Left Join (cost=1000.42..64132.00 rows=1 width=2870) Join Filter: (re1_0.system_id = re1_2.system_id) -> Nested Loop (cost=1000.42..64130.95 rows=1 width=2302) -> Gather (cost=1000.00..64126.51 rows=1 width=64) Workers Planned: 2 -> Parallel Seq Scan on system_id_entity re1_1 (cost=0.00..63126.41 rows=1 width=64) Filter: ((organisation)::text = ''::text) -> Index Scan using as_record_entity_pkey on as_record_entity re1_0 (cost=0.42..4.44 rows=1 width=2254) Index Cond: (system_id = re1_1.system_id) -> Seq Scan on sa_record_note re1_2 (cost=0.00..1.02 rows=2 width=584) -> Seq Scan on sa_registry_entry re1_3 (cost=0.00..15.51 rows=551 width=1156) -> Seq Scan on mu_meeting_record re1_4 (cost=0.00..0.00 rows=1 width=2628) (17 rows)
This is the second one.
QUERY PLAN ----------------------------------------------------------------------- Limit (cost=1000.42..64154.41 rows=1 width=6638) -> Nested Loop Left Join (cost=1000.42..64154.41 rows=1 width=6638) Join Filter: (re1_0.system_id = re1_4.system_id) -> Nested Loop Left Join (cost=1000.42..64154.39 rows=1 width=4026) Join Filter: (re1_0.system_id = re1_3.system_id) -> Nested Loop Left Join (cost=1000.42..64132.00 rows=1 width=2870) Join Filter: (re1_0.system_id = re1_2.system_id) -> Nested Loop (cost=1000.42..64130.95 rows=1 width=2302) -> Gather (cost=1000.00..64126.51 rows=1 width=64) Workers Planned: 2 -> Parallel Seq Scan on system_id_entity re1_1 (cost=0.00..63126.41 rows=1 width=64) Filter: ((organisation)::text = ''::text) -> Index Scan using as_record_entity_pkey on as_record_entity re1_0 (cost=0.42..4.44 rows=1 width=2254) Index Cond: (system_id = re1_1.system_id) -> Seq Scan on sa_record_note re1_2 (cost=0.00..1.02 rows=2 width=584) -> Seq Scan on sa_registry_entry re1_3 (cost=0.00..15.51 rows=551 width=1156) -> Seq Scan on mu_meeting_record re1_4 (cost=0.00..0.00 rows=1 width=2628) (17 rows)
I doubt these are the most important queries to optimize, just wanted to share the finding.