Re: Question on COUNT performance

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(at)itersys(dot)dk>
Pour : pgsql-sql(at)postgresql(dot)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 $$
> declare
> user record;
> contact record;
> customer record;
> begin
> SELECT INTO customer cust.* FROM contacts
> JOIN customer_contacts cc ON cc.contact_id = contacts.id
> <http://contacts.id>
> JOIN customers cust ON cust.id <http://cust.id> = cc.customer_id
> WHERE contacts.id <http://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';
> - Hide quoted text -
>
> 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
> <http://projects.id>
> JOIN project_documents ON projects.id <http://projects.id> =
> project_documents.project_id
> JOIN documents ON project_documents.document_id = documents.id
> <http://documents.id>
> WHERE documents.id <http://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!