Very slow user list update (OJS 3.2.1-1)

riquemg · July 2, 2020, 1:36pm

I fixed it with @asmecher hint:

github.com/pkp/pkp-lib

Performance issue using LIMIT and OFFSET in search users query

opened 01:52PM - 20 Nov 19 UTC

closed 03:34PM - 12 May 20 UTC

sourcedump

Community:2:Priority

Hello everyone, I don't know if this is a bug, but I have performance problems …due to a query. I'm using OJS 3.1.2.1 on php 7.3, with more than 27 thousand users, and Postgresql 9.6.15 running on different servers (the one that runs Postgresql has 2CPU and 8GB of RAM). When you search for users to enlist them in a journal this query is performed: ``` SELECT DISTINCT u.*, COALESCE(ugl.setting_value, ugpl.setting_value) AS user_given, CASE WHEN ugl.setting_value <> '' THEN ufl.setting_value ELSE ufpl.setting_value END AS user_family FROM users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation') LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings ugl ON (u.user_id = ugl.user_id AND ugl.setting_name = \$1 AND ugl.locale = \$2) LEFT JOIN user_settings ugpl ON (u.user_id = ugpl.user_id AND ugpl.setting_name = \$3 AND ugpl.locale = \$4) LEFT JOIN user_settings ufl ON (u.user_id = ufl.user_id AND ufl.setting_name = \$5 AND ufl.locale = \$6) LEFT JOIN user_settings ufpl ON (u.user_id = ufpl.user_id AND ufpl.setting_name = \$7 AND ufpl.locale = \$8) LEFT JOIN user_settings usgs ON (usgs.user_id = u.user_id AND usgs.setting_name = \$9) LEFT JOIN user_settings usfs ON (usfs.user_id = u.user_id AND usfs.setting_name = \$10) WHERE 1=1 ORDER BY user_family, user_given LIMIT 25 OFFSET 0; ``` I think from 'lib/pkp/classes/security/UserGroupDAO.inc.php'. This query takes several minutes to finish due to the 'LIMIT 25 OFFSET 0'. I know that because I've made some performance tests and when there is no 'LIMIT OFFSET' the execution time drops to 2 seconds. This behavior causes the repeated submission of the query since users, not seeing results, continue to repeat the selection. Maybe some index optimization can help? Anyone has reported a similar issue? Attached the result of explain analyze. Thank you ![explain](https://user-images.githubusercontent.com/20353276/69244184-cc65ff00-0ba4-11ea-92bd-bf6f6ef821ee.png) [Analyze.txt](https://github.com/pkp/pkp-lib/files/3869511/Analyze.txt)

I created the index in the database and it works:

CREATE INDEX user_settings_setting_name_index ON user_settings (setting_name);

Thank you @asmecher