2

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?

2

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;
  • I think Pg is smart enough to handle the LIMITs, this may actually slow it down. But, we'd have to see on the ops dataset. – Evan Carroll Dec 1 '17 at 21:21
  • The issue with setting a limit on a subqueries, is that there is no way to figure out the offset for deeper pages. – Shurik Agulyansky Dec 1 '17 at 22:56
  • @ShurikAgulyansky the question doesn't say anything about offsets. But you can use this method (although it may be slow for high offsets). If one want OFFSET x LIMIT n, they can so LIMIT x+n in the subqueries and OFFSET x LIMIT n in the main query. – ypercubeᵀᴹ Dec 1 '17 at 23:36
  • @ypercubeᵀᴹ This way of offset calculating is not going to work, because after the first page was retrieved you don't know where is was retrieved from. – Shurik Agulyansky Dec 1 '17 at 23:39
  • @ShurikAgulyansky I don't follow. What do you mean with "where it was retrieved"? – ypercubeᵀᴹ Dec 2 '17 at 0:48
1

Here two things that can be help to improve:

  • Instead of UNION, you can use UNION ALL (since you are using different criteria, always record will be different from each other) to avoid unique comparison from Oracle side.
  • Also, you don't need the subquery to do ORDER BY; it automatically applies to the results of the UNION ALL (or UNION).

I hope it may help you.

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 all
  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.