Re: Multi-column join + aggregate subquery resulting in infinite run time - Mailing list pgsql-novice

Hey gang,

I thought I knew what I was doing with queries, but this has more-or-less ruined my 4th of July holiday so I think it's high time to ask for help.  I imagine it's something trivial and obvious to you.

I've got 3 tables in question here:

prod_account_details => 538k records

line_number PK,

acct_id integer, -- nearly unique, but not quite; acct_id PLUS address is unique

address varchar(100),

entity_name varchar(100),

(... a few other fields ...)

btree index on acct_id, hash index on address, also btree on (acct_id, address)

prod_customer_profiles => 532k records, nearly 1:1 with account details

acct_id integer, -- corresponds to account_details.acct_id

match_input_address varchar(100), -- corresponds to account_details.address

(... lots of other fields ...)

btree index on acct_id, hash index on match_input_address, also btree on (acct_id, match_input_address)

prod_dunning_letters => 518k records, 1:M with account_details (covering ~181k acct_ids)

line_number PK,

acct_id integer, -- FK to account_details, not formally via constraint but semantically

(... a few other fields ...)

btree index on acct_id

--- Queries ---

So firstly, what works:

(1)

SELECT ad.acct_id, ad.entity_name, ad.address, acx.flag_infobase_match

FROM prod_account_details ad

INNER JOIN prod_customer_profiles acx ON ad.acct_id = acx.acct_id 

AND ad.address = acx.match_input_address;

...returns the 532k matched records in 3.0 seconds.

Now if we INNER join to the dunning_letters table via a grouped subquery:

(2)

SELECT ad.acct_id, ad.entity_name, ad.address, acx.flag_infobase_match, dl.num_records AS dl_count

FROM prod_account_details ad

INNER JOIN prod_customer_profiles acx ON ad.acct_id = acx.acct_id 

AND ad.address = acx.match_input_address

INNER JOIN (SELECT count(*) AS num_records, acct_id FROM prod_dunning_letters GROUP BY acct_id ) dl ON ad.acct_id = dl.acct_id;

... it returns fine too, 161k records in 1.3 seconds.  

But now if I do something as simple as changing that second join (to the subquery) to a LEFT JOIN, it blows up. i.e. I want all valid accounts, appended with the count of records in the dunning_letters table if any exist:

(3)

SELECT ad.acct_id, ad.entity_name, ad.address, acx.flag_infobase_match, dl.num_records AS dl_count

FROM prod_account_details ad

INNER JOIN prod_customer_profiles acx ON ad.acct_id = acx.acct_id 

AND ad.address = acx.match_input_address

LEFT JOIN (SELECT count(*) AS num_records, acct_id FROM prod_dunning_letters GROUP BY acct_id ) dl ON ad.acct_id = dl.acct_id;

What happens is, this runs for 20+ seconds, and then starts returning ~100 records every 10 seconds, which at the volume I'm talking about (>500k) would take forever to return.  And next up I need to bolt on the counts for some other, bigger tables too, so we're quickly approaching heat-death-of-the-universe amounts of runtime here.

Now what's interesting is, if I relax the join on the first two tables, i.e. drop the address part of the join and only do the acct_id one,

(4)

SELECT ad.acct_id, ad.entity_name, ad.address, acx.flag_infobase_match, dl.num_records AS dl_count

FROM prod_account_details ad

INNER JOIN prod_customer_profiles acx ON ad.acct_id = acx.acct_id 

-- AND ad.address = acx.match_input_address

LEFT JOIN (SELECT count(*) AS num_records, acct_id FROM prod_dunning_letters GROUP BY acct_id ) dl ON ad.acct_id = dl.acct_id;

...it starts returning immediately and spools 695k records in 4.7 seconds.  The problem is that we need the address part of the join in there, because there are some duplicate acct_ids on both sides so we get a bunch of cartesian-join junk.

--- EXPLAIN query plans ---

So here's the Explain for query (1), with just the two tables:

Gather  (cost=102995.20..123944.32 rows=1 width=41)
  Workers Planned: 2
  ->  Parallel Hash Join  (cost=101995.20..122944.22 rows=1 width=41)
        Hash Cond: ((ad.acct_id = acx.acct_id) AND (ad.address = (acx.match_input_address)::text))
        ->  Parallel Seq Scan on prod_account_details ad  (cost=0.00..14226.43 rows=224343 width=39)
        ->  Parallel Hash  (cost=97096.88..97096.88 rows=224288 width=26)
              ->  Parallel Seq Scan on prod_customer_profiles acx  (cost=0.00..97096.88 rows=224288 width=26)

Now, when we add on the third table, its strategy for that join changes.  Here's the Explain for (2), with the inner join that works:

Gather  (cost=22500.40..83636.43 rows=1 width=49)
  Workers Planned: 2
  ->  Nested Loop  (cost=21500.40..82636.33 rows=1 width=49)
        ->  Hash Join  (cost=21500.40..40427.74 rows=56285 width=54)
              Hash Cond: (ad.acct_id = prod_dunning_letters.acct_id)
              ->  Parallel Seq Scan on prod_account_details ad  (cost=0.00..14226.43 rows=224343 width=39)
              ->  Hash  (cost=19345.40..19345.40 rows=123920 width=15)
                    ->  GroupAggregate  (cost=0.42..18106.20 rows=123920 width=15)
                          Group Key: prod_dunning_letters.acct_id
                          ->  Index Only Scan using prod_dunning_letters_acct_id_idx on prod_dunning_letters  (cost=0.42..14269.36 rows=519529 width=7)
        ->  Index Scan using prod_customer_profiles_match_input_address_idx on prod_customer_profiles acx  (cost=0.00..0.74 rows=1 width=26)
              Index Cond: ((match_input_address)::text = ad.address)
              Filter: (ad.acct_id = acct_id)

But then change it to a LEFT JOIN from query (3), and it changes to:

Nested Loop Left Join  (cost=102995.63..144838.72 rows=1 width=49)
  Join Filter: (ad.acct_id = prod_dunning_letters.acct_id)
  ->  Gather  (cost=102995.20..123944.32 rows=1 width=41)
        Workers Planned: 2
        ->  Parallel Hash Join  (cost=101995.20..122944.22 rows=1 width=41)
              Hash Cond: ((ad.acct_id = acx.acct_id) AND (ad.address = (acx.match_input_address)::text))
              ->  Parallel Seq Scan on prod_account_details ad  (cost=0.00..14226.43 rows=224343 width=39)
              ->  Parallel Hash  (cost=97096.88..97096.88 rows=224288 width=26)
                    ->  Parallel Seq Scan on prod_customer_profiles acx  (cost=0.00..97096.88 rows=224288 width=26)
  ->  GroupAggregate  (cost=0.42..18106.20 rows=123920 width=15)
        Group Key: prod_dunning_letters.acct_id
        ->  Index Only Scan using prod_dunning_letters_acct_id_idx on prod_dunning_letters  (cost=0.42..14269.36 rows=519529 width=7)

Just reading that, I can't readily tell why #2 finishes fast and #3 runs without end.  If the difference was JUST the use of a left join, then why does #4 finish easily too, just with a single-column join between the first two tables?

This has befuddled the rest of my experimentation with it, so I'd really appreciate some insight.

-Steve