explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cNLY

Settings
# exclusive inclusive rows x rows loops node
1. 91,475.403 97,668.076 ↓ 11,294.4 317,441,223 1

Hash Right Join (cost=6,986.94..8,633.56 rows=28,106 width=137) (actual time=6,184.175..97,668.076 rows=317,441,223 loops=1)

  • Hash Cond: (category.serviced_product_id = sp.serviced_product_id)
2. 8.545 8.545 ↑ 1.0 69,423 1

Seq Scan on attribute_to_serviced_product category (cost=0.00..1,105.23 rows=69,423 width=8) (actual time=0.025..8.545 rows=69,423 loops=1)

3. 768.240 6,184.128 ↓ 651.2 1,868,964 1

Hash (cost=6,951.06..6,951.06 rows=2,870 width=141) (actual time=6,184.128..6,184.128 rows=1,868,964 loops=1)

  • Buckets: 131,072 (originally 4096) Batches: 8,192 (originally 1) Memory Usage: 194,122kB
4. 3,527.538 5,415.888 ↓ 651.2 1,868,964 1

Hash Right Join (cost=6,756.18..6,951.06 rows=2,870 width=141) (actual time=1,888.345..5,415.888 rows=1,868,964 loops=1)

  • Hash Cond: (conservation_every.serviced_product_id = sp.serviced_product_id)
5. 0.039 0.081 ↑ 1.0 73 1

Bitmap Heap Scan on attribute_to_serviced_product conservation_every (cost=4.88..199.17 rows=76 width=4) (actual time=0.053..0.081 rows=73 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 27)
  • Heap Blocks: exact=10
6. 0.042 0.042 ↑ 1.0 73 1

Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..4.86 rows=76 width=0) (actual time=0.042..0.042 rows=73 loops=1)

  • Index Cond: (serviced_product_attribute_id = 27)
7. 823.561 1,888.269 ↓ 651.2 1,868,964 1

Hash (cost=6,715.42..6,715.42 rows=2,870 width=141) (actual time=1,888.269..1,888.269 rows=1,868,964 loops=1)

  • Buckets: 131,072 (originally 4096) Batches: 8,192 (originally 1) Memory Usage: 194,122kB
8. 888.913 1,064.708 ↓ 651.2 1,868,964 1

Hash Right Join (cost=6,160.77..6,715.42 rows=2,870 width=141) (actual time=173.818..1,064.708 rows=1,868,964 loops=1)

  • Hash Cond: (central_company.serviced_product_id = sp.serviced_product_id)
9. 2.057 2.599 ↓ 1.0 7,101 1

Bitmap Heap Scan on attribute_to_serviced_product central_company (cost=135.14..634.60 rows=7,077 width=8) (actual time=0.604..2.599 rows=7,101 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 18)
  • Heap Blocks: exact=380
10. 0.542 0.542 ↓ 1.0 7,101 1

Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..133.37 rows=7,077 width=0) (actual time=0.542..0.542 rows=7,101 loops=1)

  • Index Cond: (serviced_product_attribute_id = 18)
11. 55.032 173.196 ↓ 38.9 111,524 1

Hash (cost=5,989.76..5,989.76 rows=2,870 width=137) (actual time=173.196..173.196 rows=111,524 loops=1)

  • Buckets: 131,072 (originally 4096) Batches: 8,192 (originally 1) Memory Usage: 11,093kB
12. 37.210 118.164 ↓ 38.9 111,524 1

Hash Right Join (cost=5,439.07..5,989.76 rows=2,870 width=137) (actual time=79.168..118.164 rows=111,524 loops=1)

  • Hash Cond: (user_company.serviced_product_id = sp.serviced_product_id)
13. 1.858 2.475 ↓ 1.0 7,023 1

Bitmap Heap Scan on attribute_to_serviced_product user_company (cost=133.63..630.65 rows=6,882 width=8) (actual time=0.670..2.475 rows=7,023 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 62)
  • Heap Blocks: exact=378
14. 0.617 0.617 ↓ 1.0 7,023 1

Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..131.91 rows=6,882 width=0) (actual time=0.617..0.617 rows=7,023 loops=1)

  • Index Cond: (serviced_product_attribute_id = 62)
15. 6.978 78.479 ↓ 4.7 13,368 1

Hash (cost=5,269.56..5,269.56 rows=2,870 width=133) (actual time=78.479..78.479 rows=13,368 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,790kB
16. 6.631 71.501 ↓ 4.7 13,368 1

Hash Right Join (cost=4,787.21..5,269.56 rows=2,870 width=133) (actual time=63.462..71.501 rows=13,368 loops=1)

  • Hash Cond: (customer_warranty_date.serviced_product_id = sp.serviced_product_id)
17. 1.474 2.045 ↓ 1.0 3,543 1

Bitmap Heap Scan on attribute_to_serviced_product customer_warranty_date (cost=67.53..522.47 rows=3,515 width=12) (actual time=0.616..2.045 rows=3,543 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 26)
  • Heap Blocks: exact=235
18. 0.571 0.571 ↓ 1.0 3,543 1

Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..66.66 rows=3,515 width=0) (actual time=0.571..0.571 rows=3,543 loops=1)

  • Index Cond: (serviced_product_attribute_id = 26)
