In our app, we have assignments. We create this by unioning two other tables with a
tasks. We want to order
assignments by the
due_dates. Right now, we are doing something like this:
select * from ( select a.type, t.id, t.due_date from assignments a inner join tasks t on t.id = a.task_id where a.type = 'Task' and a.user_id = 'X' union select a.type, c.id, c.due_date from assignments a inner join checklists c on c.id = a.checklist_id where a.type = 'Checklist' and a.user_id 'X' ) order by due_date desc limit 20
Obviously, this goes very slow because it needs to generate all the task and checklist assignments (which can be in the 100000s) and then sort them and grab the top 20.
Adding an index doesn't help because the due_date field is in separate tables.
We looked at table inheritance, but it seems to just be syntactic sugar for unions, so doesn't really help.
Also, because there are so many assignments, it seems like materialized views wouldn't help because they'd have to be refreshed every 100ms or so.
What is a good way to optimize this type of query?