cbbrowne
commented
12 years ago Actually, I suggest taking a look at issue #16 https://github.com/Abstrct/Schemaverse/issues/16 first.
I think the partial index on ships (player_id) where (not destroyed) would provide a significant improvement here. Note that that index precisely matches the filters in use for both Seq Scans below...
schemaverse@db.schemaverse.com-> explain select * from ships_in_range;
QUERY PLAN
Hash Join (cost=25853.74..83202.75 rows=20364 width=58) Hash Cond: (ships_near_ships.second_ship = enemies.id) Join Filter: ((enemies.location <-> players.location) <= (players.range)::double precision) -> Hash Join (cost=12741.74..67947.88 rows=73683 width=28) Hash Cond: (ships_near_ships.first_ship = players.id) -> Seq Scan on ships_near_ships (cost=0.00..46914.54 rows=1510954 width=8) -> Hash (cost=12718.60..12718.60 rows=1851 width=24) -> Seq Scan on ship players (cost=0.00..12718.60 rows=1851 width=24) Filter: ((NOT destroyed) AND (player_id = get_player_id("session_user"()))) -> Hash (cost=12718.60..12718.60 rows=31471 width=54) -> Seq Scan on ship enemies (cost=0.00..12718.60 rows=31471 width=54) Filter: ((NOT destroyed) AND (player_id <> get_player_id("session_user"()))) (12 rows)
The view is a bit too much of a many-way join. https://github.com/cbbrowne/Schemaverse/commit/e1a3877e3de952fa7d6d1cc3ca915d207518dd04
I suggest adding player # to the ships_near_ships table; given that, access to this data becomes way more efficient, as the view can filter out entries not belonging to the player immediately without having to join against the ship table.