0

Its not exactly my use case (would take too long too explain), but imagine a task management application where tasks for employees are displayed in a list along with some information about that task. These are some of the Task types.

  • Simple Note like "take out the trash" or "refill fridge"
  • Customer Support Ticket for a customer
  • Delivery Job with multiple destinations

These jobs can be assigned to multiple employees until someone claims it and later marks it as completed.

Heres the way I would model this:

[1]: https://i.stack.imgur.com/tgbv7.png

Now In order to get all the Information of all the tasks for a given employee, I would first need to join every assignment relevant to the employee, then join a task for each assignment, then for every task, see if there is a relevant entry in any of the task types (keep in mind there are more than the ones in the diagram), then for CustomerSupport the Customer info needs to be joined aswell and for each delivery each destination is joined (as a json array in the query).

Now I am able to create the query necessary for this (it would require some procedural generation though), that is not the problem. I am just worried that this many cascading queries might heavily affect performance, especially as the task types grow, since with DeliveryDestination we are already at O(N³) complexity.

So my question simply is, can I expect PostgreSQL to properly optimize this? Or should I find a different way of modelling it or even cache the customer and delivery information withing the customerSupport and delivery pages?

2
  • 2
    "can I expect PostgreSQL to properly optimize this" <- nobody knows. Run EXPLAIN ANALYZE and see if it does. Sep 14 at 8:38
  • 1
    Do you have a performance problem? If you didn't measure yet, you don't have a performance problem, stop worrying. Sep 14 at 10:40
4

You will have to measure to see if performance is an actual problem. But your intuition about query performance is wrong:

I am just worried that this many cascading queries might heavily affect performance, especially as the task types grow, since with DeliveryDestination we are already at O(N³) complexity.

Your intuition seems to be that a join is of O(n) complexity and a 3-way join therefore would be O(n³). But this depends on a number of factors. If there is an index on the joined column, a lookup might be O(log n) rather than O(n). If there is a materialized view on the join, it might be O(1). There are different join algorithms available (merge join, hash join, nested loops), and the query planner is supposed the selected the best performing approach. It will usually do a good job of it.

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.