From: REISS Thomas DSIC BIP <thomas.reiss@interieur.gouv.fr>
To: Anders Østergaard Jensen <aj@itersys.dk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question on COUNT performance
Date: Thu, 15 Jul 2010 11:16:03 +0200
Message-ID: <4C3ED1D3.3010203@interieur.gouv.fr> (raw)
In-Reply-To: <AANLkTik_xjNBmZLAwzJ5oG7n_jFUDOF3TAejGBxKOtbv@mail.gmail.com>
References: <AANLkTimyhXoid1bAk2FQZUx9TC3Qgh2Ba__-Fu4B8Bco@mail.gmail.com>
<AANLkTikWZdVzUQlOLRnPvqI0aJ9pNdUtUfeSLiXGIjfg@mail.gmail.com>
<AANLkTimrKXcpemRDgR5g2NaeVkfsbxHTtV_WrkuSzQT-@mail.gmail.com>
<AANLkTimgxuLfBDjlF2tpryygEPoThPtNrdNcUYrL03Eo@mail.gmail.com>
<AANLkTik_xjNBmZLAwzJ5oG7n_jFUDOF3TAejGBxKOtbv@mail.gmail.com>
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
> <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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: thomas.reiss@interieur.gouv.fr, aj@itersys.dk
Subject: Re: Question on COUNT performance
In-Reply-To: <4C3ED1D3.3010203@interieur.gouv.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox