explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SdSr

Settings
# exclusive inclusive rows x rows loops node
1. 18,803.991 35,702.667 ↑ 1.0 47,910 1

Nested Loop Left Join (cost=197,914.54..302,230.30 rows=48,138 width=872) (actual time=14,006.365..35,702.667 rows=47,910 loops=1)

2.          

CTE matches

3. 61.847 105.074 ↓ 2.4 33,362 1

HashAggregate (cost=18,334.62..18,474.89 rows=14,027 width=40) (actual time=80.947..105.074 rows=33,362 loops=1)

  • Group Key: rec.dataset_id, rec.company_id
4. 35.674 43.227 ↓ 2.3 33,963 1

Bitmap Heap Scan on record rec (cost=1,289.15..18,223.88 rows=14,765 width=27) (actual time=8.682..43.227 rows=33,963 loops=1)

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

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,285.46 rows=48,138 width=0) (actual time=7.553..7.553 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1382)
6.          

CTE dataset_parents

7. 20.333 223.407 ↑ 4.2 2,355 1

Hash Join (cost=6,049.60..7,946.22 rows=9,844 width=48) (actual time=194.886..223.407 rows=2,355 loops=1)

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

CTE Scan on matches r_3 (cost=0.00..280.54 rows=13,957 width=40) (actual time=0.002..11.672 rows=33,362 loops=1)

  • Filter: (company_id IS NOT NULL)
9. 6.682 191.402 ↑ 8.7 10,611 1

Hash (cost=4,205.02..4,205.02 rows=92,772 width=16) (actual time=191.402..191.402 rows=10,611 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1275kB
10. 60.380 184.720 ↑ 8.7 10,611 1

Hash Join (cost=382.24..4,205.02 rows=92,772 width=16) (actual time=86.342..184.720 rows=10,611 loops=1)

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

Seq Scan on group_parent t (cost=0.00..3,062.44 rows=185,544 width=12) (actual time=0.044..38.103 rows=188,149 loops=1)

12. 11.560 86.237 ↓ 23.8 33,362 1

Hash (cost=364.71..364.71 rows=1,403 width=8) (actual time=86.237..86.237 rows=33,362 loops=1)

  • Buckets: 65536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1816kB
13. 28.658 74.677 ↓ 23.8 33,362 1

HashAggregate (cost=350.68..364.71 rows=1,403 width=8) (actual time=65.590..74.677 rows=33,362 loops=1)

  • Group Key: r2.dataset_id, r2.company_id
14. 46.019 46.019 ↓ 2.4 33,362 1

CTE Scan on matches r2 (cost=0.00..280.54 rows=14,027 width=8) (actual time=0.003..46.019 rows=33,362 loops=1)

15. 83.442 14,215.716 ↑ 1.0 47,910 1

Merge Left Join (cost=171,491.74..171,784.18 rows=48,138 width=982) (actual time=14,006.330..14,215.716 rows=47,910 loops=1)

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

Sort (cost=132,196.68..132,317.03 rows=48,138 width=973) (actual time=13,510.054..13,604.253 rows=47,910 loops=1)

  • Sort Key: r.number
  • Sort Method: external merge Disk: 18968kB
17. 73.148 13,288.377 ↑ 1.0 47,910 1

Nested Loop Left Join (cost=40,662.08..107,883.28 rows=48,138 width=973) (actual time=2,666.306..13,288.377 rows=47,910 loops=1)

18. 613.652 3,489.499 ↑ 1.0 47,910 1

Hash Left Join (cost=40,661.66..65,766.75 rows=48,138 width=312) (actual time=2,666.300..3,489.499 rows=47,910 loops=1)

  • Hash Cond: (r.company_id = c.id)
19. 89.071 2,367.307 ↑ 1.0 47,910 1

Merge Right Join (cost=26,138.11..44,252.03 rows=48,138 width=230) (actual time=2,157.361..2,367.307 rows=47,910 loops=1)

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

21. 10.816 1,862.409 ↑ 1.0 47,910 1

Materialize (cost=26,137.68..26,378.37 rows=48,138 width=91) (actual time=1,837.504..1,862.409 rows=47,910 loops=1)

22. 119.265 1,851.593 ↑ 1.0 47,910 1

Sort (cost=26,137.68..26,258.03 rows=48,138 width=91) (actual time=1,837.498..1,851.593 rows=47,910 loops=1)

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

Hash Right Join (cost=19,195.91..19,922.78 rows=48,138 width=91) (actual time=1,647.576..1,732.328 rows=47,910 loops=1)

  • Hash Cond: ((r_1.dataset_id = r.dataset_id) AND (r_1.company_id = r.company_id))
24. 9.001 827.817 ↓ 31.2 2,185 1

Hash Join (cost=241.62..575.46 rows=70 width=81) (actual time=798.252..827.817 rows=2,185 loops=1)

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

CTE Scan on matches m (cost=0.00..280.54 rows=14,027 width=36) (actual time=80.952..101.568 rows=33,362 loops=1)

26. 3.103 717.248 ↓ 2,185.0 2,185 1

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

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

Nested Loop (cost=223.71..241.60 rows=1 width=57) (actual time=230.019..714.145 rows=2,185 loops=1)

28. 1.743 509.120 ↓ 2,185.0 2,185 1

Nested Loop (cost=223.29..233.15 rows=1 width=16) (actual time=227.800..509.120 rows=2,185 loops=1)

29. 0.955 234.252 ↓ 2,185.0 2,185 1

Subquery Scan on r_1 (cost=222.87..224.70 rows=1 width=16) (actual time=226.882..234.252 rows=2,185 loops=1)

  • Filter: (r_1.parent_rank = 1)
  • Rows Removed by Filter: 170
30. 4.997 233.297 ↓ 48.1 2,355 1

WindowAgg (cost=222.87..224.09 rows=49 width=56) (actual time=226.870..233.297 rows=2,355 loops=1)

31. 3.058 228.300 ↓ 48.1 2,355 1

Sort (cost=222.87..222.99 rows=49 width=48) (actual time=226.861..228.300 rows=2,355 loops=1)

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

CTE Scan on dataset_parents p (cost=0.00..221.49 rows=49 width=48) (actual time=194.893..225.242 rows=2,355 loops=1)

  • Filter: (dataset_id = 1382)
33. 273.125 273.125 ↑ 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.124..0.125 rows=1 loops=2,185)

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

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

