1

Let me start by stating that my DBMS is PostgreSQL 9.4.

I have a database with, say 30 tables. Each table has a mirror table with historical data, that is the same columns and some timestamps.

To service a webpage, where all kinds of history is filterable and sortable, I need some way to combine these 30 tables, so that I can sort by e.g. the "changed_at" date. In this case, "changed_at" is a column that is common between all the tables. Other columns may be unique to the different tables (which would make the UNIONs a little weird, since I would have to pad with NULL columns to make the tables fit together)

My first idea is to use UNION, e.g.

table1 UNION table2 UNION ... UNION table30

There are some additional requirements, which make these 30 queries non-trivial (each need to join another table to get some values).

Ultimately, this gets pretty slow. So my question is how to improve on this?

  • Can I use indexing to somehow speed things up?
  • Are there an alternative to UNION, that I am not aware of?
  • Will materialized views be the solution?

Since it is the joining that is slow, I would imagine that the materialized view would be a big help here. I know there is the caveat that the materialized view does not represent current data, as it needs to be refreshed. I believe this fits okay with my requirements, depending on how often I can refresh the view (would every 30 minutes be okay?)

I know I may have essentially solved my problem, but I am here for pointers as to what I may have missed.

An concrete example that I tested (with just two unions) can be found below. Each query joins a "comments" table to attach a comment and has a lateral self join to attach the previous version for each row.

(
  SELECT 'control', a.id, a.sys_period, p.sys_period as p_sys_period, b.comment, b.author_id
  FROM controls_control_history_view as a
    -- With comments
    LEFT JOIN log_changecomment as b
      ON referenced_table = 'controls_control_history'
         AND referenced_id = a.id
         AND b.referenced_date = lower(a.sys_period),
    -- With previous
    LATERAL (
    SELECT * FROM controls_control_history_view prev
    WHERE prev.id = a.id AND
          prev.sys_period < a.sys_period
    ORDER BY prev.sys_period DESC
    LIMIT 1
    ) AS p
-- Unioned
) UNION ALL (
  SELECT 'action', a.id, a.sys_period, p.sys_period as p_sys_period, b.comment, NULL
  FROM actions_action_history_view as a
    -- With comments
    LEFT JOIN log_changecomment as b
      ON referenced_table = 'actions_action_history'
         AND referenced_id = a.id
         AND b.referenced_date = lower(a.sys_period),
    -- With previous
    LATERAL (
    SELECT * FROM actions_action_history_view prev
    WHERE prev.id = a.id AND
          prev.sys_period < a.sys_period
    ORDER BY prev.sys_period DESC
    LIMIT 1
    ) AS p
)
ORDER BY sys_period
;

The EXPLAIN ANALYSE output can be found here

  • How do you know it is the joins that are slow? Some example queries and their EXPLAIN (ANALYZE, BUFFERS) output would help a lot. – dezso Aug 10 '16 at 8:53
  • I took the monkey approach. I ran the queries separately and then with union, and it took quite a bit longer with union. So no, the actual joins may not be slow, but the execution of the 30 queries each time definitely is. I'm not sure which concrete queries would be helpful to display here. – Eldamir Aug 10 '16 at 8:57
  • 1
    Will UNION ALL work for you? Do you need to remove duplicates? Sometimes people use UNION even if there aren't expecting duplicates. – vercelli Aug 10 '16 at 9:05
  • 1
    It looks like you could do a SELECT max(sys_period) ... WHERE ... in the LATERAL subqueries, instead of a SELECT * ... LIMIT 1, if I'm not mistaken. – dezso Aug 10 '16 at 9:10
  • 2
    Not that I believe it matters for the question, but I would advise against mixing ansi and comma joins. I suggest you replace , LATERAL with CROSS JOIN LATERAL for readability – Lennart Aug 10 '16 at 9:29

Your Answer

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

Browse other questions tagged or ask your own question.