explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WldZh

Settings
# exclusive inclusive rows x rows loops node
1. 1,481.527 4,957.224 ↑ 1.0 47,910 1

Nested Loop Left Join (cost=214,084.54..320,614.17 rows=48,527 width=875) (actual time=2,619.602..4,957.224 rows=47,910 loops=1)

2.          

CTE matches

3. 36.689 50.467 ↓ 2.2 33,362 1

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

  • Group Key: rec.dataset_id, rec.company_id
4. 10.599 13.778 ↓ 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.313..13.778 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.179 3.179 ↑ 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.179..3.179 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
6.          

CTE dataset_parents

7. 15.213 137.470 ↑ 4.4 2,355 1

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

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

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

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

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

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

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

  • Hash Cond: (t.parent_id = r2.company_id)
11. 22.207 22.207 ↓ 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.032..22.207 rows=188,217 loops=1)

12. 6.500 51.662 ↓ 22.4 33,362 1

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

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

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

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

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

15. 55.746 2,757.047 ↑ 1.0 47,910 1

Merge Left Join (cost=184,803.98..185,091.24 rows=48,527 width=982) (actual time=2,619.566..2,757.047 rows=47,910 loops=1)

  • Merge Cond: (r.number = d.number)
  • Join Filter: (d.dataset_id = r.dataset_id)
16. 268.336 1,940.193 ↑ 1.0 47,910 1

Sort (cost=139,718.48..139,839.80 rows=48,527 width=973) (actual time=1,884.338..1,940.193 rows=47,910 loops=1)

  • Sort Key: r.number
  • Sort Method: external merge Disk: 18960kB
17. 80.848 1,671.857 ↑ 1.0 47,910 1

Nested Loop Left Join (cost=44,270.15..115,207.51 rows=48,527 width=973) (actual time=693.254..1,671.857 rows=47,910 loops=1)

18. 277.719 1,063.999 ↑ 1.0 47,910 1

Hash Left Join (cost=44,269.73..69,433.32 rows=48,527 width=312) (actual time=693.247..1,063.999 rows=47,910 loops=1)

  • Hash Cond: (r.company_id = c.id)
19. 60.207 548.452 ↑ 1.0 47,910 1

Merge Right Join (cost=29,019.98..47,141.11 rows=48,527 width=230) (actual time=454.869..548.452 rows=47,910 loops=1)

  • Merge Cond: (u.id = r.upload_id)
20. 93.205 93.205 ↑ 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.013..93.205 rows=293,040 loops=1)

21. 7.973 395.040 ↑ 1.0 47,910 1

Materialize (cost=29,019.55..29,262.19 rows=48,527 width=91) (actual time=371.499..395.040 rows=47,910 loops=1)

22. 91.809 387.067 ↑ 1.0 47,910 1

Sort (cost=29,019.55..29,140.87 rows=48,527 width=91) (actual time=371.493..387.067 rows=47,910 loops=1)

  • Sort Key: r.upload_id
  • Sort Method: external merge Disk: 1384kB
23. 24.080 295.258 ↑ 1.0 47,910 1

Hash Right Join (cost=22,037.98..22,754.08 rows=48,527 width=91) (actual time=254.153..295.258 rows=47,910 loops=1)

  • Hash Cond: ((r_1.dataset_id = r.dataset_id) AND (r_1.company_id = r.company_id))
24. 9.769 236.843 ↓ 29.5 2,185 1

Hash Join (cost=254.97..608.90 rows=74 width=81) (actual time=219.268..236.843 rows=2,185 loops=1)

  • Hash Cond: (m.company_id = r_1.parent_id)
25. 42.599 42.599 ↓ 2.2 33,362 1

CTE Scan on matches m (cost=0.00..297.42 rows=14,871 width=36) (actual time=34.655..42.599 rows=33,362 loops=1)

26. 1.593 184.475 ↓ 2,185.0 2,185 1

Hash (cost=254.96..254.96 rows=1 width=57) (actual time=184.475..184.475 rows=2,185 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 229kB
27. 3.102 182.882 ↓ 2,185.0 2,185 1

Nested Loop (cost=236.96..254.96 rows=1 width=57) (actual time=140.918..182.882 rows=2,185 loops=1)

28. 0.928 164.485 ↓ 2,185.0 2,185 1

Nested Loop (cost=236.53..246.51 rows=1 width=16) (actual time=140.901..164.485 rows=2,185 loops=1)

29. 0.738 146.077 ↓ 2,185.0 2,185 1

Subquery Scan on r_1 (cost=236.11..238.06 rows=1 width=16) (actual time=140.780..146.077 rows=2,185 loops=1)

  • Filter: (r_1.parent_rank = 1)
  • Rows Removed by Filter: 170
30. 3.549 145.339 ↓ 45.3 2,355 1

WindowAgg (cost=236.11..237.41 rows=52 width=56) (actual time=140.769..145.339 rows=2,355 loops=1)

31. 2.713 141.790 ↓ 45.3 2,355 1

Sort (cost=236.11..236.24 rows=52 width=48) (actual time=140.758..141.790 rows=2,355 loops=1)

  • Sort Key: p.company_id, p.crm_id, p.separation
  • Sort Method: quicksort Memory: 207kB
32. 139.077 139.077 ↓ 45.3 2,355 1

CTE Scan on dataset_parents p (cost=0.00..234.63 rows=52 width=48) (actual time=119.054..139.077 rows=2,355 loops=1)

  • Filter: (dataset_id = 1396)
33. 17.480 17.480 ↑ 1.0 1 2,185

Index Only Scan using company_pkey on company c_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=2,185)

  • Index Cond: (id = r_1.company_id)
  • Heap Fetches: 2185