19. 5.210 62.825 ↓ 4.7 13,368 1

Hash (cost=4,683.81..4,683.81 rows=2,870 width=125) (actual time=62.825..62.825 rows=13,368 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,719kB
20. 3.555 57.615 ↓ 4.7 13,368 1

Hash Right Join (cost=4,488.93..4,683.81 rows=2,870 width=125) (actual time=54.061..57.615 rows=13,368 loops=1)

  • Hash Cond: (provider_warranty_date.serviced_product_id = sp.serviced_product_id)
21. 0.020 0.042 ↑ 5.1 15 1

Bitmap Heap Scan on attribute_to_serviced_product provider_warranty_date (cost=4.88..199.17 rows=76 width=12) (actual time=0.028..0.042 rows=15 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 21)
  • Heap Blocks: exact=7
22. 0.022 0.022 ↑ 5.1 15 1

Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..4.86 rows=76 width=0) (actual time=0.022..0.022 rows=15 loops=1)

  • Index Cond: (serviced_product_attribute_id = 21)
23. 4.671 54.018 ↓ 4.7 13,368 1

Hash (cost=4,448.17..4,448.17 rows=2,870 width=117) (actual time=54.018..54.018 rows=13,368 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,719kB
24. 5.463 49.347 ↓ 4.7 13,368 1

Hash Join (cost=3,853.31..4,448.17 rows=2,870 width=117) (actual time=39.895..49.347 rows=13,368 loops=1)

  • Hash Cond: (seller_company.serviced_product_id = sp.serviced_product_id)
25. 2.250 10.200 ↓ 2.5 7,055 1

Hash Join (cost=503.93..1,059.33 rows=2,870 width=8) (actual time=6.185..10.200 rows=7,055 loops=1)

  • Hash Cond: (seller_company.company_id = seller_company_ac.company_id)
26. 3.973 3.973 ↑ 1.0 7,055 1

Bitmap Heap Scan on attribute_to_serviced_product seller_company (cost=135.47..635.47 rows=7,120 width=8) (actual time=2.178..3.973 rows=7,055 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 19)
  • Heap Blocks: exact=381
  • -> Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..133.69 rows=7,120 width=0) (actual time=2.064..2.064 rows=7,055 loops=1
  • Index Cond: (serviced_product_attribute_id = 19)
27. 1.305 3.977 ↑ 1.0 6,509 1

Hash (cost=287.09..287.09 rows=6,509 width=4) (actual time=3.977..3.977 rows=6,509 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 293kB
28. 2.672 2.672 ↑ 1.0 6,509 1

Seq Scan on account_company seller_company_ac (cost=0.00..287.09 rows=6,509 width=4) (actual time=0.007..2.672 rows=6,509 loops=1)

29. 2.256 33.684 ↓ 1.1 7,548 1

Hash (cost=3,260.77..3,260.77 rows=7,089 width=113) (actual time=33.684..33.684 rows=7,548 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 912kB
30. 2.479 31.428 ↓ 1.1 7,548 1

Hash Join (cost=2,632.57..3,260.77 rows=7,089 width=113) (actual time=24.422..31.428 rows=7,548 loops=1)

  • Hash Cond: (sp.product_id = p.productid)
31. 3.367 27.021 ↓ 1.1 7,548 1

Hash Right Join (cost=2,449.67..2,980.39 rows=7,089 width=81) (actual time=22.484..27.021 rows=7,548 loops=1)

  • Hash Cond: (last_conservation_date.serviced_product_id = sp.serviced_product_id)
32. 1.702 1.702 ↓ 1.0 4,569 1

Bitmap Heap Scan on attribute_to_serviced_product last_conservation_date (cost=87.64..555.65 rows=4,561 width=12) (actual time=0.505..1.702 rows=4,569 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 23)
  • Heap Blocks: exact=281
  • -> Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..86.50 rows=4,561 width=0) (actual time=0.463..0.463 rows=4
  • Index Cond: (serviced_product_attribute_id = 23)
33. 1.815 21.952 ↓ 1.0 7,409 1

Hash (cost=2,273.41..2,273.41 rows=7,089 width=73) (actual time=21.952..21.952 rows=7,409 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 586kB
34. 5.677 20.137 ↓ 1.0 7,409 1

Hash Right Join (cost=1,758.59..2,273.41 rows=7,089 width=73) (actual time=14.885..20.137 rows=7,409 loops=1)

  • Hash Cond: (installation_date.serviced_product_id = sp.serviced_product_id)
  • -> Bitmap Heap Scan on attribute_to_serviced_product installation_date (cost=74.94..535.38 rows=3,955 width=12) (actual time=0.334..1.304 rows=3,878 l
  • Recheck Cond: (serviced_product_attribute_id = 7)
  • Heap Blocks: exact=251
  • -> Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..73.95 rows=3,955 width=0) (actual time=0.301..
  • Index Cond: (serviced_product_attribute_id = 7)
35. 1.661 14.460 ↓ 1.0 7,111 1

Hash (cost=1,595.04..1,595.04 rows=7,089 width=65) (actual time=14.460..14.460 rows=7,111 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 477kB
36. 4.033 12.799 ↓ 1.0 7,111 1

Hash Right Join (cost=1,083.13..1,595.04 rows=7,089 width=65) (actual time=9.169..12.799 rows=7,111 loops=1)

  • Hash Cond: (sell_date.serviced_product_id = sp.serviced_product_id)
  • -> Bitmap Heap Scan on attribute_to_serviced_product sell_date (cost=74.08..533.13 rows=3,844 width=12) (actual time=0.340..1.381 rows=38
  • Recheck Cond: (serviced_product_attribute_id = 20)
  • Heap Blocks: exact=253
  • -> Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..73.12 rows=3,844 width=0) (actual
  • Index Cond: (serviced_product_attribute_id = 20)
37. 1.882 8.766 ↑ 1.0 7,089 1

Hash (cost=920.44..920.44 rows=7,089 width=57) (actual time=8.766..8.766 rows=7,089 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 438kB
38. 1.413 6.884 ↑ 1.0 7,089 1

Merge Left Join (cost=604.90..920.44 rows=7,089 width=57) (actual time=0.644..6.884 rows=7,089 loops=1)

  • Merge Cond: (sp.serviced_product_id = last_software_update_date.serviced_product_id)
39. 1.140 4.980 ↑ 1.0 7,089 1

Merge Left Join (cost=403.36..700.03 rows=7,089 width=49) (actual time=0.171..4.980 rows=7,089 loops=1)

  • Merge Cond: (sp.serviced_product_id = purchase_price.serviced_product_id)
40. 3.695 3.809 ↑ 1.0 7,089 1

Merge Left Join (cost=201.82..479.63 rows=7,089 width=34) (actual time=0.138..3.809 rows=7,089 loops=1)

  • Merge Cond: (sp.serviced_product_id = currency_version.serviced_product_id)
  • -> Index Scan using pk_serviced_product_id on serviced_product sp (cost=0.28..259.23 rows=7,089 width=19) (actual
41. 0.114 0.114 ↑ 10.9 7 1

Sort (cost=201.54..201.73 rows=76 width=19) (actual time=0.114..0.114 rows=7 loops=1)

  • Sort Key: currency_version.serviced_product_id
  • Sort Method: quicksort Memory: 25kB
42. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on attribute_to_serviced_product currency_version (cost=4.88..199.17 rows=76 width=19) (actual rows= loops=)

  • Recheck Cond: (serviced_product_attribute_id = 32)
  • Heap Blocks: exact=5
  • -> Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..4.8
  • Index Cond: (serviced_product_attribute_id = 32)
43. 0.031 0.031 ↓ 0.0 0 1

Sort (cost=201.54..201.73 rows=76 width=19) (actual time=0.031..0.031 rows=0 loops=1)

  • Sort Key: purchase_price.serviced_product_id
  • Sort Method: quicksort Memory: 25kB
  • -> Bitmap Heap Scan on attribute_to_serviced_product purchase_price (cost=4.88..199.17 rows=76 width=19) (actual
  • Recheck Cond: (serviced_product_attribute_id = 55)
  • -> Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..4.86 rows
  • Index Cond: (serviced_product_attribute_id = 55)
44. 0.491 0.491 ↓ 2.5 188 1

Sort (cost=201.54..201.73 rows=76 width=12) (actual time=0.470..0.491 rows=188 loops=1)

  • Sort Key: last_software_update_date.serviced_product_id
  • Sort Method: quicksort Memory: 33kB
  • -> Bitmap Heap Scan on attribute_to_serviced_product last_software_update_date (cost=4.88..199.17 rows=76 width=12) (a
  • Recheck Cond: (serviced_product_attribute_id = 24)
  • Heap Blocks: exact=36
  • -> Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..4.86 rows=76 wi
  • Index Cond: (serviced_product_attribute_id = 24)
45. 0.575 1.928 ↑ 1.0 2,218 1

Hash (cost=155.18..155.18 rows=2,218 width=40) (actual time=1.928..1.928 rows=2,218 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 193kB
46. 1.353 1.353 ↑ 1.0 2,218 1

Seq Scan on product p (cost=0.00..155.18 rows=2,218 width=40) (actual time=0.011..1.353 rows=2,218 loops=1)

Planning time : 7.546 ms
Execution time : 112,885.168 ms