explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dp35

Settings
# exclusive inclusive rows x rows loops node
1. 0.264 17,588.465 ↓ 1.1 158 1

Sort (cost=1,328,774.15..1,328,774.52 rows=150 width=2,083) (actual time=17,588.445..17,588.465 rows=158 loops=1)

  • Sort Key: transactions.timedate DESC
  • Sort Method: quicksort Memory: 106kB
2.          

CTE devices

3. 0.112 0.113 ↑ 1.0 237 1

ProjectSet (cost=0.00..1.22 rows=237 width=224) (actual time=0.008..0.113 rows=237 loops=1)

4. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

5. 0.177 17,588.201 ↓ 1.1 158 1

Nested Loop Left Join (cost=1,328,715.34..1,328,767.51 rows=150 width=2,083) (actual time=17,587.571..17,588.201 rows=158 loops=1)

6. 0.050 17,587.774 ↓ 2.0 50 1

Hash Left Join (cost=1,328,714.92..1,328,721.89 rows=25 width=2,039) (actual time=17,587.540..17,587.774 rows=50 loops=1)

  • Hash Cond: (transactions.sourcephonelinktype = "linkType".id)
7. 0.206 17,587.693 ↓ 2.0 50 1

Hash Right Join (cost=1,328,713.83..1,328,720.73 rows=25 width=1,807) (actual time=17,587.498..17,587.693 rows=50 loops=1)

  • Hash Cond: (devices.serial_list = (transactions.serial)::text)
  • Join Filter: ((devices.association_from_date_list IS NULL) OR ((transactions.timedate >= (devices.association_from_date_list)::timestamp without time zone) AND (transactions.timedate <= (devices.association_to_date_list)::timestamp without time zone)) OR ((devices.association_to_date_list IS NULL) AND ((devices.association_from_date_list)::timestamp without time zone <= transactions.timedate)))
8. 0.039 0.039 ↑ 1.0 237 1

CTE Scan on devices (cost=0.00..4.74 rows=237 width=224) (actual time=0.001..0.039 rows=237 loops=1)

9. 0.074 17,587.448 ↓ 2.0 50 1

