explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k4xl

Settings
# exclusive inclusive rows x rows loops node
1. 12,676.768 15,273.821 ↓ 12,024.8 34,511,084 1

Hash Right Join (cost=6,885.13..7,436.35 rows=2,870 width=137) (actual time=2,595.106..15,273.821 rows=34,511,084 loops=1)

  • Hash Cond: (owner_company.serviced_product_id = sp.serviced_product_id)
2. 2.048 4.465 ↓ 1.0 7,009 1

Bitmap Heap Scan on attribute_to_serviced_product owner_company (cost=133.83..631.18 rows=6,908 width=4) (actual time=2.478..4.465 rows=7,009 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 17)
  • Heap Blocks: exact=378
3. 2.417 2.417 ↓ 1.0 7,009 1

Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..132.10 rows=6,908 width=0) (actual time=2.417..2.417 rows=7,009 loops=1)

  • Index Cond: (serviced_product_attribute_id = 17)
4. 1,371.456 2,592.588 ↓ 651.2 1,868,964 1

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

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

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

  • Hash Cond: (central_company.serviced_product_id = sp.serviced_product_id)
6. 1.925 5.336 ↓ 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=3.472..5.336 rows=7,101 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 18)
  • Heap Blocks: exact=380
7. 3.411 3.411 ↓ 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=3.411..3.411 rows=7,101 loops=1)

  • Index Cond: (serviced_product_attribute_id = 18)
8. 58.418 300.805 ↓ 38.9 111,524 1

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

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

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

  • Hash Cond: (user_company.serviced_product_id = sp.serviced_product_id)
10. 1.950 5.909 ↓ 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=4.019..5.909 rows=7,023 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 62)
  • Heap Blocks: exact=378
11. 3.959 3.959 ↓ 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=3.959..3.959 rows=7,023 loops=1)

  • Index Cond: (serviced_product_attribute_id = 62)
12. 6.213 201.033 ↓ 4.7 13,368 1

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

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

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

  • Hash Cond: (customer_warranty_date.serviced_product_id = sp.serviced_product_id)
14. 0.993 1.402 ↓ 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.448..1.402 rows=3,543 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 26)
  • Heap Blocks: exact=235
15. 0.409 0.409 ↓ 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.409..0.409 rows=3,543 loops=1)

  • Index Cond: (serviced_product_attribute_id = 26)
16. 7.645 188.424 ↓ 4.7 13,368 1

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

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,719kB
17. 4.675 180.779 ↓ 4.7 13,368 1

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

  • Hash Cond: (provider_warranty_date.serviced_product_id = sp.serviced_product_id)
18. 0.025 0.057 ↑ 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.039..0.057 rows=15 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 21)
  • Heap Blocks: exact=7
19. 0.032 0.032 ↑ 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.032..0.032 rows=15 loops=1)

  • Index Cond: (serviced_product_attribute_id = 21)
20. 5.850 176.047 ↓ 4.7 13,368 1

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

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,719kB
21. 5.419 170.197 ↓ 4.7 13,368 1

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

  • Hash Cond: (seller_company.serviced_product_id = sp.serviced_product_id)
22. 2.414 24.820 ↓ 2.5 7,055 1

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

  • Hash Cond: (seller_company.company_id = seller_company_ac.company_id)
23. 3.317 7.430 ↑ 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=4.183..7.430 rows=7,055 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 19)
  • Heap Blocks: exact=381
24. 4.113 4.113 ↑ 1.0 7,055 1

Bitmap Index Scan on fki_serviced_product_attribute_serviced_product_attribute_id (cost=0.00..133.69 rows=7,120 width=0) (actual time=4.113..4.113 rows=7,055 loops=1)

  • Index Cond: (serviced_product_attribute_id = 19)
25. 1.488 14.976 ↑ 1.0 6,509 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 293kB
26. 13.488 13.488 ↑ 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.005..13.488 rows=6,509 loops=1)

27. 3.106 139.958 ↓ 1.1 7,548 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 912kB
28. 2.442 136.852 ↓ 1.1 7,548 1

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

  • Hash Cond: (sp.product_id = p.productid)
29. 3.722 105.212 ↓ 1.1 7,548 1

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

  • Hash Cond: (last_conservation_date.serviced_product_id = sp.serviced_product_id)
30. 23.329 23.901 ↓ 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.615..23.901 rows=4,569 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 23)
  • Heap Blocks: exact=281
31. 0.572 0.572 ↓ 1.0 4,569 1

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.572..0.572 rows=4,569 loops=1)

  • Index Cond: (serviced_product_attribute_id = 23)
32. 2.449 77.589 ↓ 1.0 7,409 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 586kB
33. 3.054 75.140 ↓ 1.0 7,409 1

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

  • Hash Cond: (installation_date.serviced_product_id = sp.serviced_product_id)
34. 2.836 2.836 ↑ 1.0 3,878 1

Bitmap Heap Scan on attribute_to_serviced_product installation_date (cost=74.94..535.38 rows=3,955 width=12) (actual time=1.719..2.836 rows=3,878 loops=1)

  • 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=1.684..1.684 rows=3
  • Index Cond: (serviced_product_attribute_id = 7)
35. 3.413 69.250 ↓ 1.0 7,111 1

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

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

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

  • Hash Cond: (sell_date.serviced_product_id = sp.serviced_product_id)
37. 21.699 21.699 ↓ 1.0 3,888 1

Bitmap Heap Scan on attribute_to_serviced_product sell_date (cost=74.08..533.13 rows=3,844 width=12) (actual time=0.506..21.699 rows=3,888 loops=1)

  • 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 time=0.468..
  • Index Cond: (serviced_product_attribute_id = 20)
38. 2.131 39.245 ↑ 1.0 7,089 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 438kB
39. 1.366 37.114 ↑ 1.0 7,089 1

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

  • Merge Cond: (sp.serviced_product_id = last_software_update_date.serviced_product_id)
40. 1.114 31.211 ↑ 1.0 7,089 1

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

  • Merge Cond: (sp.serviced_product_id = purchase_price.serviced_product_id)
41. 8.774 29.111 ↑ 1.0 7,089 1

Merge Left Join (cost=201.82..479.63 rows=7,089 width=34) (actual time=20.359..29.111 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 time=0.017.
42. 20.337 20.337 ↑ 10.9 7 1

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

  • Sort Key: currency_version.serviced_product_id
  • Sort Method: quicksort Memory: 25kB
  • -> Bitmap Heap Scan on attribute_to_serviced_product currency_version (cost=4.88..199.17 rows=76 width=19) (actual tim
  • 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.86 rows=76 wi
  • Index Cond: (serviced_product_attribute_id = 32)
43. 0.986 0.986 ↓ 0.0 0 1

Sort (cost=201.54..201.73 rows=76 width=19) (actual time=0.986..0.986 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 time=0.947.
  • Recheck Cond: (serviced_product_attribute_id = 55)
44. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (serviced_product_attribute_id = 55)
45. 4.537 4.537 ↓ 2.5 188 1

Sort (cost=201.54..201.73 rows=76 width=12) (actual time=4.508..4.537 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) (actual time=0
  • 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 width=0) (actu
  • Index Cond: (serviced_product_attribute_id = 24)
46. 1.178 29.198 ↑ 1.0 2,218 1

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

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

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

Planning time : 33.528 ms
Execution time : 16,878.762 ms