How to efficiently sort a column from two tables? In our app, we have assignments. We create this by unioning two other tables with a due_date column: checklists and 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?
You might get improved performance by doing the following:


Use UNION ALL instead of UNION (as suggested by Thangamani Eraniyan).
Make sure both tables have an index on due_date (or with due_date as the first column, if there are more columns indexed).
Use the ORDER BY and the LIMIT 20 on the separate queries of the two tables, as well as on the final combined result. The individual queries do have to be subqueries; otherwise, we can't use ORDER BY on them.


The principle is to limit the number of records that need to be looked at in both individual tables. With an index on due_date in both tables, it should be able to find the most recent records quickly. We know that the top 20 records from both tables can't include more than 20 tasks or more than 20 checklists, so if we just grab the top 20 from each table, we've definitely got the top 20 form the combined tables. The final ORDER BY ... LIMIT simply drops out rows 21-40.

Try this; I tested the same basic query via db-fiddle, and it worked (so, barring any cut-and-paste errors combining that with your actual query, this should work as well).

SELECT type, id, due_date FROM 
   (SELECT a.type, t.id, t.due_date
      FROM assignments a
             INNER JOIN tasks t on t.id = a.task_id
     ORDER BY t.due_date ASC
     LIMIT 20) sq1
UNION ALL
SELECT type, id, due_date FROM 
   (SELECT a.type, c.id, c.due_date
      FROM assignments a
             INNER JOIN checklists c on c.id = a.checklist_id
     ORDER BY c.due_date DESC
     LIMIT 20) sq2
 ORDER BY due_date ASC
 LIMIT 20;