Hash (cost=1,328,713.52..1,328,713.52 rows=25 width=1,679) (actual time=17,587.448..17,587.448 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
10. 0.224 17,587.374 ↓ 2.0 50 1

Merge Semi Join (cost=1,328,711.49..1,328,713.52 rows=25 width=1,679) (actual time=17,587.159..17,587.374 rows=50 loops=1)

  • Merge Cond: ((transactions.serial)::text = devices_1.serial_list)
11. 0.149 17,585.688 ↑ 1.0 50 1

Sort (cost=1,328,697.40..1,328,697.53 rows=50 width=1,679) (actual time=17,585.682..17,585.688 rows=50 loops=1)

  • Sort Key: transactions.serial
  • Sort Method: quicksort Memory: 50kB
12. 24.805 17,585.539 ↑ 1.0 50 1

Hash Join (cost=1,321,432.34..1,328,695.99 rows=50 width=1,679) (actual time=16,883.713..17,585.539 rows=50 loops=1)

  • Hash Cond: (transactions.id = a.id)
13. 73.181 677.586 ↑ 1.0 120,694 1

Hash Left Join (cost=41.35..6,988.17 rows=120,694 width=1,679) (actual time=0.555..677.586 rows=120,694 loops=1)

  • Hash Cond: (transactions.sourcephonevendorid = "phoneVendor".vendorid)
14. 90.783 604.384 ↑ 1.0 120,694 1

Hash Left Join (cost=39.58..6,669.42 rows=120,694 width=1,529) (actual time=0.527..604.384 rows=120,694 loops=1)

  • Hash Cond: (("customerReportedProduct".product_code_name)::text = ("productCodeName".code)::text)
15. 112.830 513.586 ↑ 1.0 120,694 1

Hash Left Join (cost=25.76..6,330.87 rows=120,694 width=1,093) (actual time=0.503..513.586 rows=120,694 loops=1)

  • Hash Cond: (("deviceReport".product_uuid)::text = ("customerReportedProduct".uuid)::text)
16. 73.542 400.686 ↑ 1.0 120,694 1

Hash Left Join (cost=22.36..6,000.88 rows=120,694 width=1,038) (actual time=0.422..400.686 rows=120,694 loops=1)

  • Hash Cond: (transactions.report_id = "deviceReport".id)
17. 51.950 326.893 ↑ 1.0 120,694 1

Hash Left Join (cost=5.75..5,663.93 rows=120,694 width=993) (actual time=0.162..326.893 rows=120,694 loops=1)

  • Hash Cond: (transactions.sourcephonemodelid = "phoneModels".id)
18. 72.893 274.898 ↑ 1.0 120,694 1

Hash Left Join (cost=2.47..5,343.78 rows=120,694 width=961) (actual time=0.067..274.898 rows=120,694 loops=1)

  • Hash Cond: (transactions.status = "transactionStatus".id)
19. 172.478 201.992 ↑ 1.0 120,694 1

Hash Left Join (cost=1.32..4,833.99 rows=120,694 width=537) (actual time=0.049..201.992 rows=120,694 loops=1)

  • Hash Cond: (transactions.type = "transactionType".id)
20. 29.493 29.493 ↑ 1.0 120,694 1

Seq Scan on transactions (cost=0.00..4,419.94 rows=120,694 width=113) (actual time=0.020..29.493 rows=120,694 loops=1)

21. 0.008 0.021 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=424) (actual time=0.021..0.021 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.013 0.013 ↑ 1.0 14 1

Seq Scan on enum_transactiontype "transactionType" (cost=0.00..1.14 rows=14 width=424) (actual time=0.011..0.013 rows=14 loops=1)

23. 0.004 0.013 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=424) (actual time=0.013..0.013 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.009 0.009 ↑ 1.0 7 1

Seq Scan on enum_status "transactionStatus" (cost=0.00..1.07 rows=7 width=424) (actual time=0.008..0.009 rows=7 loops=1)

25. 0.024 0.045 ↑ 1.0 101 1

Hash (cost=2.01..2.01 rows=101 width=32) (actual time=0.045..0.045 rows=101 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
26. 0.021 0.021 ↑ 1.0 101 1

Seq Scan on phone_models "phoneModels" (cost=0.00..2.01 rows=101 width=32) (actual time=0.012..0.021 rows=101 loops=1)

27. 0.099 0.251 ↓ 1.1 405 1

Hash (cost=11.83..11.83 rows=383 width=45) (actual time=0.250..0.251 rows=405 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
28. 0.152 0.152 ↓ 1.1 405 1

Seq Scan on device_reports "deviceReport" (cost=0.00..11.83 rows=383 width=45) (actual time=0.012..0.152 rows=405 loops=1)

29. 0.032 0.070 ↓ 1.2 73 1

Hash (cost=2.62..2.62 rows=62 width=55) (actual time=0.070..0.070 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
30. 0.038 0.038 ↓ 1.2 73 1

Seq Scan on customer_reported_products "customerReportedProduct" (cost=0.00..2.62 rows=62 width=55) (actual time=0.013..0.038 rows=73 loops=1)

31. 0.005 0.015 ↑ 28.3 6 1

Hash (cost=11.70..11.70 rows=170 width=436) (actual time=0.015..0.015 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.010 0.010 ↑ 28.3 6 1

Seq Scan on product_code_name "productCodeName" (cost=0.00..11.70 rows=170 width=436) (actual time=0.010..0.010 rows=6 loops=1)

33. 0.009 0.021 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=150) (actual time=0.021..0.021 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.012 0.012 ↑ 1.0 34 1

Seq Scan on phone_vendors "phoneVendor" (cost=0.00..1.34 rows=34 width=150) (actual time=0.009..0.012 rows=34 loops=1)

35. 0.017 16,883.148 ↑ 1.0 50 1

Hash (cost=1,321,390.37..1,321,390.37 rows=50 width=8) (actual time=16,883.148..16,883.148 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.005 16,883.131 ↑ 1.0 50 1

Limit (cost=1,321,389.62..1,321,389.87 rows=50 width=8) (actual time=16,883.065..16,883.131 rows=50 loops=1)

37. 0.034 16,883.126 ↑ 7,252.6 50 1

Unique (cost=1,321,389.62..1,323,202.77 rows=362,630 width=8) (actual time=16,883.063..16,883.126 rows=50 loops=1)

38. 413.004 16,883.092 ↑ 2,339.5 155 1

Sort (cost=1,321,389.62..1,322,296.19 rows=362,630 width=8) (actual time=16,883.061..16,883.092 rows=155 loops=1)

  • Sort Key: a.id
  • Sort Method: external merge Disk: 14624kB
39. 95.564 16,470.088 ↓ 2.3 825,245 1

Subquery Scan on a (cost=0.84..1,282,944.61 rows=362,630 width=8) (actual time=0.363..16,470.088 rows=825,245 loops=1)

40. 535.123 16,374.524 ↓ 2.3 825,245 1

Nested Loop Left Join (cost=0.84..1,279,318.31 rows=362,630 width=4,195) (actual time=0.363..16,374.524 rows=825,245 loops=1)

41. 5,115.270 14,995.019 ↓ 2.0 120,626 1

Nested Loop Left Join (cost=0.42..1,172,812.68 rows=60,347 width=16) (actual time=0.340..14,995.019 rows=120,626 loops=1)

  • Join Filter: ((devices_2.serial_list = (transactions_1.serial)::text) AND ((devices_2.association_from_date_list IS NULL) OR ((transactions_1.timedate >= (devices_2.association_from_date_list)::timestamp without time zone) AND (transactions_1.timedate <= (devices_2.association_to_date_list)::timestamp without time zone)) OR ((devices_2.association_to_date_list IS NULL) AND ((devices_2.association_from_date_list)::timestamp without time zone <= transactions_1.timedate))))
  • Rows Removed by Join Filter: 28482252
42. 3,166.383 6,260.969 ↓ 2.0 120,626 1

Nested Loop Semi Join (cost=0.42..529,209.55 rows=60,347 width=27) (actual time=0.274..6,260.969 rows=120,626 loops=1)

  • Join Filter: ((transactions_1.serial)::text = devices_3.serial_list)
  • Rows Removed by Join Filter: 22310893
43. 197.930 197.930 ↑ 1.0 120,694 1

Index Scan Backward using t_timedate on transactions transactions_1 (cost=0.42..16,427.35 rows=120,694 width=59) (actual time=0.013..197.930 rows=120,694 loops=1)

44. 2,896.656 2,896.656 ↑ 1.3 186 120,694

CTE Scan on devices devices_3 (cost=0.00..4.74 rows=237 width=32) (actual time=0.000..0.024 rows=186 loops=120,694)

45. 3,618.780 3,618.780 ↑ 1.0 237 120,626

CTE Scan on devices devices_2 (cost=0.00..4.74 rows=237 width=96) (actual time=0.000..0.030 rows=237 loops=120,626)

46. 844.382 844.382 ↑ 6.5 6 120,626

Index Only Scan using id_idx on case_details "caseDetails_1" (cost=0.42..1.37 rows=39 width=8) (actual time=0.004..0.007 rows=6 loops=120,626)

  • Index Cond: (transaction_id = transactions_1.id)
  • Heap Fetches: 724892
47. 1.410 1.462 ↑ 1.1 211 1

Sort (cost=14.09..14.68 rows=237 width=32) (actual time=1.446..1.462 rows=211 loops=1)

  • Sort Key: devices_1.serial_list
  • Sort Method: quicksort Memory: 39kB
48. 0.052 0.052 ↑ 1.0 237 1

CTE Scan on devices devices_1 (cost=0.00..4.74 rows=237 width=32) (actual time=0.003..0.052 rows=237 loops=1)

49. 0.005 0.031 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=232) (actual time=0.031..0.031 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.026 0.026 ↑ 1.0 4 1

Seq Scan on enum_linktype "linkType" (cost=0.00..1.04 rows=4 width=232) (actual time=0.025..0.026 rows=4 loops=1)

51. 0.250 0.250 ↑ 13.0 3 50

Index Scan using id_idx on case_details "caseDetails" (cost=0.42..1.37 rows=39 width=36) (actual time=0.004..0.005 rows=3 loops=50)

  • Index Cond: (transaction_id = transactions.id)
Planning time : 14.729 ms
Execution time : 17,636.149 ms