explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xm5b

Settings
# exclusive inclusive rows x rows loops node
1. 22,362.583 738,188.941 ↓ 2.1 715,565 1

GroupAggregate (cost=28,242,219.43..28,715,722.74 rows=347,359 width=89) (actual time=695,459.414..738,188.941 rows=715,565 loops=1)

  • Group Key: ncm_1.id, ncm_1.attorney_name
2. 56,061.261 715,826.358 ↓ 1.4 50,988,923 1

Sort (cost=28,242,219.43..28,335,878.01 rows=37,463,434 width=50) (actual time=695,458.446..715,826.358 rows=50,988,923 loops=1)

  • Sort Key: ncm_1.id, ncm_1.attorney_name
  • Sort Method: external merge Disk: 3348896kB
3. 20,149.168 659,765.097 ↓ 1.4 50,988,923 1

Hash Join (cost=17,908,098.24..22,249,021.53 rows=37,463,434 width=50) (actual time=513,854.379..659,765.097 rows=50,988,923 loops=1)

  • Hash Cond: (ncm_other.attorney_cluster_id = ncm_1.id)
4. 100,368.935 381,940.421 ↓ 1.0 51,110,087 1

Hash Join (cost=15,490,704.76..19,265,683.00 rows=51,016,190 width=33) (actual time=256,176.108..381,940.421 rows=51,110,087 loops=1)

  • Hash Cond: (ncm_other.id = ucm_other.id)
  • Join Filter: ((NOT ncm_other.filter_out) OR (ucm_other.source = ANY ('{lex_metadata,lex_sigblock}'::text[])))
  • Rows Removed by Join Filter: 18423306
5. 5,539.706 25,408.588 ↓ 1.0 69,533,393 1

Append (cost=0.00..1,913,973.08 rows=69,471,673 width=42) (actual time=0.220..25,408.588 rows=69,533,393 loops=1)

6. 0.057 0.057 ↓ 0.0 0 1

Seq Scan on normalized_counsel_mention ncm_other (cost=0.00..0.00 rows=1 width=57) (actual time=0.057..0.057 rows=0 loops=1)

7. 19,868.825 19,868.825 ↓ 1.0 69,533,393 1

Seq Scan on normalized_counsel_mention_20191023174259 ncm_other_1 (cost=0.00..1,566,614.72 rows=69,471,672 width=42) (actual time=0.159..19,868.825 rows=69,533,393 loops=1)

8. 23,542.114 256,162.898 ↓ 1.0 78,176,744 1

Hash (cost=14,057,331.56..14,057,331.56 rows=78,073,056 width=22) (actual time=256,162.897..256,162.898 rows=78,176,744 loops=1)

  • Buckets: 2097152 Batches: 64 Memory Usage: 81656kB
9. 232,620.784 232,620.784 ↓ 1.0 78,176,744 1

Seq Scan on unnormalized_counsel_mention ucm_other (cost=0.00..14,057,331.56 rows=78,073,056 width=22) (actual time=1.106..232,620.784 rows=78,176,744 loops=1)

10. 734.233 257,675.508 ↓ 2.8 715,565 1

Hash (cost=2,414,204.97..2,414,204.97 rows=255,081 width=41) (actual time=257,675.508..257,675.508 rows=715,565 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 62557kB
11. 0.000 256,941.275 ↓ 2.8 715,565 1

Gather (cost=1,000.57..2,414,204.97 rows=255,081 width=41) (actual time=2.337..256,941.275 rows=715,565 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 249,086.946 257,136.370 ↓ 2.2 238,522 3

Nested Loop (cost=0.57..2,387,696.87 rows=106,284 width=41) (actual time=1.400..257,136.370 rows=238,522 loops=3)

13. 75.276 8,048.904 ↓ 3.3 477,609 3

Parallel Append (cost=0.00..1,234,453.29 rows=144,733 width=34) (actual time=0.310..8,048.904 rows=477,609 loops=3)

14. 7,973.626 7,973.626 ↓ 3.3 477,609 3

Parallel Seq Scan on normalized_counsel_mention_20191023174259 ncm_1 (cost=0.00..1,233,729.62 rows=144,732 width=34) (actual time=0.307..7,973.626 rows=477,609 loops=3)

  • Filter: (id = attorney_cluster_id)
  • Rows Removed by Filter: 22700189
15. 0.002 0.002 ↓ 0.0 0 1

Parallel Seq Scan on normalized_counsel_mention ncm (cost=0.00..0.00 rows=1 width=49) (actual time=0.001..0.002 rows=0 loops=1)

  • Filter: (id = attorney_cluster_id)
16. 0.520 0.520 ↓ 0.0 0 1,432,826

Index Scan using unnormalized_counsel_mention_pkey on unnormalized_counsel_mention ucm (cost=0.57..7.96 rows=1 width=22) (actual time=0.520..0.520 rows=0 loops=1,432,826)

  • Index Cond: (id = ncm_1.id)
  • Filter: ((NOT ncm_1.filter_out) OR (source = ANY ('{lex_metadata,lex_sigblock}'::text[])))
  • Rows Removed by Filter: 1
Planning time : 69.465 ms
Execution time : 739,357.588 ms