Re: Question on COUNT performance
От | REISS Thomas DSIC BIP |
---|---|
Тема | Re: Question on COUNT performance |
Дата | |
Msg-id | 4C3ED1D3.3010203@interieur.gouv.fr обсуждение исходный текст |
Ответ на | Re: Question on COUNT performance (Anders Østergaard Jensen <aj@itersys.dk>) |
Список | pgsql-sql |
Hello,
You can add another JOIN in your function to avoid the test to return either true or false.
Here's an example:
CREATE OR REPLACE FUNCTION f_contact_acl(integer, integer)
RETURNS BOOL AS $$
WITH contact (id_contact) AS (VALUES (1),(2),(3),(7),(8)),
customer (id_customer,id_org) AS (VALUES (4,1),(5,2),(6,3)),
customer_contact (id_contact,id_customer) AS (VALUES (1,4),(2,5),(3,6)),
util (id_user,id_org) AS (VALUES (1,1),(2,2))
SELECT COUNT(*)!=0 FROM contact JOIN customer_contact ON contact.id_contact=customer_contact.id_contact
JOIN customer ON customer.id_customer=customer_contact.id_customer
JOIN util ON customer.id_org=util.id_org
WHERE contact.id_contact=$2 AND util.id_user=$1;
$$ LANGUAGE SQL;
The WITH clause and VALUES was supposed to give a test case. You simply have to remove them and keep the query. So the function becomes a simple SQL function.
Hope this helps :-)
Regards
Jean-Michel Souchard and Thomas Reiss
-------- Message original --------
Sujet : Re: [SQL] Question on COUNT performance
De : Anders Østergaard Jensen <aj@itersys.dk>
Pour : pgsql-sql@postgresql.org
Date : 15/07/2010 02:14
You can add another JOIN in your function to avoid the test to return either true or false.
Here's an example:
CREATE OR REPLACE FUNCTION f_contact_acl(integer, integer)
RETURNS BOOL AS $$
WITH contact (id_contact) AS (VALUES (1),(2),(3),(7),(8)),
customer (id_customer,id_org) AS (VALUES (4,1),(5,2),(6,3)),
customer_contact (id_contact,id_customer) AS (VALUES (1,4),(2,5),(3,6)),
util (id_user,id_org) AS (VALUES (1,1),(2,2))
SELECT COUNT(*)!=0 FROM contact JOIN customer_contact ON contact.id_contact=customer_contact.id_contact
JOIN customer ON customer.id_customer=customer_contact.id_customer
JOIN util ON customer.id_org=util.id_org
WHERE contact.id_contact=$2 AND util.id_user=$1;
$$ LANGUAGE SQL;
The WITH clause and VALUES was supposed to give a test case. You simply have to remove them and keep the query. So the function becomes a simple SQL function.
Hope this helps :-)
Regards
Jean-Michel Souchard and Thomas Reiss
-------- Message original --------
Sujet : Re: [SQL] Question on COUNT performance
De : Anders Østergaard Jensen <aj@itersys.dk>
Pour : pgsql-sql@postgresql.org
Date : 15/07/2010 02:14
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 $$declareuser record;contact record;customer record;beginSELECT INTO customer cust.* FROM contactsJOIN customer_contacts cc ON cc.contact_id = contacts.idJOIN customers cust ON cust.id = cc.customer_idWHERE contacts.id = cid;SELECT INTO user * FROM users WHERE id=uid;if (customer.org_id != user.org_id) thenreturn false;end if;return true;end$$ LANGUAGE 'plpgsql';- Hide quoted text -CREATE OR REPLACE FUNCTION f_customer_acl(uid integer, cid integer)RETURNS BOOL AS $$declareuser_id integer;customer_id integer;user record;customer record;beginuser_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) thenreturn 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 $$DECLAREuser_id integer;doc_id integer;user record;doc record;proj_rel record;BEGINuser_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 matchesif (doc.org_id != user.org_id) thenreturn false;end if;-- If document was created by user, accept itif (doc.user_id_created = user_id) thenreturn true;end if; -- if document is public, accept itif (doc.is_public) thenreturn 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_usersJOIN projects ON project_users.project_id = projects.idJOIN project_documents ON projects.id = project_documents.project_idJOIN documents ON project_documents.document_id = documents.idWHERE documents.id = doc_idAND project_users.user_id = $1;-- acl_count returns the number of allowed relationships to exactly-- this documentreturn 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!
В списке pgsql-sql по дате отправления: