Query with many self-joins is slow

Question

I have a query that involves doing tons of joins to the same table that stores lead data. In campaigns that I have tons of leads (5k) the queries runs very slow. FYI there are 40657335 records in the table that has tons of joins to itself. I have indexes set up. Any one have any suggestion how to fix this?

SELECT to_char(DATE, 'MM/DD/YYYY HH:MI:SS AM') AS "Date"
    ,email AS "Email"
    ,f512.value AS "f512value"
    ,f341.value AS "f341value"
    ,f11.value AS "f11value"
    ,f12.value AS "f12value"
    ,f15.value AS "f15value"
    ,f14.value AS "f14value"
    ,UPPER(f21.value) AS "f21value"
    ,f20.value AS "f20value"
    ,f19.value AS "f19value"
    ,f22.value AS "f22value"
    ,f225.value AS "f225value"
    ,f223.value AS "f223value"
    ,f24.value AS "f24value"
    ,f28.value AS "f28value"
    ,f228.value AS "f228value"
    ,f229.value AS "f229value"
    ,f231.value AS "f231value"
    ,f230.value AS "f230value"
    ,f232.value AS "f232value"
    ,f233.value AS "f233value"
    ,f234.value AS "f234value"
    ,f235.value AS "f235value"
    ,f264.value AS "f264value"
    ,f246.value AS "f246value"
    ,f247.value AS "f247value"
    ,f248.value AS "f248value"
    ,f249.value AS "f249value"
    ,f250.value AS "f250value"
    ,f251.value AS "f251value"
    ,f252.value AS "f252value"
    ,f253.value AS "f253value"
    ,f314.value AS "f314value"
    ,f254.value AS "f254value"
    ,f255.value AS "f255value"
    ,f256.value AS "f256value"
    ,f236.value AS "f236value"
    ,f242.value AS "f242value"
    ,f241.value AS "f241value"
    ,f257.value AS "f257value"
    ,f258.value AS "f258value"
    ,f259.value AS "f259value"
    ,f260.value AS "f260value"
    ,f261.value AS "f261value"
    ,f262.value AS "f262value"
    ,f263.value AS "f263value"
    ,f243.value AS "f243value"
    ,f244.value AS "f244value"
    ,f237.value AS "f237value"
    ,f238.value AS "f238value"
    ,f239.value AS "f239value"
    ,f240.value AS "f240value"
    ,f245.value AS "f245value"
    ,f82.value AS "f82value"
    ,f127.value AS "f127value"
    ,classification AS "Classification"
    ,sent_to AS "Leads Sent To"
    ,delivery_method AS "Delivery Method"
    ,sent_date AS "Date Lead Sent"
    ,source AS "Source"
FROM reg_event_uniques
LEFT JOIN reg_event_fields f512 ON f512.field_id = 512
    AND f512.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f341 ON f341.field_id = 341
    AND f341.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f11 ON f11.field_id = 11
    AND f11.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f12 ON f12.field_id = 12
    AND f12.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f15 ON f15.field_id = 15
    AND f15.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f14 ON f14.field_id = 14
    AND f14.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f21 ON f21.field_id = 21
    AND f21.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f20 ON f20.field_id = 20
    AND f20.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f19 ON f19.field_id = 19
    AND f19.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f22 ON f22.field_id = 22
    AND f22.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f225 ON f225.field_id = 225
    AND f225.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f223 ON f223.field_id = 223
    AND f223.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f24 ON f24.field_id = 24
    AND f24.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f28 ON f28.field_id = 28
    AND f28.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f228 ON f228.field_id = 228
    AND f228.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f229 ON f229.field_id = 229
    AND f229.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f231 ON f231.field_id = 231
    AND f231.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f230 ON f230.field_id = 230
    AND f230.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f232 ON f232.field_id = 232
    AND f232.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f233 ON f233.field_id = 233
    AND f233.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f234 ON f234.field_id = 234
    AND f234.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f235 ON f235.field_id = 235
    AND f235.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f264 ON f264.field_id = 264
    AND f264.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f246 ON f246.field_id = 246
    AND f246.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f247 ON f247.field_id = 247
    AND f247.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f248 ON f248.field_id = 248
    AND f248.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f249 ON f249.field_id = 249
    AND f249.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f250 ON f250.field_id = 250
    AND f250.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f251 ON f251.field_id = 251
    AND f251.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f252 ON f252.field_id = 252
    AND f252.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f253 ON f253.field_id = 253
    AND f253.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f314 ON f314.field_id = 314
    AND f314.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f254 ON f254.field_id = 254
    AND f254.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f255 ON f255.field_id = 255
    AND f255.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f256 ON f256.field_id = 256
    AND f256.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f236 ON f236.field_id = 236
    AND f236.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f242 ON f242.field_id = 242
    AND f242.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f241 ON f241.field_id = 241
    AND f241.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f257 ON f257.field_id = 257
    AND f257.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f258 ON f258.field_id = 258
    AND f258.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f259 ON f259.field_id = 259
    AND f259.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f260 ON f260.field_id = 260
    AND f260.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f261 ON f261.field_id = 261
    AND f261.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f262 ON f262.field_id = 262
    AND f262.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f263 ON f263.field_id = 263
    AND f263.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f243 ON f243.field_id = 243
    AND f243.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f244 ON f244.field_id = 244
    AND f244.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f237 ON f237.field_id = 237
    AND f237.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f238 ON f238.field_id = 238
    AND f238.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f239 ON f239.field_id = 239
    AND f239.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f240 ON f240.field_id = 240
    AND f240.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f245 ON f245.field_id = 245
    AND f245.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f82 ON f82.field_id = 82
    AND f82.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f127 ON f127.field_id = 127
    AND f127.reg_id = reg_event_uniques.reg_event_id
