explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PBjI

Settings
# exclusive inclusive rows x rows loops node
1. 0.144 557.825 ↓ 1.5 50 1

Limit (cost=59,471.54..59,471.62 rows=33 width=15,832) (actual time=557.672..557.825 rows=50 loops=1)

2. 29.899 557.681 ↓ 1.5 50 1

Sort (cost=59,471.54..59,471.62 rows=33 width=15,832) (actual time=557.671..557.681 rows=50 loops=1)

  • Sort Key: transactio0_.created_timestamp DESC
  • Sort Method: top-N heapsort Memory: 265kB
3. 10.799 527.782 ↓ 231.0 7,623 1

Nested Loop Left Join (cost=11,625.87..59,470.71 rows=33 width=15,832) (actual time=72.368..527.782 rows=7,623 loops=1)

4. 15.095 478.868 ↓ 231.0 7,623 1

Nested Loop Left Join (cost=11,625.59..59,456.28 rows=33 width=14,428) (actual time=72.348..478.868 rows=7,623 loops=1)

5. 9.228 402.789 ↓ 231.0 7,623 1

Nested Loop Left Join (cost=11,625.18..59,429.31 rows=33 width=13,040) (actual time=72.326..402.789 rows=7,623 loops=1)

6. 19.993 332.577 ↓ 231.0 7,623 1

Nested Loop (cost=11,624.77..59,402.33 rows=33 width=11,652) (actual time=72.300..332.577 rows=7,623 loops=1)

  • Join Filter: ((((transactio0_.supplier_site_id)::text = (ua.site_id)::text) OR ((company4_.main_site_id)::text = (ua.site_id)::text)) AND (((ua.type)::text = 'ALL'::text) OR ((ua.type)::text = (transactio0_.application)::text)))
  • Rows Removed by Join Filter: 30565
7. 0.020 0.020 ↑ 1.0 1 1

Index Scan using idx_user_access_2_user on tbl_user_access ua (cost=0.29..8.30 rows=1 width=1,164) (actual time=0.018..0.020 rows=1 loops=1)

  • Index Cond: ((user_id)::text = '58375063-937d-4694-9282-dd1b34e587ae'::text)
  • Filter: active
8. 17.414 312.564 ↑ 1.6 38,188 1