Hash (cost=18,232.22..18,232.22 rows=48,138 width=18) (actual time=848.632..848.633 rows=47,910 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2836kB
36. 790.153 814.859 ↑ 1.0 47,910 1

Bitmap Heap Scan on record r (cost=1,297.50..18,232.22 rows=48,138 width=18) (actual time=26.362..814.859 rows=47,910 loops=1)

  • Recheck Cond: (dataset_id = 1382)
  • Heap Blocks: exact=4009
37. 24.706 24.706 ↑ 1.0 47,910 1

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,285.46 rows=48,138 width=0) (actual time=24.706..24.706 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1382)
38. 194.888 508.540 ↓ 1.0 268,018 1

Hash (cost=7,530.80..7,530.80 rows=267,180 width=86) (actual time=508.540..508.540 rows=268,018 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2219kB
39. 313.652 313.652 ↓ 1.0 268,018 1

Seq Scan on company c (cost=0.00..7,530.80 rows=267,180 width=86) (actual time=0.014..313.652 rows=268,018 loops=1)

40. 9,725.730 9,725.730 ↑ 1.0 1 47,910

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

  • Index Cond: (id = c.id)
41. 64.331 528.021 ↓ 16.5 33,963 1

Sort (cost=39,295.06..39,300.19 rows=2,053 width=17) (actual time=496.268..528.021 rows=33,963 loops=1)

  • Sort Key: d.number
  • Sort Method: quicksort Memory: 3599kB
42. 8.273 463.690 ↓ 16.5 33,963 1

Subquery Scan on d (cost=39,105.12..39,182.11 rows=2,053 width=17) (actual time=417.939..463.690 rows=33,963 loops=1)

43. 32.206 455.417 ↓ 16.5 33,963 1

WindowAgg (cost=39,105.12..39,151.31 rows=2,053 width=32) (actual time=417.926..455.417 rows=33,963 loops=1)

44. 32.812 423.211 ↓ 16.5 33,963 1

Sort (cost=39,105.12..39,110.25 rows=2,053 width=20) (actual time=417.915..423.211 rows=33,963 loops=1)

  • Sort Key: m_1.company_id, m_1.number
  • Sort Method: quicksort Memory: 3599kB
45. 24.743 390.399 ↓ 16.5 33,963 1

Merge Join (cost=38,827.68..38,992.17 rows=2,053 width=20) (actual time=340.254..390.399 rows=33,963 loops=1)

  • Merge Cond: (m_1.company_id = cc.company_id)
46. 54.823 171.110 ↓ 2.3 33,963 1

Sort (cost=19,246.35..19,283.26 rows=14,765 width=12) (actual time=160.560..171.110 rows=33,963 loops=1)

  • Sort Key: m_1.company_id
  • Sort Method: quicksort Memory: 2958kB
47. 61.622 116.287 ↓ 2.3 33,963 1

Bitmap Heap Scan on record m_1 (cost=1,289.15..18,223.88 rows=14,765 width=12) (actual time=57.351..116.287 rows=33,963 loops=1)

  • Recheck Cond: (dataset_id = 1382)
  • Filter: (company_id IS NOT NULL)
  • Rows Removed by Filter: 13947
  • Heap Blocks: exact=4009
48. 54.665 54.665 ↑ 1.0 47,910 1

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,285.46 rows=48,138 width=0) (actual time=54.665..54.665 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1382)
49. 41.588 194.546 ↓ 2.4 33,963 1

