I have a table
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:
FROM permissions
WHERE   party_id in (4, 7, 11, 26)
        permission = 'permission8'
        ((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?

Another alternative is to combine the key_name and key_value fields into a
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.
So, from a design standpoint, what is the the best way to go, should I have
two fields key_name, and key_value,
or just one field key. And how should I index this table best.  I guess the
fundamental question here is, is it faster
to check a varchar(60) field for equality, or to check two check an integer
and then a varchar(30). Or does having
one varchar field replace an integer and a varchar field, allow for some
nice optimization not practical otherwise (i.e a 3-column index).

I'd greatly appreciate any insight into this matter.
-Ara Anjargolian

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to