Re: query/table design help - Mailing list pgsql-performance

From Shridhar Daithankar
Subject Re: query/table design help
Date
Msg-id 200308051527.50790.shridhar_daithankar@nospam.persistent.co.in
Whole thread Raw
In response to query/table design help  ("Ara Anjargolian" <ara818@uclink.berkeley.edu>)
List pgsql-performance
On Tuesday 05 August 2003 15:03, Ara Anjargolian wrote:
> I have a table
> permissions
> with the fields (party_id integer, permission varchar, key_name varchar,
> key_value integer)
> for which I need to a query to see if a person has permission to carry out
> a particular action.
> The query looks like:
> SELECT 1
> FROM permissions
> WHERE   party_id in (4, 7, 11, 26)
>     AND
>         permission = 'permission8'
>         AND
>         ((key_name = 'keyname8' AND key_value = 223) OR
>          (key_name = 'keyname1' AND key_value = 123) OR
>          (key_name = 'keyname5' AND key_value = 212) OR
>          (key_name = 'keyname7' AND key_value = 523) OR
>          (key_name = 'keyname0' AND key_value = 123) OR
>          (key_name = 'keyname10' AND key_value = 400));
>
> would a permissions(party_id, permission) index work best here?
> or should I index all 4 columns?
>
> Also,
> Another alternative is to combine the key_name and key_value fields into a
> varchar
> field key (e. g. 'keyname8=223'), in which case the equilalent query would
> just check
> 1 field 6 times instead of having 6 ANDstatements.
>
> I expect the table to have about 1 million rows at the most, and I need
> this query to run as fast
> as possible since it will be run many, many times.

I would suggest a 3 column table with party id, action and permission. Index
on partyid and action.

If table is static enough clustering should help.

 But this is one of many possible ways to design it. There could be other
details that can affect this decision.

 Shridhar


pgsql-performance by date:

Previous
From: "Ara Anjargolian"
Date:
Subject: query/table design help
Next
From: Jeff
Date:
Subject: Some vacuum & tuning help