Database design

I suggest:

CREATE TABLE matchversion ( matchversion_id int PRIMARY KEY , matchversion text UNIQUE NOT NULL ); CREATE TABLE matchtype ( matchtype_id int PRIMARY KEY , matchtype text UNIQUE NOT NULL ); CREATE TABLE region ( region_id int PRIMARY KEY , region text NOT NULL ); CREATE TABLE match ( match_id bigint PRIMARY KEY , region_id int REFERENCES region , matchtype_id int REFERENCES matchtype , matchversion_id int REFERENCES matchversion ); CREATE TABLE team ( match_id bigint REFERENCES match , team_id integer -- better name ! , winner boolean -- ?! , PRIMARY KEY(match_id, team_id) ); CREATE TABLE champion ( champion_id int PRIMARY KEY , version text , name text ); CREATE TABLE participant ( participant_id serial PRIMARY KEY -- use proper name ! , champion_id int NOT NULL REFERENCES champion , match_id bigint NOT NULL REFERENCES match -- this FK might be redundant , team_id int , magic_damage_dealt_to_champions real , damage_dealt_to_champions real , item0 text -- or integer ?? , item1 text , item2 text , item3 text , item4 text , item5 text , highest_achieved_season_tier text -- integer ?? , FOREIGN KEY (match_id, team_id) REFERENCES team );

More normalization in order to get smaller tables and indexes and faster access. Create lookup-tables for matchversion , matchtype and region and only write a small integer ID in match .

Seems like the columns participant.item0 .. item5 and highestAchievedSeasonTier could be integer , but are defined as text ?

The column team.winner seems to be boolean , but is defined as text .

I also changed the order of columns to be more efficient. Details: Calculating and saving space in PostgreSQL



Query

Building on above modifications and for Postgres 9.3:

SELECT c.name, * FROM ( SELECT p.champion_id , count(p.item0 = '3285' OR NULL) AS it0 , count(p.item1 = '3285' OR NULL) AS it1 , count(p.item2 = '3285' OR NULL) AS it2 , count(p.item3 = '3285' OR NULL) AS it3 , count(p.item4 = '3285' OR NULL) AS it4 , count(p.item5 = '3285' OR NULL) AS it5 FROM matchversion mv CROSS JOIN matchtype mt JOIN match m USING (matchtype_id, matchversion_id) JOIN team t USING (match_id) JOIN participant p USING (match_id, team_id) WHERE mv.matchversion = '5.14' AND mt.matchtype = 'RANKED_SOLO_5x5' AND t.winner = 'True' -- should be boolean GROUP BY p.champion_id ) p JOIN champion c USING (champion_id); -- probably just JOIN ?

Since champion.name is not defined UNIQUE , it's probably wrong to GROUP BY it. It's also inefficient. Use participant.championid instead (and join to champion later if you need the name in the result).

All instances of LEFT JOIN are pointless, since you have predicates on the left tables anyway and / or use the column in GROUP BY .

Parentheses around AND -ed WHERE conditions are not needed.

In Postgres 9.4 or later you could use the new aggregate FILTER syntax instead. Details and alternatives: How can I simplify this game statistics query?



Index

The partial index on team you already have should look like this to allow index-only scans:

CREATE INDEX on team (matchid, id) WHERE winner -- boolean

But from what I see, you might just add a winner column to participant and drop the table team completely (unless there is more to it).

Also, that index is not going to help much, because (telling from your query plan) the table has 800k rows, half of which qualify:

rows=399999 ... Filter: (winner = 'True'::text) ... Rows Removed by Filter: 399999

This index on match will help a little more (later) when you have more different matchtypes and matchversions:

CREATE INDEX on match (matchtype_id, matchversion_id, match_id);

Still, while 100k rows qualify out of 400k, the index is only useful for an index only scan. Otherwise, a sequential scan will be faster. An index typically pays for about selecting 5 % of the table or less.

Your main problem is that you are obviously running a test case with hardly realistic data distribution. With more selective predicates, indexes will be used more readily.

Aside

Make sure you have configured basic Postgres settings like random_page_cost or work_mem etc.