explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IEXE : extractions

Settings
# exclusive inclusive rows x rows loops node
1. 3,240.736 23,631.012 ↓ 2.3 825,245 1

Sort (cost=2,120,977.73..2,121,884.31 rows=362,630 width=2,083) (actual time=23,241.880..23,631.012 rows=825,245 loops=1)

  • Sort Key: transactions.timedate DESC
  • Sort Method: external merge Disk: 245736kB
2.          

CTE devices

3. 0.118 0.119 ↑ 1.0 237 1

ProjectSet (cost=0.00..1.22 rows=237 width=224) (actual time=0.011..0.119 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.001..0.001 rows=1 loops=1)

5. 542.742 20,390.276 ↓ 2.3 825,245 1

Hash Left Join (cost=1,384,017.44..1,433,054.01 rows=362,630 width=2,083) (actual time=17,423.296..20,390.276 rows=825,245 loops=1)

  • Hash Cond: (transactions.sourcephonelinktype = "linkType".id)
6. 2,371.432 19,847.511 ↓ 2.3 825,245 1

Hash Right Join (cost=1,384,016.35..1,428,471.96 rows=362,630 width=1,843) (actual time=17,423.200..19,847.511 rows=825,245 loops=1)

  • Hash Cond: ("caseDetails".transaction_id = transactions.id)
7. 73.946 73.946 ↑ 1.0 725,219 1

Seq Scan on case_details "caseDetails" (cost=0.00..13,279.59 rows=725,259 width=36) (actual time=0.025..73.946 rows=725,219 loops=1)

8. 180.926 17,402.133 ↓ 2.0 120,626 1

Hash (cost=1,369,766.01..1,369,766.01 rows=60,347 width=1,807) (actual time=17,402.133..17,402.133 rows=120,626 loops=1)

  • Buckets: 4096 Batches: 32 Memory Usage: 1109kB
9. 272.136 17,221.207 ↓ 2.0 120,626 1

Hash Left Join (cost=1,342,690.84..1,369,766.01 rows=60,347 width=1,807) (actual time=15,698.404..17,221.207 rows=120,626 loops=1)

  • Hash Cond: ((transactions.serial)::text = devices.serial_list)
  • 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)))
  • Rows Removed by Join Filter: 14516
10. 1,045.634 16,948.979 ↓ 2.0 120,626 1

Hash Join (cost=1,342,683.14..1,365,472.92 rows=60,347 width=1,679) (actual time=15,698.299..16,948.979 rows=120,626 loops=1)

  • Hash Cond: (a.id = transactions.id)
11. 84.833 14,994.429 ↑ 3.0 120,626 1

Unique (cost=1,321,389.62..1,323,202.77 rows=362,630 width=8) (actual time=14,780.379..14,994.429 rows=120,626 loops=1)

12. 471.463 14,909.596 ↓ 2.3 825,245 1

Sort (cost=1,321,389.62..1,322,296.19 rows=362,630 width=8) (actual time=14,780.376..14,909.596 rows=825,245 loops=1)

  • Sort Key: a.id
  • Sort Method: external merge Disk: 14624kB
13. 89.592 14,438.133 ↓ 2.3 825,245 1

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

14. 586.463 14,348.541 ↓ 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.150..14,348.541 rows=825,245 loops=1)

15. 4,558.522 13,158.948 ↓ 2.0 120,626 1

Nested Loop Left Join (cost=0.42..1,172,812.68 rows=60,347 width=16) (actual time=0.119..13,158.948 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
16. 2,874.564 5,464.150 ↓ 2.0 120,626 1

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

  • Join Filter: ((transactions_1.serial)::text = devices_3.serial_list)
  • Rows Removed by Join Filter: 22310893
17. 175.706 175.706 ↑ 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.015..175.706 rows=120,694 loops=1)

18. 2,413.880 2,413.880 ↑ 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.020 rows=186 loops=120,694)

19. 3,136.276 3,136.276 ↑ 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.026 rows=237 loops=120,626)

20. 603.130 603.130 ↑ 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.005 rows=6 loops=120,626)

  • Index Cond: (transaction_id = transactions_1.id)
  • Heap Fetches: 724892
21. 269.824 908.916 ↓ 2.0 120,626 1

Hash (cost=7,986.18..7,986.18 rows=60,347 width=1,679) (actual time=908.916..908.916 rows=120,626 loops=1)

  • Buckets: 4096 Batches: 32 Memory Usage: 1105kB
22. 82.652 639.092 ↓ 2.0 120,626 1

Hash Join (cost=51.18..7,986.18 rows=60,347 width=1,679) (actual time=1.075..639.092 rows=120,626 loops=1)

  • Hash Cond: ((transactions.serial)::text = devices_1.serial_list)
23. 54.183 555.949 ↑ 1.0 120,694 1

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

  • Hash Cond: (transactions.sourcephonevendorid = "phoneVendor".vendorid)
24. 77.495 501.741 ↑ 1.0 120,694 1

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

  • Hash Cond: (("customerReportedProduct".product_code_name)::text = ("productCodeName".code)::text)
25. 88.992 424.220 ↑ 1.0 120,694 1

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

  • Hash Cond: (("deviceReport".product_uuid)::text = ("customerReportedProduct".uuid)::text)
26. 61.653 335.149 ↑ 1.0 120,694 1

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

  • Hash Cond: (transactions.report_id = "deviceReport".id)
27. 41.984 273.226 ↑ 1.0 120,694 1

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

  • Hash Cond: (transactions.sourcephonemodelid = "phoneModels".id)
28. 58.172 231.195 ↑ 1.0 120,694 1

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

  • Hash Cond: (transactions.status = "transactionStatus".id)
29. 146.181 173.006 ↑ 1.0 120,694 1

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

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

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

31. 0.014 0.026 ↑ 1.0 14 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.012 0.012 ↑ 1.0 14 1

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

33. 0.005 0.017 ↑ 1.0 7 1

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

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

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

35. 0.025 0.047 ↑ 1.0 101 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
36. 0.022 0.022 ↑ 1.0 101 1

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

37. 0.103 0.270 ↓ 1.1 405 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
38. 0.167 0.167 ↓ 1.1 405 1

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

39. 0.035 0.079 ↓ 1.2 73 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
40. 0.044 0.044 ↓ 1.2 73 1

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

41. 0.013 0.026 ↑ 28.3 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.013 0.013 ↑ 28.3 6 1

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

43. 0.009 0.025 ↑ 1.0 34 1

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

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

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

45. 0.069 0.491 ↓ 1.2 237 1

Hash (cost=7.33..7.33 rows=200 width=32) (actual time=0.491..0.491 rows=237 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
46. 0.165 0.422 ↓ 1.2 237 1

HashAggregate (cost=5.33..7.33 rows=200 width=32) (actual time=0.375..0.422 rows=237 loops=1)

  • Group Key: devices_1.serial_list
47. 0.257 0.257 ↑ 1.0 237 1

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

48. 0.051 0.092 ↑ 1.0 237 1

Hash (cost=4.74..4.74 rows=237 width=224) (actual time=0.092..0.092 rows=237 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
49. 0.041 0.041 ↑ 1.0 237 1

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

50. 0.005 0.023 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.018 0.018 ↑ 1.0 4 1

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

Planning time : 15.533 ms
Execution time : 23,733.962 ms