Abstrct/Schemaverse — Issue #10: Some performancey things
Author: cbbrowne
Created: 2012-03-02 05:39:00+00:00
State: open

1. location (in the form point(x,y)) has been added to Ships; would be nice to have this on Planets, too.

2. I imagine it might be useful to have a GIST index on location.

3. It would be lovely to have two tables that capture
a) Relative positions of ships to each other, which notably allows capturing distance (e.g. - point(a_x, a_y) <=> point(b_x, b_y).
It sure would be nice to have an index on distance!

This table would need to be captured via firing triggers whenever ships are created or move.

And presumably you'd only be allowed to see tuples where one of the ships belongs to you AND the distance is low enough to allow visibility. (Yep, needs that index on distance!)

Probably...
create table ship_relative_position (
ship_1_id integer,
ship_2_id integer,
primary key (ship_1_id, ship_2_id),
ship_1_owner_id integer,
ship_2_owner_id integer,
ship_1_location point,
ship_2_location point,
distance float
);

b) Ditto for ships relative to planets

There would need to be a couple views to make this useful; the data would make ships_in_range into a query on a single table, which should make queries on it wicked fast.

I'd be happy to take a poke at an implementation, if that seems like a good idea.