34. 15.295 15.295 ↑ 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.007..0.007 rows=1 loops=2,185)

  • Index Cond: (id = r_1.parent_id)
35. 14.813 34.335 ↑ 1.0 47,910 1

Hash (cost=21,055.10..21,055.10 rows=48,527 width=18) (actual time=34.335..34.335 rows=47,910 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2836kB
36. 15.705 19.522 ↑ 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=3.959..19.522 rows=47,910 loops=1)

  • Recheck Cond: (dataset_id = 1396)
  • Heap Blocks: exact=868
37. 3.817 3.817 ↑ 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.817..3.817 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
38. 137.032 237.828 ↓ 1.0 268,531 1

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

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

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

40. 527.010 527.010 ↑ 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.011..0.011 rows=1 loops=47,910)

  • Index Cond: (id = c.id)
41. 92.861 761.108 ↓ 17.1 33,963 1

Sort (cost=45,085.50..45,090.47 rows=1,990 width=17) (actual time=735.219..761.108 rows=33,963 loops=1)

  • Sort Key: d.number
  • Sort Method: quicksort Memory: 3599kB
42. 34.822 668.247 ↓ 17.1 33,963 1

Subquery Scan on d (cost=44,901.83..44,976.46 rows=1,990 width=17) (actual time=494.204..668.247 rows=33,963 loops=1)

43. 128.572 633.425 ↓ 17.1 33,963 1

WindowAgg (cost=44,901.83..44,946.61 rows=1,990 width=32) (actual time=494.190..633.425 rows=33,963 loops=1)

44. 48.329 504.853 ↓ 17.1 33,963 1

Sort (cost=44,901.83..44,906.81 rows=1,990 width=20) (actual time=494.175..504.853 rows=33,963 loops=1)

  • Sort Key: m_1.company_id, m_1.number
  • Sort Method: quicksort Memory: 3599kB
45. 62.169 456.524 ↓ 17.1 33,963 1

Merge Join (cost=44,620.80..44,792.80 rows=1,990 width=20) (actual time=329.323..456.524 rows=33,963 loops=1)

  • Merge Cond: (m_1.company_id = cc.company_id)
46. 78.340 121.717 ↓ 2.2 33,963 1

Sort (cost=22,129.34..22,168.21 rows=15,548 width=12) (actual time=88.504..121.717 rows=33,963 loops=1)

  • Sort Key: m_1.company_id
  • Sort Method: quicksort Memory: 2958kB
47. 32.365 43.377 ↓ 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=11.247..43.377 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
48. 11.012 11.012 ↑ 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=11.012..11.012 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
49. 102.138 272.638 ↓ 2.3 33,963 1

Sort (cost=22,491.46..22,528.64 rows=14,871 width=16) (actual time=240.810..272.638 rows=33,963 loops=1)

  • Sort Key: cc.company_id
  • Sort Method: quicksort Memory: 2930kB
50. 9.873 170.500 ↓ 2.2 33,362 1

Subquery Scan on cc (cost=21,163.46..21,460.88 rows=14,871 width=16) (actual time=118.288..170.500 rows=33,362 loops=1)

51. 109.537 160.627 ↓ 2.2 33,362 1

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

  • Group Key: m_2.dataset_id, m_2.company_id
52. 40.377 51.090 ↓ 2.2 33,963 1

Bitmap Heap Scan on record m_2 (cost=1,260.27..21,046.85 rows=15,548 width=8) (actual time=10.945..51.090 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
53. 10.713 10.713 ↑ 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=10.713..10.713 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1396)
54. 28.870 718.650 ↑ 1.0 1 47,910

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

55. 43.305 622.830 ↑ 1.0 1 47,910

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

56. 91.215 479.100 ↑ 1.0 1 47,910

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

57. 287.460 287.460 ↑ 1.0 1 47,910

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

  • Index Cond: (c.id = id)
58. 100.425 100.425 ↓ 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.003..0.003 rows=0 loops=33,475)

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

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

  • Index Cond: (r_2.domestic_parent_id = id)
60. 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_2.global_parent_id = id)
Planning time : 6.313 ms
Execution time : 4,972.865 ms