WHERE reg_event_uniques.cid = 3741
    AND (
        UPPER(f21.value) = 'UNITED STATES'
        OR UPPER(f21.value) = 'CANADA'
        OR UPPER(f21.value) != 'UNITED STATES'
        AND UPPER(f21.value) != 'CANADA'
        AND f21.value IS NOT NULL
        OR f21.value IS NULL
        )
    AND (
        reg_event_uniques.classification = 'End user or machine builder'
        OR reg_event_uniques.classification = 'Interested in sponsoring'
        OR reg_event_uniques.classification = 'Automation suppliers'
        )
ORDER BY DATE DESC LIMIT 100;

This is how my table looks:

id | field_id    | value                           | reg_id
---------------------------------------------------------------
 1 | 11          | first name value would go here  |   2234
 2 | 12          | last name value would go here   |   2234
 3 | 13          | middle name vlaue would go here |   2234
 4 | 14          | address 1 value would go here   |   2234
 5 | 15          | address 2 value would go here   |   2234
...
...

so what's the `EXPLAIN` and `EXPLAIN ANALYZE` result? write it on the question also.. — Kokizzu, Apr 16 '15 at 01:54
Exactly what rows is the compound `WHERE` condition on `f21.value` supposed to filter out? It looks to me like a Rube Goldberg way of saying "AND 1". — John Bollinger, Apr 16 '15 at 02:07
What is the reason for the voluminous amount of self joins? Do you want individual id values as columns? If so, consider a [crosstab query](http://www.postgresql.org/docs/current/interactive/tablefunc.html) where you pivot/transpose the data by `field id`? — Parfait, Apr 16 '15 at 02:09
Is (`reg_id`, `field_id`) unique in `reg_event_fields`? If not, then you have serious trouble. If so, do you have a constraint enforcing it? — John Bollinger, Apr 16 '15 at 02:18
@Parfait This table holds lead data for a campaign and a campaign needs to be dynamic so campaign 1 can have 20 fields (first name, last name, address, etc..) but campaign 2 can only have 2 fields. I updated the question to give you an idea how my table looks. — albertski, Apr 16 '15 at 02:27
@albertski, yes, I see that. I'm saying that your where clause has a compound condition on `f21.value` that appears to be useless on account of allowing every possible value. — John Bollinger, Apr 16 '15 at 02:28
@JohnBollinger field_id is a foreign key to my field table. For example 11 in my field table would be first name. reg_id also is a foreign key to another table. — albertski, Apr 16 '15 at 02:28
@JohnBollinger You are right I think I there is an issue there. Thanks. I still don't think that is whats causing the slowness. — albertski, Apr 16 '15 at 02:29
@albertski, that was not my question. I asked whether the *combination* of `field_id` and `reg_id` is required to be *unique* in table `reg_event_fields`. That each one is a foreign key referencing another table has no bearing on the question. — John Bollinger, Apr 16 '15 at 02:30
You have a bunch of columns that contradict your demonstrated table layout. `classification`, `sent_to`, `delivery_method`, etc. You also forgot table qualifications, your query cannot even work. And your question is broken ... — Erwin Brandstetter, Apr 16 '15 at 03:15
@JohnBollinger Oh sorry! Yes the combination of field_id and reg_id are unique. — albertski, Apr 16 '15 at 13:33

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

Proper database schema

This is an incredible question. I checked the calender to make sure April 1st is over.

You have more than 50 joins on a table with 40M rows. Of course this is going to be slow! Quoting @cha, this looks like an EAV model gone very wrong. Read this:

Is there a name for this database structure?

The solution is to remodel your database. Either use a document type like hstore or jsonb or use a wide row with many nullable columns.

Use case for hstore against multiple columns

20 fields like you commented (or 100 fields) that might be NULL cost almost nothing. NULL storage is very cheap. 64 columns with NULL only occupy 8 bytes in the NULL bitmap. As opposed to your model, which is extremely expensive. The overhead for one additional row is at least 28 bytes:

While stuck with this query

All your conditions on f21.value burn down to .. nothing. The field must be either USA or CAN or not (USA or CAN) or NULL - in short: it can be anything at all, it's just noise. Remove it.

If conditions on f21.value would make sense, you would use JOIN instead of LEFT JOIN and move the join to f21 up to the top. With that many joins, Postgres cannot optimize the query properly any more. You have to make sure that joins are written in a useful order already. Don't even try to optimize, it would be a waste of time. Run with set join_collapse_limit = 1:

How to optimize query postgres

SET LOCAL join_collapse_limit = 1;

SELECT to_char(DATE, 'MM/DD/YYYY HH:MI:SS AM') AS "Date"
     , ...
FROM   reg_event_uniques r
JOIN  reg_event_fields f21 ON f21.field_id = 21
    AND f21.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f512 ON f512.field_id = 512
    AND f512.reg_id = reg_event_uniques.reg_event_id
...
LEFT JOIN reg_event_fields f127 ON f127.field_id = 127
    AND f127.reg_id = reg_event_uniques.reg_event_id
WHERE  r.cid = 3741
AND    r.classification IN
        ('End user or machine builder'
       , 'Interested in sponsoring'
       , 'Automation suppliers')
AND    f21.value ...  -- some useful condition
ORDER  BY DATE DESC
LIMIT  100;

score 1 · Answer 2 · answered Apr 16 '15 at 03:25

It is hardly surprising that a query involving a bazillion-way join of huge tables is slow. It is certainly orders of magnitude worse if any of the joins match multiple rows of the inner table to the same row of the outer table.

Supposing that a given registration has at most one value for each field, at least the multiple matches will not occur. You can also rewrite the query without so many joins -- with only one, in fact. Something like this:

SELECT *
FROM (
    SELECT
      reg_event_id
      ,to_char(DATE, 'MM/DD/YYYY HH:MI:SS AM') AS "Date"
      ,email AS "Email"
      ,classification AS "Classification"
      ,sent_to AS "Leads Sent To"
      ,delivery_method AS "Delivery Method"
      ,sent_date AS "Date Lead Sent"
      ,source AS "Source"
    FROM reg_event_uniques
    WHERE cid = 3741
      AND CASE classification
          WHEN 'End user or machine builder' THEN 1
          WHEN 'Interested in sponsoring' THEN 1
          WHEN 'Automation suppliers' THEN 1
          ELSE 0
          END
    ORDER BY reg_event_uniques.date DESC
    LIMIT 100
  ) uniq
  LEFT JOIN (
    SELECT 
      reg_id
      ,MAX(CASE field_id WHEN 1 THEN value END) AS "f1value"
      ,MAX(CASE field_id WHEN 2 THEN value END) AS "f2value"
      ,MAX(CASE field_id WHEN 3 THEN value END) AS "f3value"
      -- ...
    GROUP BY reg_id
  ) fields
    ON fields.reg_id = uniq.reg_event_id

Since your filter conditions for the overall query relied only on columns from the reg_event_uniques table, and since that table is the outermost table in your original query, this version uses an inline view to select only the rows of reg_event_uniques that contribute to the final result. Note that it orders by the base table's date column, not by the date column of the results, as the latter will yield the wrong order (lexicographic). This inline view may or may not be useful, for the query planner can perform this same reorganization on the original query if it is clever enough.

The main advance is to pivot the fields table to a rowset having exactly one row per reg_id, and containing all the corresponding field values as separate columns. It uses an inline view with an aggregate query for this purpose. The use of the MAX() function therein is not especially significant, as for each column it will determine the maximum of at most one non-NULL value. The MIN() function could have been used to exactly the same effect.

Query with many self-joins is slow

2 Answers2

Proper database schema

While stuck with this query