Re: Question on COUNT performance - Mailing list pgsql-sql
| From | Anders Østergaard Jensen |
|---|---|
| Subject | Re: Question on COUNT performance |
| Date | |
| Msg-id | AANLkTimgxuLfBDjlF2tpryygEPoThPtNrdNcUYrL03Eo@mail.gmail.com Whole thread Raw |
| In response to | Re: Question on COUNT performance (Lee Hachadoorian <lee.hachadoorian@gmail.com>) |
| Responses |
Re: Question on COUNT performance
|
| List | pgsql-sql |
Hi all,
Thank you so much for your kind replies. It has all been a great help.
I tried the SELECT COUNT(1) but that didn't yield any improvement, sorry.
Doing the index on f_plan_event_acl( ... ) wont work, as the parameters are frequently shifted (the second parameter denotes the id of a user in another table).
As Mr. Leeuwen rightfully points out, there might be some performance problems in my acl functions (these are basic functions that determine wether or not a user has got access to a certain row in a table or not---fx f_customer_acl(customer_id, user_id) will return true if the user has access to the customer with ID customer_id etc, the same for f_project_acl on projects etc).. I am not great at optimising PL/pgSQL, though I have the assumption that the speed of the procedural language might have a great impact here.
Before I start changing the content of the function that Mr. Leeuwen kindly provided above, can I pleas ask for help on how to optimise the other acl functions first?
CREATE OR REPLACE FUNCTION f_contact_acl(uid integer, cid integer)
RETURNS BOOL AS $$
declare
user record;
contact record;
customer record;
begin
SELECT INTO customer cust.* FROM contacts
JOIN customer_contacts cc ON cc.contact_id = contacts.id
JOIN customers cust ON cust.id = cc.customer_id
WHERE contacts.id = cid;
SELECT INTO user * FROM users WHERE id=uid;
if (customer.org_id != user.org_id) then
return false;
end if;
return true;
end
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION f_customer_acl(uid integer, cid integer)
RETURNS BOOL AS $$
declare
user_id integer;
customer_id integer;
user record;
customer record;
begin
user_id = $1;
customer_id = $2;
SELECT INTO user * FROM users WHERE id=user_id;
SELECT INTO customer * FROM customers WHERE id=customer_id;
-- Assert that org_id matches:
if (customer.org_id != user.org_id) then
return false;
end if;
-- Nothing more to check for:
return true;
end;
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION f_doc_acl(uid integer, did integer)
RETURNS BOOL AS $$
DECLARE
user_id integer;
doc_id integer;
user record;
doc record;
proj_rel record;
BEGIN
user_id := $1;
doc_id := $2;
SELECT INTO user * FROM users WHERE id=user_id;
SELECT INTO doc * FROM documents WHERE id=doc_id;
-- Check that org_id matches
if (doc.org_id != user.org_id) then
return false;
end if;
-- If document was created by user, accept it
if (doc.user_id_created = user_id) then
return true;
end if; -- if document is public, accept it
if (doc.is_public) then
return true;
end if;
-- else, check the project-document relations -- is the
-- user member of a project that allows access to the document?
SELECT INTO proj_rel COUNT(*) AS acl_count FROM project_users
JOIN projects ON project_users.project_id = projects.id
JOIN project_documents ON projects.id = project_documents.project_id
JOIN documents ON project_documents.document_id = documents.id
WHERE documents.id = doc_id
AND project_users.user_id = $1;
-- acl_count returns the number of allowed relationships to exactly
-- this document
return proj_rel.acl_count > 0;
END;
$$ LANGUAGE 'plpgsql';
Would it be more beneficial to drop the functions and rewrite my basic queries first? However, it is a nice feature having all security checks wrapped into a three-four basic functions.
If my design is completely flawed, I am also open to other design suggestions on how to do proper row-based access control.
I am not asking for the complete solution but a few pointers on how to speed this up would be really great. Thanks!