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@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 $$

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';

- 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

    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!