Gather (cost=11,624.49..58,203.57 rows=59,523 width=10,488) (actual time=72.117..312.564 rows=38,188 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 20.564 295.150 ↑ 1.9 12,729 3 / 3

Hash Left Join (cost=10,624.49..51,251.27 rows=24,801 width=10,488) (actual time=69.009..295.150 rows=12,729 loops=3)

  • Hash Cond: ((site2_.company_id)::text = (company3_.id)::text)
10. 20.323 272.717 ↑ 1.9 12,729 3 / 3

Nested Loop Left Join (cost=10,442.76..51,004.33 rows=24,801 width=9,092) (actual time=67.108..272.717 rows=12,729 loops=3)

11. 15.694 176.018 ↑ 1.9 12,729 3 / 3

Hash Left Join (cost=10,442.48..43,229.19 rows=24,801 width=7,868) (actual time=67.080..176.018 rows=12,729 loops=3)

  • Hash Cond: ((site1_.id)::text = (company9_.main_site_id)::text)
12. 18.865 156.684 ↑ 1.9 12,729 3 / 3

Hash Left Join (cost=10,260.75..42,819.96 rows=24,801 width=6,472) (actual time=63.413..156.684 rows=12,729 loops=3)

  • Hash Cond: ((site1_.company_id)::text = (company4_.id)::text)
13. 52.758 135.546 ↑ 1.9 12,729 3 / 3

Hash Left Join (cost=10,079.03..42,573.02 rows=24,801 width=5,076) (actual time=61.113..135.546 rows=12,729 loops=3)

  • Hash Cond: ((transactio0_.supplier_site_id)::text = (site1_.id)::text)
14. 22.170 22.170 ↑ 1.9 12,729 3 / 3

Parallel Index Scan using tbl_trx_status on tbl_transaction transactio0_ (cost=0.43..23,133.41 rows=24,801 width=1,076) (actual time=0.022..22.170 rows=12,729 loops=3)

  • Index Cond: ((status)::text = 'TODO'::text)
  • Filter: ((application)::text = ANY ('{MRT,ORD}'::text[]))
15. 6.593 60.618 ↓ 1.0 4,758 3 / 3

Hash (cost=7,685.23..7,685.23 rows=4,750 width=4,000) (actual time=60.618..60.618 rows=4,758 loops=3)

  • Buckets: 1024 Batches: 8 Memory Usage: 288kB
16. 5.190 54.025 ↓ 1.0 4,758 3 / 3

Nested Loop Left Join (cost=985.28..7,685.23 rows=4,750 width=4,000) (actual time=4.923..54.025 rows=4,758 loops=3)

17. 7.579 15.529 ↓ 1.0 4,758 3 / 3

Hash Right Join (cost=984.88..3,803.13 rows=4,750 width=2,612) (actual time=4.897..15.529 rows=4,758 loops=3)

  • Hash Cond: ((ordpayment5_.site_id)::text = (site1_.id)::text)
18. 3.216 3.216 ↓ 1.0 4,758 3 / 3

Seq Scan on tbl_payment_detail ordpayment5_ (cost=0.00..437.77 rows=4,751 width=1,388) (actual time=0.098..3.216 rows=4,758 loops=3)

  • Filter: ((application)::text = 'ORD'::text)
  • Rows Removed by Filter: 4758
19. 2.959 4.734 ↓ 1.0 4,758 3 / 3

Hash (cost=201.50..201.50 rows=4,750 width=1,224) (actual time=4.734..4.734 rows=4,758 loops=3)

  • Buckets: 4096 Batches: 2 Memory Usage: 584kB
20. 1.775 1.775 ↓ 1.0 4,758 3 / 3

Seq Scan on tbl_site site1_ (cost=0.00..201.50 rows=4,750 width=1,224) (actual time=0.011..1.775 rows=4,758 loops=3)

21. 33.306 33.306 ↑ 1.0 1 14,274 / 3

Index Scan using tbl_payment_detail_application_site_id_key on tbl_payment_detail mrtpayment6_ (cost=0.41..0.82 rows=1 width=1,388) (actual time=0.007..0.007 rows=1 loops=14,274)

  • Index Cond: (((application)::text = 'MRT'::text) AND ((site1_.id)::text = (site_id)::text))
22. 1.318 2.273 ↓ 1.0 2,611 3 / 3

Hash (cost=149.10..149.10 rows=2,610 width=1,396) (actual time=2.272..2.273 rows=2,611 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 909kB
23. 0.955 0.955 ↓ 1.0 2,611 3 / 3

Seq Scan on tbl_company company4_ (cost=0.00..149.10 rows=2,610 width=1,396) (actual time=0.011..0.955 rows=2,611 loops=3)

24. 3.227 3.640 ↑ 1.0 2,576 3 / 3

Hash (cost=149.10..149.10 rows=2,610 width=1,396) (actual time=3.640..3.640 rows=2,576 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 899kB
25. 0.413 0.413 ↓ 1.0 2,611 3 / 3

Seq Scan on tbl_company company9_ (cost=0.00..149.10 rows=2,610 width=1,396) (actual time=0.004..0.413 rows=2,611 loops=3)

26. 76.376 76.376 ↑ 1.0 1 38,188 / 3

Index Scan using tbl_site_pkey on tbl_site site2_ (cost=0.28..0.31 rows=1 width=1,224) (actual time=0.006..0.006 rows=1 loops=38,188)

  • Index Cond: ((transactio0_.client_site_id)::text = (id)::text)
27. 1.476 1.869 ↓ 1.0 2,611 3 / 3

Hash (cost=149.10..149.10 rows=2,610 width=1,396) (actual time=1.869..1.869 rows=2,611 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 909kB
28. 0.393 0.393 ↓ 1.0 2,611 3 / 3

Seq Scan on tbl_company company3_ (cost=0.00..149.10 rows=2,610 width=1,396) (actual time=0.004..0.393 rows=2,611 loops=3)

29. 60.984 60.984 ↑ 1.0 1 7,623

Index Scan using tbl_payment_detail_application_site_id_key on tbl_payment_detail ordpayment7_ (cost=0.41..0.82 rows=1 width=1,388) (actual time=0.008..0.008 rows=1 loops=7,623)

  • Index Cond: (((application)::text = 'ORD'::text) AND ((site2_.id)::text = (site_id)::text))
30. 60.984 60.984 ↑ 1.0 1 7,623

Index Scan using tbl_payment_detail_application_site_id_key on tbl_payment_detail mrtpayment8_ (cost=0.41..0.82 rows=1 width=1,388) (actual time=0.008..0.008 rows=1 loops=7,623)

  • Index Cond: (((application)::text = 'MRT'::text) AND ((site2_.id)::text = (site_id)::text))
31. 38.115 38.115 ↑ 1.0 1 7,623

Index Scan using idx_company_2_main_site on tbl_company company10_ (cost=0.28..0.43 rows=1 width=1,396) (actual time=0.005..0.005 rows=1 loops=7,623)

  • Index Cond: ((site2_.id)::text = (main_site_id)::text)
Planning time : 10.288 ms
Execution time : 558.380 ms