From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Question on COUNT performance
Date: Wed, 14 Jul 2010 23:43:35 -0400
Message-ID: <4C3E83E7.4010102@gmail.com> (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>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
It appears that the acl functions use more SELECTs than necessary. For
f_customer_acl(<span class="Apple-style-span"
style="font-family: arial,sans-serif; font-size: 13px; border-collapse: collapse;">uid
integer, cid integer</span>), I might use:<br>
<br>
PERFORM 1 FROM customers JOIN users USING (org_id) WHERE customer_id =
cid and user_id = uid;<br>
RETURN FOUND;<br>
<br>
This still requires one call to f_customer_acl() (and therefore one
SELECT) for each row in plan_events (since that's the way the calling
function is written). If the goal is to count the number of plan_events
a specific user has access rights to, I'm sure you can write a query
that would accomplish that directly. It's beyond my knowledge whether
it makes more sense to do this via these function calls.<br>
<br>
--Lee<br>
<br>
<br>
<pre class="moz-signature" cols="72">--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
</pre>
</body>
</html>
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: lee.hachadoorian@gmail.com
Subject: Re: Question on COUNT performance
In-Reply-To: <4C3E83E7.4010102@gmail.com>
* 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