explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kZd8

Settings
# exclusive inclusive rows x rows loops node
1. 4,960.302 5,116.908 ↓ 253.2 17,577,844 1

Hash Right Join (cost=5,932.04..7,991.83 rows=69,423 width=133) (actual time=149.158..5,116.908 rows=17,577,844 loops=1)

  • Hash Cond: (category.serviced_product_id = sp.serviced_product_id)
2. 7.497 7.497 ↑ 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.013..7.497 rows=69,423 loops=1)

3. 53.978 149.109 ↓ 15.7 111,618 1

Hash (cost=5,843.42..5,843.42 rows=7,089 width=137) (actual time=149.109..149.109 rows=111,618 loops=1)

  • Buckets: 131,072 (originally 8192) Batches: 8,192 (originally 1) Memory Usage: 11,093kB
4. 34.722 95.131 ↓ 15.7 111,618 1

Hash Right Join (cost=5,246.65..5,843.42 rows=7,089 width=137) (actual time=58.408..95.131 rows=111,618 loops=1)

  • Hash Cond: (central_company.serviced_product_id = sp.serviced_product_id)
5. 2.101 2.832 ↓ 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.796..2.832 rows=7,101 loops=1)

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

  • Index Cond: (serviced_product_attribute_id = 18)
7. 5.064 57.577 ↓ 1.9 13,354 1

Hash (cost=5,022.90..5,022.90 rows=7,089 width=133) (actual time=57.577..57.577 rows=13,354 loops=1)

  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,784kB
8. 6.082 52.513 ↓ 1.9 13,354 1

Hash Right Join (cost=4,431.25..5,022.90 rows=7,089 width=133) (actual time=44.656..52.513 rows=13,354 loops=1)

  • Hash Cond: (user_company.serviced_product_id = sp.serviced_product_id)
9. 1.864 2.550 ↓ 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.739..2.550 rows=7,023 loops=1)

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

  • Index Cond: (serviced_product_attribute_id = 62)
11. 2.405 43.881 ↓ 1.1 7,548 1

Hash (cost=4,209.01..4,209.01 rows=7,089 width=129) (actual time=43.881..43.881 rows=7,548 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 943kB
12. 2.863 41.476 ↓ 1.1 7,548 1

Hash Right Join (cost=3,705.74..4,209.01 rows=7,089 width=129) (actual time=37.703..41.476 rows=7,548 loops=1)

  • Hash Cond: (customer_warranty_date.serviced_product_id = sp.serviced_product_id)
13. 0.989 1.323 ↓ 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.383..1.323 rows=3,543 loops=1)

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

  • Index Cond: (serviced_product_attribute_id = 26)
15. 2.186 37.290 ↓ 1.1 7,548 1

Hash (cost=3,549.59..3,549.59 rows=7,089 width=121) (actual time=37.290..37.290 rows=7,548 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 912kB
16. 1.902 35.104 ↓ 1.1 7,548 1

Hash Right Join (cost=3,354.26..3,549.59 rows=7,089 width=121) (actual time=33.210..35.104 rows=7,548 loops=1)

  • Hash Cond: (provider_warranty_date.serviced_product_id = sp.serviced_product_id)
17. 0.013 0.032 ↑ 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.023..0.032 rows=15 loops=1)

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

  • Index Cond: (serviced_product_attribute_id = 21)
19. 2.295 33.170 ↓ 1.1 7,548 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 912kB
20. 2.555 30.875 ↓ 1.1 7,548 1

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

  • Hash Cond: (sp.product_id = p.productid)
21. 2.854 26.565 ↓ 1.1 7,548 1

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

  • Hash Cond: (last_conservation_date.serviced_product_id = sp.serviced_product_id)
22. 1.312 1.688 ↓ 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.418..1.688 rows=4,569 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 23)
  • Heap Blocks: exact=281
23. 0.376 0.376 ↓ 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.376..0.376 rows=4,569 loops=1)

  • Index Cond: (serviced_product_attribute_id = 23)
24. 2.036 22.023 ↓ 1.0 7,409 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 586kB
25. 2.768 19.987 ↓ 1.0 7,409 1

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

  • Hash Cond: (installation_date.serviced_product_id = sp.serviced_product_id)
26. 0.986 1.328 ↑ 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=0.377..1.328 rows=3,878 loops=1)

  • Recheck Cond: (serviced_product_attribute_id = 7)
  • Heap Blocks: exact=251
27. 0.342 0.342 ↑ 1.0 3,878 1

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.342..0.342 rows=3,878 loops=1)

  • Index Cond: (serviced_product_attribute_id = 7)
28. 1.929 15.891 ↓ 1.0 7,111 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 477kB
29. 2.799 13.962 ↓ 1.0 7,111 1

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

  • Hash Cond: (sell_date.serviced_product_id = sp.serviced_product_id)
30. 1.653 1.653 ↓ 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.362..1.653 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.324..0.324 rows=38
  • Index Cond: (serviced_product_attribute_id = 20)
31. 2.206 9.510 ↑ 1.0 7,089 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 438kB
32. 1.574 7.304 ↑ 1.0 7,089 1

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

  • Merge Cond: (sp.serviced_product_id = last_software_update_date.serviced_product_id)
33. 1.242 5.402 ↑ 1.0 7,089 1

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

  • Merge Cond: (sp.serviced_product_id = purchase_price.serviced_product_id)
34. 4.090 4.147 ↑ 1.0 7,089 1

Merge Left Join (cost=201.82..479.63 rows=7,089 width=34) (actual time=0.067..4.147 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.010..2.094 rows=7
35. 0.057 0.057 ↑ 10.9 7 1

Sort (cost=201.54..201.73 rows=76 width=19) (actual time=0.056..0.057 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 time=0.028..0.03
  • 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 width=0) (actua
  • Index Cond: (serviced_product_attribute_id = 32)
36. 0.013 0.013 ↓ 0.0 0 1

Sort (cost=201.54..201.73 rows=76 width=19) (actual time=0.013..0.013 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.010..0.010 rows=0
  • 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=76 width=0) (actual time
  • Index Cond: (serviced_product_attribute_id = 55)
37. 0.328 0.328 ↓ 2.5 188 1

Sort (cost=201.54..201.73 rows=76 width=12) (actual time=0.302..0.328 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.037..0.253 r
  • 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) (actual time=0.027
  • Index Cond: (serviced_product_attribute_id = 24)
38. 0.479 1.755 ↑ 1.0 2,218 1

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

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

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

Planning time : 7.865 ms
Execution time : 5,955.513 ms