SQL Query Performance tips - Mailing list pgsql-sql

Hi All,

I have a partial table structure as below

tbl_user_mainuser_id pkuser_level references tbl_leveluser_class references tbl_classuser_level references tbl_level

tbl_levellevel_id pklevel_namelevel_points

tbl_weaponweapon_id pkweapon_nameweapon_level references tbl_levelweapon_class references tbl_classweapon_alignment
referencestbl_alignmentweapon_cost 

tbl_classclass_id pkclass_name

tbl_alignmentalignment_id pkalignment_name

tbl_user_weaponuser_id references tbl_userweapon_id references tbl_weapon


I want to know how many weapons a given user has of a particular class or
alignment where the level of the weapons are equal or less than the players
own level. For this I have developed the following SQL:

1 | select alignment.alignment as alignment,
count(distinct(weapon.weapon_name)) as count from
2 |     ( select * from tbl_alignment where alignment_id != 1 ) alignment,
3 |     ( select * from tbl_weapon) weapon,
4 |     ( select * from tbl_user_main where user_id = $user_id ) person
5 | where weapon.weapon_id IN (
6 |     select u.weapon_id
7 |     from tbl_weapon u
8 |     where u.weapon_level <= person.user_level
9 |     and u.cost = 0
10|     or u.weapon_id IN (
11|         select uu.weapon_id
12|         from tbl_user_weapon uu
13|         where uu.user_id = person.user_id
14|     )
15| )
16| and alignment.alignment_id = weapon.weapon_alignment
17| group by alignment.alignment
18| order by alignment.alignment ASC;

To clarify lines 5 through 15 - a weapon can be free or cost some amount. To
track users that have bought a weapon there is the tbl_user_weapon table.
Every purchase gets listed in there. The count must take into account all
free weapons and weapons which the user has purchased. At the moment there
are some 300 weapons.

In the case above the "alignment" with id 1 is a catchall so I disregard it.
$user_id can be any user_id from tbl_user_main. The result of a query such
as this is along the lines of;

alignment | count
----------+-------
Shadow    | 4
Heavenly  | 6

This takes a long time to complete - circa 3 seconds. Which is fine when run
one off - but it appears in a section of a website that will potentially be
accessed a lot and I can see it causing a few issues in terms of table/row
locking as the game app uses these tables a lot. ideally I need it to run a
lot quicker. Can anyone see any ways to speed this up?

I have considered views but these seem to just be a way of aliasing a query
as opposed to the materialised views present in other RDBMS's. Short of
creating another 3rd form table that has

user_id | alignment_id | count

Which would get updated upon each weapon purchase I cannot see a low
overhead way of getting the data. Creating a table such as this would need
to be a last resort as its maintenance will quickly become a headache due to
the number of purchase routes in the project.

Any help is much appreciated.

Thanks,

------------------------------
Michael Ossareh (M²)
Technical Manager
12snap UK Ltd