Split events into private/global tables
Opened this issue · 3 comments
Splitting them into separate tables allows handling the queries quite a lot more efficiently.
Notably, instead of having a (nasty-to-performance, Seq Scan-inducing) series-of-OR clauses in my_event, it becomes series of
UNION ALL subqueries, each of which is quite a bit more efficient.
I am definitely down with implementing anything that will make events useful. I have been a bit distant the last week or so but I have some time lined up this week to work on the game. I will work on adding this, the fleet script execution time/error events, and what you mentioned to help speed up my_ships (adding player_id to ship_control w\ and index).
Public DB's down at the moment, so I can't offer up samples/query plans, but it looks likely to me that the use of CTEs for the event view may have been a little bit too clever.
I was trying to query for how long fleet scripts have been running, lately, hence
select * from my_events where action = 'FLEET_SUCCESS' where tic > (select last_value from tic_seq) - 50;
In principle, both the tic restriction and the action restriction ought to offer excellent opportunity to restrict things to the last 50 fleet script entries.
Unfortunately, it doesn't seem that the filters on action or tic are getting pushed down into the 3 UNION ALL portions of the query, so this reverted into a trio of Seq Scans on event, and winds up timing out.
As cool as CTE is, it seems to me that you may want to turn it back into a simpler trio of UNION ALL subqueries; I think that'll be able to take advantage of the indexes rather more readily.
Here's a patch that creates 3 views:
- My self-inflicted events
- Other-inflicted events
- Public events
Grab any indexes that you like...