query/table design help

From: "Ara Anjargolian" <ara818(at)uclink(dot)berkeley(dot)edu>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: query/table design help
Date: 2003-08-05 09:33:17
Message-ID: 000501c35b34$9c945660$6401a8c0@charterpipeline.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.
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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Shridhar Daithankar 2003-08-05 09:57:50 Re: query/table design help
Previous Message Volker Helm 2003-08-05 06:22:58 Re: I can't wait too much: Total runtime 432478.44 msec