5

(edit: see end for a simpler example)

I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), which means that for these records a mix of parent/child fields must be used for filtering and display.

I have identified four different parent-child relationships and created views for them:

  • caselist_no_specials: not a child record, use record data as is; 116106 rows total.
  • caselist_disputes_with_ipr: child record; 138 rows total.
  • caselist_mark_children: child record; 18132 rows total.
  • caselist_design_children: child record; 671 rows total.

The results from these views do not overlap and together cover 100% of the table.

When I select a union of all of them, and separately filter each view, the query takes about 9 ms. Selecting a union of all views and filtering the result of that takes about 500 ms.

I have also tested this without views, inlining the queries they contain, which did not produce a measurable improvement.

This is the fast query (explain):

  SELECT  c.*
    FROM  caselist_no_specials c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_disputes_with_ipr c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_mark_children c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_design_children c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

ORDER BY  sort_nr,
          id;

As you can see, the join and filter is duplicated for each view. Trying to avoid the duplication yielded this query, which takes a lot longer (explain):

  SELECT  x.*
    FROM  (
              SELECT * FROM caselist_no_specials
              UNION ALL
              SELECT * FROM caselist_disputes_with_ipr
              UNION ALL
              SELECT * FROM caselist_mark_children
              UNION ALL
              SELECT * FROM caselist_design_children
          ) x
    JOIN  case_clients cacl ON cacl.case_id = x.main_id
   WHERE  cacl.client_id = 12046
ORDER BY  x.sort_nr,
          x.id;

Is it possible to somehow let PostgreSQL know that the filter/join on the outer query can be applied to the inner subquery?

Or is there any other way to avoid filtering each view separately? The user-facing form for this query has over 20 filter fields, and there can be JOINs with up to 14 additional tables.

PostgreSQL is version 9.4.7 running on Linux.


EDIT: I've created a much simplified example, simply partitioning the original table using 3 views, but not involving other tables (and the query plans for both variants). In retrospect, this is what I should have used as an example in the first place.

  • could you add execution plan. I had opposite experience in MS SQL server where it as way faster to do whole union table first and then filter. – simplexio Apr 27 '16 at 11:48
  • The query plans are linked for each query (see "explain"). I have also added a much simpler example at the end, including query plans. Basically, the slow query performs three full seq scans, then a hash join on the result, while the fast query combines index scans on two tables, then just appends the results. – Zilk Apr 27 '16 at 13:00
3

What you're asking the DB to do in Query one is: Give me ALL from table A FILTERED Give me ALL from table B FILTERED Give me ALL from table C FILTERED Give me ALL from table D FILTERED And then Union.

In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really enable you to index anything, obviously runs slower. (It has nothing to do with the Server variant or the OS).

  • 1
    Well, yes, that much was clear. But is there a solution? Is filtering each subquery separately really the only way to improve the performance? As mentioned, there could be up to 20 filters and 14 joins that would have to be duplicated. – Zilk Apr 27 '16 at 9:55
  • Pretty much yes. If you want to create better portability try using a parameter like @client_id, so it would read better. Alternative solution would be to Joins all the tables to the base table "case_clients" and select from there (and then you get wide table, not a deeper one). – Hila DG Apr 27 '16 at 22:45
1

The results from these views do not overlap and together cover 100% of the table.

What keeps you from just querying the underlying table? Should be fastest:

SELECT  x.*
FROM    cases x
JOIN    case_clients cacl ON cacl.case_id = x.main_id
WHERE   cacl.client_id = 12046
ORDER   BY x.sort_nr, x.id;
  • For some rows, the values are taken from somewhere else instead of the actual record. For example, in the 3rd and 4th views in the original example, there is a parent record which contains most (but not all) of the data; the case_clients table also only points to the parent. -- An alternative design would copy all those values and references between parent and child records; this would make searching easier but introduce consistency problems. – Zilk May 1 '16 at 11:57

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.