Skip to content

Revert SQL function optimization

Adam Hegyi requested to merge ah-revert-sql-function-optimization into master

What does this MR do and why?

This change removes the previously created SQL functions for the primary key lookup optimization. The optimization didn't work out 100% and to fix it we would need to patch AR. I think implementing a solution that works for all cases is too expensive and dangerous to implement: !135196 (comment 1673038005)

Up:

main: == [advisory_lock_connection] object_id: 182760, pg_backend_pid: 1053275
main: == 20231219093121 DropUnusedSqlFunctions: migrating ===========================
main: -- execute("DROP FUNCTION IF EXISTS find_users_by_id")
main:    -> 0.0090s
main: -- execute("DROP FUNCTION IF EXISTS find_namespaces_by_id")
main:    -> 0.0005s
main: -- execute("DROP FUNCTION IF EXISTS find_projects_by_id")
main:    -> 0.0004s
main: == 20231219093121 DropUnusedSqlFunctions: migrated (0.0128s) ==================

main: == [advisory_lock_connection] object_id: 182760, pg_backend_pid: 1053275
ci: == [advisory_lock_connection] object_id: 182960, pg_backend_pid: 1053277
ci: == 20231219093121 DropUnusedSqlFunctions: migrating ===========================
ci: -- execute("DROP FUNCTION IF EXISTS find_users_by_id")
ci:    -> 0.0008s
ci: -- execute("DROP FUNCTION IF EXISTS find_namespaces_by_id")
ci:    -> 0.0003s
ci: -- execute("DROP FUNCTION IF EXISTS find_projects_by_id")
ci:    -> 0.0003s
ci: == 20231219093121 DropUnusedSqlFunctions: migrated (0.0085s) ==================

ci: == [advisory_lock_connection] object_id: 182960, pg_backend_pid: 1053277

Down:

ci: == [advisory_lock_connection] object_id: 182420, pg_backend_pid: 1058417
ci: == 20231219093121 DropUnusedSqlFunctions: reverting ===========================
ci: -- execute("CREATE OR REPLACE FUNCTION find_users_by_id(users_id bigint)\nRETURNS users AS $$\nBEGIN\n  return (SELECT users FROM users WHERE id = users_id LIMIT 1);\nEND;\n$$ LANGUAGE plpgsql STABLE PARALLEL SAFE COST 1;\n")
ci:    -> 0.0109s
ci: -- execute("CREATE OR REPLACE FUNCTION find_namespaces_by_id(namespaces_id bigint)\nRETURNS namespaces AS $$\nBEGIN\n  return (SELECT namespaces FROM namespaces WHERE id = namespaces_id LIMIT 1);\nEND;\n$$ LANGUAGE plpgsql STABLE PARALLEL SAFE COST 1;\n")
ci:    -> 0.0005s
ci: -- execute("CREATE OR REPLACE FUNCTION find_projects_by_id(projects_id bigint)\nRETURNS projects AS $$\nBEGIN\n  return (SELECT projects FROM projects WHERE id = projects_id LIMIT 1);\nEND;\n$$ LANGUAGE plpgsql STABLE PARALLEL SAFE COST 1;\n")
ci:    -> 0.0004s
ci: == 20231219093121 DropUnusedSqlFunctions: reverted (0.0194s) ==================

ci: == [advisory_lock_connection] object_id: 182420, pg_backend_pid: 1058417


main: == [advisory_lock_connection] object_id: 182420, pg_backend_pid: 1058797
main: == 20231219093121 DropUnusedSqlFunctions: reverting ===========================
main: -- execute("CREATE OR REPLACE FUNCTION find_users_by_id(users_id bigint)\nRETURNS users AS $$\nBEGIN\n  return (SELECT users FROM users WHERE id = users_id LIMIT 1);\nEND;\n$$ LANGUAGE plpgsql STABLE PARALLEL SAFE COST 1;\n")
main:    -> 0.0107s
main: -- execute("CREATE OR REPLACE FUNCTION find_namespaces_by_id(namespaces_id bigint)\nRETURNS namespaces AS $$\nBEGIN\n  return (SELECT namespaces FROM namespaces WHERE id = namespaces_id LIMIT 1);\nEND;\n$$ LANGUAGE plpgsql STABLE PARALLEL SAFE COST 1;\n")
main:    -> 0.0006s
main: -- execute("CREATE OR REPLACE FUNCTION find_projects_by_id(projects_id bigint)\nRETURNS projects AS $$\nBEGIN\n  return (SELECT projects FROM projects WHERE id = projects_id LIMIT 1);\nEND;\n$$ LANGUAGE plpgsql STABLE PARALLEL SAFE COST 1;\n")
main:    -> 0.0005s
main: == 20231219093121 DropUnusedSqlFunctions: reverted (0.0152s) ==================

main: == [advisory_lock_connection] object_id: 182420, pg_backend_pid: 1058797
Edited by Adam Hegyi

Merge request reports