From: PFC <lists@boutiquenumerique.com>
To: Michael Ossareh <michael.ossareh@12snap.com>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: SQL Query Performance tips
Date: Sat, 15 Jan 2005 09:26:22 +0100
Message-ID: <opskmxp8pwth1vuj@musicbox> (raw)
In-Reply-To: <5CCF98464386D4119F1200306E0050CC01447BE6@betauk.london.12snap.com>
References: <5CCF98464386D4119F1200306E0050CC01447BE6@betauk.london.12snap.com>
If I understand well a person has all the free weapons which have a level
<= to his own level, and of course all the weapons he bought.
1) get da weapons
One query can only use one index. Bad for you !
Let's split the free and non-free weapons.
1a) free weapons
SELECT weapon_alignment, count(1) as cnt
FROM weapons
WHERE weapon_level < (user_level)
AND weapon_cost = 0
GROUP BY weapon_alignment;
No need for distinct anymore ! Note also that distinct'ing on weapon_name
is a slower than on weapon_id.
You can create an index on (weapon_cost,weapon_level) but I don't think
it'll be useful.
For ultimate speed, as this does not depend on the user_id, only the
level, you can store the results of this in a table, precalculating the
results for all levels (if there are like 10 levels, it'll be a big win).
1b) weapons bought by the user
SELECT w.weapon_alignment, count(1) as cnt
FROM weapons w, user_weapons uw
WHERE w.weapon_id = uw.weapon_id
AND uw.user_id = (the user_id)
AND w.weapon_cost > 0
GROUP BY weapon_alignment;
You'll note that the weapons in 1a) had cose=0 so they cannot appear
here, no need to distinct the two.
2) combine the two
SELECT weapon_alignment, sum(cnt) FROM
(SELECT weapon_alignment, count(1) as cnt
FROM weapons
WHERE weapon_level < (user_level)
AND weapon_cost = 0
GROUP BY weapon_alignment)
UNION ALL
SELECT w.weapon_alignment, count(1) as cnt
FROM weapons w, user_weapons uw
WHERE w.weapon_id = uw.weapon_id
AND uw.user_id = (the user_id)
AND w.weapon_cost > 0
GROUP BY weapon_alignment)
GROUP BY weapon_alignment;
You can also do this :
SELECT weapon_alignment, count(1) as cnt FROM
(SELECT weapon_alignment
FROM weapons
WHERE weapon_level < (user_level)
AND weapon_cost = 0)
UNION ALL
SELECT w.weapon_alignment
FROM weapons w, user_weapons uw
WHERE w.weapon_id = uw.weapon_id
AND uw.user_id = (the user_id)
AND w.weapon_cost > 0)
GROUP BY weapon_alignment;
How does it turn out ?
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: lists@boutiquenumerique.com, michael.ossareh@12snap.com
Subject: Re: SQL Query Performance tips
In-Reply-To: <opskmxp8pwth1vuj@musicbox>
* 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