cbbrowne
commented
12 years ago By the way, adding the partial index into the live copy would be a not-invasive change, and I expect it would massively improve performance.
Open cbbrowne opened 12 years ago
cbbrowne
commented
12 years ago By the way, adding the partial index into the live copy would be a not-invasive change, and I expect it would massively improve performance.
I have been finding queries against my_ships to be extremely slow.
An EXPLAIN shows why, pretty clearly: schemaverse@db.schemaverse.com-> explain select * from my_ships;
QUERY PLAN
Hash Join (cost=12653.52..13744.93 rows=2070 width=189) Hash Cond: (ship_control.ship_id = ship.id) -> Seq Scan on ship_control (cost=0.00..882.14 rows=37714 width=83) -> Hash (cost=12627.57..12627.57 rows=2076 width=110) -> Seq Scan on ship (cost=0.00..12627.57 rows=2076 width=110) Filter: ((NOT destroyed) AND (player_id = get_player_id("session_user"()))) (6 rows)
None of the indexes are doing any good; it's doing Seq Scans across both tables.
Here is a fix: https://github.com/cbbrowne/Schemaverse/commit/ebb1d6cecddfba6ba237520f3ebedd30f48c45e7
This does the following: a) Adds player_id to ship_control, so that becomes indexable b) Adds an index on that c) Adds a partial index on ship(player_id) with condition "where not destroyed"
Equivalent query plans:
schemaverse@[local]-> explain select * from my_ships;
QUERY PLAN
Nested Loop (cost=4.26..107.91 rows=1 width=178) -> Bitmap Heap Scan on ship (cost=4.26..91.11 rows=1 width=104) Recheck Cond: (NOT destroyed) Filter: (player_id = get_player_id("session_user"())) -> Bitmap Index Scan on live_peoples_ships (cost=0.00..4.26 rows=290 width=0) -> Index Scan using ship_control_pkey on ship_control (cost=0.00..8.52 rows=1 width=78) Index Cond: (ship_id = ship.id) Filter: (player_id = get_player_id("session_user"())) SubPlan 1 -> Index Scan using ship_health_pkey on ship_health sh (cost=0.00..8.27 rows=1 width=4) Index Cond: (ship_id = ship.id) (11 rows)
Actually, the partial index is probably enough to get the big gains.
schemaverse@[local]-> explain select * from my_ships;
QUERY PLAN
Nested Loop (cost=4.26..107.66 rows=1 width=178) -> Bitmap Heap Scan on ship (cost=4.26..91.11 rows=1 width=104) Recheck Cond: (NOT destroyed) Filter: (player_id = get_player_id("session_user"())) -> Bitmap Index Scan on live_peoples_ships (cost=0.00..4.26 rows=290 width=0) -> Index Scan using ship_control_pkey on ship_control (cost=0.00..8.27 rows=1 width=78) Index Cond: (ship_id = ship.id) SubPlan 1 -> Index Scan using ship_health_pkey on ship_health sh (cost=0.00..8.27 rows=1 width=4) Index Cond: (ship_id = ship.id) (10 rows)
That's inducing much the same plan without the extra player_id column on ship_control.
https://github.com/cbbrowne/Schemaverse/commit/a610be6989ef7bfa8d696057d152c7b04beaccd5