explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nvQM

Settings
# exclusive inclusive rows x rows loops node
1. 135.943 394,339.152 ↓ 0.0 0 1

Finalize GroupAggregate (cost=5,170,842.05..5,174,902.99 rows=31,238 width=56) (actual time=394,339.152..394,339.152 rows=0 loops=1)

  • Group Key: r.source_document
  • Filter: ((count(*) FILTER (WHERE ((f.raw_lead_id IS NOT NULL) OR (s.source_document IS NOT NULL))) > 0) AND (count(*) <> (COALESCE(max(s.standardized), '0'::bigint) + count(*) FILTER (WHERE (f.raw_lead_id IS NOT NULL)))))
  • Rows Removed by Filter: 87234
2. 4,016.116 394,203.209 ↓ 1.4 356,693 1

Sort (cost=5,170,842.05..5,171,466.81 rows=249,904 width=88) (actual time=394,114.883..394,203.209 rows=356,693 loops=1)

  • Sort Key: r.source_document
  • Sort Method: quicksort Memory: 83288kB
3. 180.939 390,187.093 ↓ 1.4 356,693 1

Gather (cost=5,123,134.11..5,148,436.89 rows=249,904 width=88) (actual time=390,047.050..390,187.093 rows=356,693 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
4. 1,336.733 390,006.154 ↓ 1.3 39,633 9

Partial HashAggregate (cost=5,122,384.11..5,122,696.49 rows=31,238 width=88) (actual time=389,985.480..390,006.154 rows=39,633 loops=9)

  • Group Key: r.source_document
5. 1,239.811 388,669.421 ↑ 1.1 4,236,362 9

Hash Left Join (cost=2,564,219.13..5,062,752.09 rows=4,770,562 width=136) (actual time=382,612.614..388,669.421 rows=4,236,362 loops=9)

  • Hash Cond: ((r.source_document)::text = (s.source_document)::text)
6. 2,371.331 5,963.045 ↑ 1.1 4,236,362 9

Hash Left Join (cost=57,271.40..2,495,577.37 rows=4,770,562 width=72) (actual time=1,145.827..5,963.045 rows=4,236,362 loops=9)

  • Hash Cond: (r.raw_lead_id = f.raw_lead_id)
7. 2,451.774 2,451.774 ↑ 1.1 4,236,362 9

Parallel Seq Scan on leads_raw r (cost=0.00..2,378,077.62 rows=4,770,562 width=72) (actual time=0.007..2,451.774 rows=4,236,362 loops=9)

8. 300.865 1,139.940 ↓ 1.0 915,843 9

Hash (cost=45,912.78..45,912.78 rows=908,690 width=16) (actual time=1,139.940..1,139.940 rows=915,843 loops=9)

  • Buckets: 1048576 Batches: 1 Memory Usage: 51123kB
9. 839.075 839.075 ↓ 1.0 915,843 9

Index Only Scan using leads_filtered_pkey on leads_filtered f (cost=0.42..45,912.78 rows=908,690 width=16) (actual time=0.041..839.075 rows=915,843 loops=9)

  • Heap Fetches: 128622
10. 112.607 381,466.565 ↓ 2.7 84,326 9

Hash (cost=2,506,562.37..2,506,562.37 rows=30,829 width=64) (actual time=381,466.565..381,466.565 rows=84,326 loops=9)

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11419kB
11. 29.147 381,353.958 ↓ 2.7 84,326 9

Subquery Scan on s (cost=0.69..2,506,562.37 rows=30,829 width=64) (actual time=0.179..381,353.958 rows=84,326 loops=9)

12. 4,608.040 381,324.811 ↓ 2.7 84,326 9

GroupAggregate (cost=0.69..2,506,254.08 rows=30,829 width=64) (actual time=0.177..381,324.811 rows=84,326 loops=9)

  • Group Key: s_1.source_document
13. 376,716.771 376,716.771 ↑ 1.0 37,196,853 9

Index Only Scan using leads_standardized_source_document_idx on leads_standardized s_1 (cost=0.69..2,319,451.01 rows=37,298,955 width=56) (actual time=0.051..376,716.771 rows=37,196,853 loops=9)

  • Heap Fetches: 16112974