Sort (cost=19,581.33..19,616.40 rows=14,027 width=16) (actual time=179.685..194.546 rows=33,963 loops=1)

  • Sort Key: cc.company_id
  • Sort Method: quicksort Memory: 2930kB
50. 10.183 152.958 ↓ 2.4 33,362 1

Subquery Scan on cc (cost=18,334.62..18,615.16 rows=14,027 width=16) (actual time=100.546..152.958 rows=33,362 loops=1)

51. 85.955 142.775 ↓ 2.4 33,362 1

HashAggregate (cost=18,334.62..18,474.89 rows=14,027 width=16) (actual time=100.546..142.775 rows=33,362 loops=1)

  • Group Key: m_2.dataset_id, m_2.company_id
52. 41.614 56.820 ↓ 2.3 33,963 1

Bitmap Heap Scan on record m_2 (cost=1,289.15..18,223.88 rows=14,765 width=8) (actual time=16.176..56.820 rows=33,963 loops=1)

  • Recheck Cond: (dataset_id = 1382)
  • Filter: (company_id IS NOT NULL)
  • Rows Removed by Filter: 13947
  • Heap Blocks: exact=4009
53. 15.206 15.206 ↑ 1.0 47,910 1

Bitmap Index Scan on record_dataset_id_number_key (cost=0.00..1,285.46 rows=48,138 width=0) (actual time=15.206..15.206 rows=47,910 loops=1)

  • Index Cond: (dataset_id = 1382)
54. 76.780 2,682.960 ↑ 1.0 1 47,910

Nested Loop Left Join (cost=1.69..2.14 rows=1 width=316) (actual time=0.055..0.056 rows=1 loops=47,910)

55. 24.265 2,539.230 ↑ 1.0 1 47,910

Nested Loop Left Join (cost=1.27..1.59 rows=1 width=216) (actual time=0.052..0.053 rows=1 loops=47,910)

56. 134.520 2,347.590 ↑ 1.0 1 47,910

Nested Loop Left Join (cost=0.84..1.04 rows=1 width=116) (actual time=0.048..0.049 rows=1 loops=47,910)

57. 2,012.220 2,012.220 ↑ 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.042..0.042 rows=1 loops=47,910)

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

Index Scan using company_pkey on company ip (cost=0.42..0.54 rows=1 width=97) (actual time=0.006..0.006 rows=0 loops=33,475)

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

Index Scan using company_pkey on company dp (cost=0.42..0.54 rows=1 width=97) (actual time=0.005..0.005 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.54 rows=1 width=97) (actual time=0.002..0.002 rows=1 loops=33,475)

  • Index Cond: (r_2.global_parent_id = id)
Planning time : 7.225 ms
Execution time : 35,731.633 ms