Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 4F3379FB4F4 for ; Fri, 13 Oct 2006 18:17:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 56858-02 for ; Fri, 13 Oct 2006 21:17:33 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.176]) by postgresql.org (Postfix) with ESMTP id 418579FB4F2 for ; Fri, 13 Oct 2006 18:17:32 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so1305528pyd for ; Fri, 13 Oct 2006 14:16:59 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=mNBbsqxEa+jdSWy6ORyhqsgqwLA67QRk02q+gB/lhfMa7x0vG1ZZJFcdSL1Nd8Vzr5C2JSaWO660D2h07eqDbZ6ZtxJM65PIe4n3tvca7Qqlm7fMjttIltEZ1PWYSyD7fjq3s/uPp4lF0vZn701XLJ1VQrzv4VVGWFhAP0/VOCw= Received: by 10.35.98.6 with SMTP id a6mr6514112pym; Fri, 13 Oct 2006 14:16:56 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Fri, 13 Oct 2006 14:16:56 -0700 (PDT) Message-ID: Date: Fri, 13 Oct 2006 16:16:56 -0500 From: "Aaron Bono" To: "tv@fuzzy.cz" Subject: Re: storing access rights in a postgres database Cc: pgsql-sql@postgresql.org In-Reply-To: <1160487514.452ba25a936ea@mail.fuzzy.cz> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_34045_3227684.1160774216613" References: <1160487514.452ba25a936ea@mail.fuzzy.cz> X-Google-Sender-Auth: 0cc89fee0b4039a3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/129 X-Sequence-Number: 26518 ------=_Part_34045_3227684.1160774216613 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/10/06, tv@fuzzy.cz wrote: > > > SELECT id, ( > SELECT allowed FROM rights WHERE user_id = 1 AND ( > (firm_id = projects.firm_id AND project_id = projects.id AND > subproject_id IS NULL) > OR (firm_id = projects.firm_id AND project_id IS NULL) > ) > ORDER BY firm_id, project_id, subproject_id, module_id, allowed DESC > LIMIT 1 > ) as allowed > FROM projects; > > The problem is in the 'LIMIT 1' clause - that's the reason I can't write > that as > a join. > > Does someone else has an idea how to solve this? If needed I can send more > complex examples and some testing data, explain plans, etc. > > I've been thinking about some 'intermediate table' with results of the > subselect, updated by a set of triggers, but maybe there's some better > solution. I think your problem is NOT the LIMIT, it is the fact that you are putting a select inside the select block (your correlated subquery). You should try left outer joins instead: SELECT projects.id, rights.allowed FROM projects LEFT OUTER JOIN rights ON ( ( rights.firm_id = projects.firm_id AND rights.project_id = projects.id AND rights.subproject_id IS NULL ) OR ( rights.firm_id = projects.firm_id AND rights.project_id IS NULL ) ) GROUP BY projects.id, rights.allowed Using correlated subqueries is really bad (IMHO) because it causes your query to perform a select for each row returned. I have never seen a correlated subquery that cannot be refactored into a join (this is a challenge for any of you who disagree - I would love to see a reason to use a correlated subquery). You can get more information about correlated subqueries and performance at http://www.bcarter.com/sap29.htm . Also, I would consider putting a conditional unique constraint to enforce your rule on which fields must be null/not null to help preserve your data integrity. Hope this helps! -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_34045_3227684.1160774216613 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/10/06, tv@fuzzy.cz <tv@fuzzy.cz> wrote:

SELECT id, (
    SELECT allowed FROM rights WHERE user_id = 1 AND (
         (firm_id = projects.firm_id AND project_id = projects.id AND
subproject_id IS NULL)
      OR (firm_id = projects.firm_id AND project_id IS NULL)
    )
    ORDER BY firm_id, project_id, subproject_id, module_id, allowed DESC LIMIT 1
) as allowed
FROM projects;

The problem is in the 'LIMIT 1' clause - that's the reason I can't write that as
a join.

Does someone else has an idea how to solve this? If needed I can send more
complex examples and some testing data, explain plans, etc.

I've been thinking about some 'intermediate table' with results of the
subselect, updated by a set of triggers, but maybe there's some better
solution.

I think your problem is NOT the LIMIT, it is the fact that you are putting a select inside the select block (your correlated subquery).  You should try left outer joins instead:

SELECT
    projects.id,
    rights.allowed
FROM projects
LEFT OUTER JOIN rights ON (
    (
        rights.firm_id = projects.firm_id
        AND rights.project_id = projects.id
        AND rights.subproject_id IS NULL
    ) OR (
        rights.firm_id = projects.firm_id
        AND rights.project_id IS NULL
    )
)
GROUP BY
    projects.id,
    rights.allowed

Using correlated subqueries is really bad (IMHO) because it causes your query to perform a select for each row returned.  I have never seen a correlated subquery that cannot be refactored into a join (this is a challenge for any of you who disagree - I would love to see a reason to use a correlated subquery).

You can get more information about correlated subqueries and performance at http://www.bcarter.com/sap29.htm .

Also, I would consider putting a conditional unique constraint to enforce your rule on which fields must be null/not null to help preserve your data integrity.

Hope this helps!

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_34045_3227684.1160774216613--