explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 60N9

Settings
# exclusive inclusive rows x rows loops node
1. 1,758.683 5,128.927 ↑ 1.0 47,910 1

Nested Loop Left Join (cost=155,190.26..307,327.73 rows=48,527 width=1,104) (actual time=1,909.147..5,128.927 rows=47,910 loops=1)

2.          

CTE matches

3. 33.180 46.135 ↓ 2.2 33,362 1

HashAggregate (cost=21,163.46..21,312.17 rows=14,871 width=40) (actual time=31.003..46.135 rows=33,362 loops=1)

  • Group Key: rec.dataset_id, rec.company_id
4. 9.813 12.955 ↓ 2.2 33,963 1

Bitmap Heap Scan on record rec (cost=1,260.27..21,046.85 rows=15,548 width=27) (actual time=3.277..12.955 rows=33,963 loops=1)

  • Recheck Cond: (dataset_id = 1396)
  • Filter: (company_id IS NOT NULL)
  • Rows Removed by Filter: 13947
  • Heap Blocks: exact=868
5. 3.142 3.142 ↑ 1.0 47,910 1

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,256.38 rows=48,527 width=0) (actual time=3.142..3.142 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
6.          

CTE dataset_parents

7. 14.439 136.948 ↑ 4.4 2,355 1

Hash Join (cost=6,199.40..7,966.70 rows=10,428 width=48) (actual time=119.075..136.948 rows=2,355 loops=1)

  • Hash Cond: ((r_2.company_id = t.company_id) AND (r_2.dataset_id = r2.dataset_id))
8. 4.679 4.679 ↓ 2.3 33,362 1

CTE Scan on matches r_2 (cost=0.00..297.42 rows=14,797 width=40) (actual time=0.001..4.679 rows=33,362 loops=1)

  • Filter: (company_id IS NOT NULL)
9. 4.466 117.830 ↑ 8.9 10,619 1

Hash (cost=4,327.90..4,327.90 rows=94,100 width=16) (actual time=117.830..117.830 rows=10,619 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1272kB
10. 40.011 113.364 ↑ 8.9 10,619 1

Hash Join (cost=405.23..4,327.90 rows=94,100 width=16) (actual time=51.404..113.364 rows=10,619 loops=1)

  • Hash Cond: (t.parent_id = r2.company_id)
11. 22.062 22.062 ↓ 1.0 188,217 1

Seq Scan on group_parent t (cost=0.00..3,147.99 rows=188,199 width=12) (actual time=0.024..22.062 rows=188,217 loops=1)

12. 6.447 51.291 ↓ 22.4 33,362 1

Hash (cost=386.65..386.65 rows=1,487 width=8) (actual time=51.291..51.291 rows=33,362 loops=1)

  • Buckets: 65536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1816kB
13. 16.691 44.844 ↓ 22.4 33,362 1

HashAggregate (cost=371.78..386.65 rows=1,487 width=8) (actual time=40.509..44.844 rows=33,362 loops=1)

  • Group Key: r2.dataset_id, r2.company_id
14. 28.153 28.153 ↓ 2.2 33,362 1

CTE Scan on matches r2 (cost=0.00..297.42 rows=14,871 width=8) (actual time=0.002..28.153 rows=33,362 loops=1)

15.          

CTE cc

16. 120.876 174.749 ↓ 2.2 33,362 1

HashAggregate (cost=21,163.46..21,312.17 rows=14,871 width=16) (actual time=116.844..174.749 rows=33,362 loops=1)

  • Group Key: m.dataset_id, m.company_id
17. 47.173 53.873 ↓ 2.2 33,963 1

Bitmap Heap Scan on record m (cost=1,260.27..21,046.85 rows=15,548 width=8) (actual time=6.915..53.873 rows=33,963 loops=1)

  • Recheck Cond: (dataset_id = 1396)
  • Filter: (company_id IS NOT NULL)
  • Rows Removed by Filter: 13947
  • Heap Blocks: exact=868
18. 6.700 6.700 ↑ 1.0 47,910 1

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,256.38 rows=48,527 width=0) (actual time=6.700..6.700 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
19.          

CTE dp

20. 32.961 599.857 ↓ 3,396.3 33,963 1

Subquery Scan on d (cost=21,557.56..21,558.01 rows=10 width=26) (actual time=441.404..599.857 rows=33,963 loops=1)

21. 91.490 566.896 ↓ 3,396.3 33,963 1

WindowAgg (cost=21,557.56..21,557.81 rows=10 width=32) (actual time=441.385..566.896 rows=33,963 loops=1)

22. 94.554 475.406 ↓ 3,396.3 33,963 1

Sort (cost=21,557.56..21,557.58 rows=10 width=20) (actual time=441.358..475.406 rows=33,963 loops=1)

  • Sort Key: m_1.company_id, m_1.number
  • Sort Method: quicksort Memory: 3599kB
23. 57.252 380.852 ↓ 3,396.3 33,963 1

Hash Join (cost=1,595.79..21,557.39 rows=10 width=20) (actual time=291.110..380.852 rows=33,963 loops=1)

  • Hash Cond: (m_1.company_id = cc.company_id)
24. 32.741 39.167 ↓ 2.2 33,963 1

Bitmap Heap Scan on record m_1 (cost=1,260.27..21,046.85 rows=15,548 width=12) (actual time=6.647..39.167 rows=33,963 loops=1)

  • Recheck Cond: (dataset_id = 1396)
  • Filter: (company_id IS NOT NULL)
  • Rows Removed by Filter: 13947
  • Heap Blocks: exact=868
25. 6.426 6.426 ↑ 1.0 47,910 1

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,256.38 rows=48,527 width=0) (actual time=6.426..6.426 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
26. 34.694 284.433 ↓ 450.8 33,362 1

Hash (cost=334.60..334.60 rows=74 width=16) (actual time=284.433..284.433 rows=33,362 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2076kB
27. 249.739 249.739 ↓ 450.8 33,362 1

CTE Scan on cc (cost=0.00..334.60 rows=74 width=16) (actual time=116.867..249.739 rows=33,362 loops=1)

  • Filter: (dataset_id = 1396)
28.          

CTE p

29. 8.418 223.434 ↑ 1.8 2,185 1

Hash Join (cost=2,175.03..2,641.24 rows=3,866 width=118) (actual time=208.008..223.434 rows=2,185 loops=1)

  • Hash Cond: (m_2.company_id = r_3.parent_id)
30. 38.066 38.066 ↓ 2.2 33,362 1

CTE Scan on matches m_2 (cost=0.00..297.42 rows=14,871 width=36) (actual time=31.007..38.066 rows=33,362 loops=1)

31. 1.766 176.950 ↓ 42.0 2,185 1

Hash (cost=2,174.38..2,174.38 rows=52 width=94) (actual time=176.950..176.950 rows=2,185 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 306kB
32. 1.550 175.184 ↓ 42.0 2,185 1

Nested Loop (cost=905.38..2,174.38 rows=52 width=94) (actual time=140.801..175.184 rows=2,185 loops=1)

33. 1.846 160.524 ↓ 42.0 2,185 1

Nested Loop (cost=904.96..1,734.98 rows=52 width=53) (actual time=140.790..160.524 rows=2,185 loops=1)

34. 0.711 145.568 ↓ 42.0 2,185 1

Subquery Scan on r_3 (cost=904.53..1,295.58 rows=52 width=16) (actual time=140.751..145.568 rows=2,185 loops=1)

  • Filter: (r_3.parent_rank = 1)
  • Rows Removed by Filter: 170
35. 3.132 144.857 ↑ 4.4 2,355 1

WindowAgg (cost=904.53..1,165.23 rows=10,428 width=56) (actual time=140.739..144.857 rows=2,355 loops=1)

36. 3.455 141.725 ↑ 4.4 2,355 1

Sort (cost=904.53..930.60 rows=10,428 width=48) (actual time=140.729..141.725 rows=2,355 loops=1)

  • Sort Key: p_1.dataset_id, p_1.company_id, p_1.crm_id, p_1.separation
  • Sort Method: quicksort Memory: 207kB
37. 138.270 138.270 ↑ 4.4 2,355 1

CTE Scan on dataset_parents p_1 (cost=0.00..208.56 rows=10,428 width=48) (actual time=119.080..138.270 rows=2,355 loops=1)

38. 13.110 13.110 ↑ 1.0 1 2,185

Index Scan using company_pkey on company c_1 (cost=0.42..8.44 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=2,185)

  • Index Cond: (id = r_3.company_id)
39. 13.110 13.110 ↑ 1.0 1 2,185

Index Scan using company_pkey on company cp (cost=0.42..8.44 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=2,185)

  • Index Cond: (id = r_3.parent_id)
40. 58.704 2,555.774 ↑ 1.0 47,910 1

Nested Loop Left Join (cost=80,398.28..126,414.70 rows=48,527 width=1,204) (actual time=1,909.115..2,555.774 rows=47,910 loops=1)

41. 84.701 2,065.880 ↑ 1.0 47,910 1

Merge Left Join (cost=80,397.86..80,640.52 rows=48,527 width=543) (actual time=1,909.107..2,065.880 rows=47,910 loops=1)

  • Merge Cond: (r.number = dp.number)
  • Join Filter: (dp.dataset_id = r.dataset_id)
42. 212.162 1,258.616 ↑ 1.0 47,910 1

Sort (cost=80,397.63..80,518.94 rows=48,527 width=541) (actual time=1,211.889..1,258.616 rows=47,910 loops=1)

  • Sort Key: r.number
  • Sort Method: external merge Disk: 9008kB
43. 31.513 1,046.454 ↑ 1.0 47,910 1

Hash Left Join (cost=40,169.52..64,843.15 rows=48,527 width=541) (actual time=646.738..1,046.454 rows=47,910 loops=1)

  • Hash Cond: ((r.dataset_id = p.dataset_id) AND (r.company_id = p.company_id))
44. 284.259 786.968 ↑ 1.0 47,910 1

Hash Left Join (cost=40,082.25..64,270.53 rows=48,527 width=243) (actual time=418.745..786.968 rows=47,910 loops=1)

  • Hash Cond: (r.company_id = c.id)
45. 61.296 265.763 ↑ 1.0 47,910 1

Merge Right Join (cost=24,832.50..42,832.32 rows=48,527 width=157) (actual time=181.719..265.763 rows=47,910 loops=1)

  • Merge Cond: (u.id = r.upload_id)
46. 106.076 106.076 ↑ 1.1 293,040 1

Index Scan using upload_pkey on upload u (cost=0.42..16,497.95 rows=310,195 width=143) (actual time=0.010..106.076 rows=293,040 loops=1)

47. 74.915 98.391 ↑ 1.0 47,910 1

Sort (cost=24,832.08..24,953.39 rows=48,527 width=18) (actual time=85.453..98.391 rows=47,910 loops=1)

  • Sort Key: r.upload_id
  • Sort Method: external sort Disk: 1488kB
48. 18.484 23.476 ↑ 1.0 47,910 1

Bitmap Heap Scan on record r (cost=1,268.51..21,055.10 rows=48,527 width=18) (actual time=5.122..23.476 rows=47,910 loops=1)

  • Recheck Cond: (dataset_id = 1396)
  • Heap Blocks: exact=868
49. 4.992 4.992 ↑ 1.0 47,910 1

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,256.38 rows=48,527 width=0) (actual time=4.992..4.992 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
50. 129.221 236.946 ↓ 1.0 268,531 1

Hash (cost=8,222.78..8,222.78 rows=268,478 width=86) (actual time=236.946..236.946 rows=268,531 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2222kB
51. 107.725 107.725 ↓ 1.0 268,531 1

Seq Scan on company c (cost=0.00..8,222.78 rows=268,478 width=86) (actual time=0.012..107.725 rows=268,531 loops=1)

52. 1.768 227.973 ↓ 115.0 2,185 1

Hash (cost=86.98..86.98 rows=19 width=310) (actual time=227.972..227.973 rows=2,185 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 212kB
53. 226.205 226.205 ↓ 115.0 2,185 1

CTE Scan on p (cost=0.00..86.98 rows=19 width=310) (actual time=208.015..226.205 rows=2,185 loops=1)

  • Filter: (dataset_id = 1396)
54. 77.998 722.563 ↓ 33,963.0 33,963 1

Sort (cost=0.23..0.24 rows=1 width=10) (actual time=697.208..722.563 rows=33,963 loops=1)

  • Sort Key: dp.number
  • Sort Method: quicksort Memory: 2958kB
55. 644.565 644.565 ↓ 33,963.0 33,963 1

CTE Scan on dp (cost=0.00..0.22 rows=1 width=10) (actual time=441.413..644.565 rows=33,963 loops=1)

  • Filter: (dataset_id = 1396)
56. 431.190 431.190 ↑ 1.0 1 47,910

Index Scan using company_full_pkey on company_full cf (cost=0.42..0.93 rows=1 width=665) (actual time=0.009..0.009 rows=1 loops=47,910)

  • Index Cond: (id = c.id)
57. 28.870 814.470 ↑ 1.0 1 47,910

Nested Loop Left Join (cost=1.69..2.17 rows=1 width=319) (actual time=0.016..0.017 rows=1 loops=47,910)

58. 43.305 718.650 ↑ 1.0 1 47,910

Nested Loop Left Join (cost=1.27..1.61 rows=1 width=218) (actual time=0.014..0.015 rows=1 loops=47,910)

59. 105.650 574.920 ↑ 1.0 1 47,910

Nested Loop Left Join (cost=0.84..1.05 rows=1 width=117) (actual time=0.011..0.012 rows=1 loops=47,910)

60. 335.370 335.370 ↑ 1.0 1 47,910

Index Scan using group_relation_pkey on group_relation r_1 (cost=0.42..0.49 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=47,910)

  • Index Cond: (c.id = id)
61. 133.900 133.900 ↓ 0.0 0 33,475

Index Scan using company_pkey on company ip (cost=0.42..0.55 rows=1 width=98) (actual time=0.004..0.004 rows=0 loops=33,475)

  • Index Cond: (r_1.immediate_parent_id = id)
62. 100.425 100.425 ↑ 1.0 1 33,475

Index Scan using company_pkey on company dp_1 (cost=0.42..0.55 rows=1 width=98) (actual time=0.003..0.003 rows=1 loops=33,475)

  • Index Cond: (r_1.domestic_parent_id = id)
63. 66.950 66.950 ↑ 1.0 1 33,475

Index Scan using company_pkey on company up (cost=0.42..0.55 rows=1 width=98) (actual time=0.002..0.002 rows=1 loops=33,475)

  • Index Cond: (r_1.global_parent_id = id)
Planning time : 6.128 ms
Execution time : 5,149.082 ms