explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LgVN : legs

Settings
# exclusive inclusive rows x rows loops node
1. 253.545 2,476.868 ↑ 56.8 515,642 1

Merge Right Join (cost=102,002.80..541,600.60 rows=29,270,113 width=184) (actual time=2,132.324..2,476.868 rows=515,642 loops=1)

  • Merge Cond: (xaction.transaction_id = r.transaction_id)
2.          

CTE relationships

3. 121.078 1,403.922 ↓ 1.5 181,594 1

Hash Left Join (cost=26,448.29..49,654.76 rows=124,089 width=86) (actual time=1,236.868..1,403.922 rows=181,594 loops=1)

  • Hash Cond: ((rt.ticketno)::text = (sdt.ticket_number)::text)
4.          

Initplan (for Hash Left Join)

5. 0.894 0.894 ↑ 1,000.0 1 1

Function Scan on find_location (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.893..0.894 rows=1 loops=1)

6. 0.730 0.730 ↑ 1,000.0 1 1

Function Scan on find_location find_location_1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.730..0.730 rows=1 loops=1)

7. 120.536 120.536 ↑ 1,000.0 1 1

Function Scan on find_date (cost=0.25..10.25 rows=1,000 width=8) (actual time=120.535..120.536 rows=1 loops=1)

8. 117.305 117.305 ↑ 1,000.0 1 1

Function Scan on find_date find_date_1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=117.305..117.305 rows=1 loops=1)

9. 116.838 116.838 ↑ 1,000.0 1 1

Function Scan on find_date find_date_2 (cost=0.25..10.25 rows=1,000 width=8) (actual time=116.837..116.838 rows=1 loops=1)

10. 116.868 116.868 ↑ 1,000.0 1 1

Function Scan on find_date find_date_3 (cost=0.25..10.25 rows=1,000 width=4) (actual time=116.868..116.868 rows=1 loops=1)

11. 0.001 102.279 ↓ 0.0 0 1

Limit (cost=1.02..106.13 rows=1 width=4) (actual time=102.279..102.279 rows=0 loops=1)

12. 7.186 102.278 ↓ 0.0 0 1

Hash Join (cost=1.02..20,708.08 rows=197 width=4) (actual time=102.278..102.278 rows=0 loops=1)

  • Hash Cond: ((e.service_number)::text = (c.code)::text)
13. 95.081 95.081 ↑ 1.0 45,937 1

Seq Scan on reserved_travels e (cost=0.00..20,583.42 rows=46,265 width=3) (actual time=0.023..95.081 rows=45,937 loops=1)

  • Filter: (etl_batch_file_id = 36)
  • Rows Removed by Filter: 45,937
14. 0.006 0.011 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=72) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on dim_services c (cost=0.00..1.01 rows=1 width=72) (actual time=0.005..0.005 rows=1 loops=1)

16. 97.445 97.445 ↓ 0.0 0 1

Function Scan on find_customer (cost=0.25..10.25 rows=1,000 width=4) (actual time=97.445..97.445 rows=0 loops=1)

17. 186.810 186.810 ↓ 0.0 0 1

Function Scan on find_brand (cost=0.25..10.25 rows=1,000 width=4) (actual time=186.810..186.810 rows=0 loops=1)

18. 0.001 106.414 ↓ 0.0 0 1

Limit (cost=1.02..400.20 rows=1 width=4) (actual time=106.414..106.414 rows=0 loops=1)

19. 7.318 106.413 ↓ 0.0 0 1

Hash Join (cost=1.02..20,758.46 rows=52 width=4) (actual time=106.413..106.413 rows=0 loops=1)

  • Hash Cond: ((e_1.flight_code)::text = (c_1.flight_code)::text)
20. 98.733 98.733 ↑ 1.0 45,937 1

Seq Scan on reserved_travels e_1 (cost=0.00..20,583.42 rows=46,265 width=3) (actual time=0.011..98.733 rows=45,937 loops=1)

  • Filter: (etl_batch_file_id = 36)
  • Rows Removed by Filter: 45,937
21. 0.004 0.362 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=520) (actual time=0.362..0.362 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.358 0.358 ↑ 1.0 1 1

Seq Scan on dim_flights c_1 (cost=0.00..1.01 rows=1 width=520) (actual time=0.357..0.358 rows=1 loops=1)

23. 107.670 107.670 ↑ 1.0 45,937 1

Seq Scan on reserved_travels rt (cost=0.00..20,583.42 rows=46,265 width=21) (actual time=61.155..107.670 rows=45,937 loops=1)

  • Filter: (etl_batch_file_id = 36)
  • Rows Removed by Filter: 45,937
24. 36.144 209.055 ↓ 1.0 95,354 1

Hash (cost=24,139.90..24,139.90 rows=93,684 width=17) (actual time=209.055..209.055 rows=95,354 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 2,820kB
25. 49.791 172.911 ↓ 1.0 95,354 1

Hash Join (cost=2,504.79..24,139.90 rows=93,684 width=17) (actual time=49.330..172.911 rows=95,354 loops=1)

  • Hash Cond: ((ert.ticketno)::text = (sdt.ticket_number)::text)
26. 74.159 74.159 ↑ 1.0 91,874 1

Seq Scan on reserved_travels ert (cost=0.00..20,353.74 rows=91,874 width=9) (actual time=0.011..74.159 rows=91,874 loops=1)

27. 11.836 48.961 ↑ 1.0 35,617 1

Hash (cost=2,059.58..2,059.58 rows=35,617 width=17) (actual time=48.961..48.961 rows=35,617 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,217kB
28. 15.407 37.125 ↑ 1.0 35,617 1

Hash Join (cost=1,077.90..2,059.58 rows=35,617 width=17) (actual time=17.355..37.125 rows=35,617 loops=1)

  • Hash Cond: (t.ticket_id = sdt.ticket_id)
29. 4.627 4.627 ↑ 1.0 35,617 1

Seq Scan on fact_ticket_transactions t (cost=0.00..888.17 rows=35,617 width=8) (actual time=0.007..4.627 rows=35,617 loops=1)

30. 9.652 17.091 ↑ 1.0 34,929 1

Hash (cost=641.29..641.29 rows=34,929 width=13) (actual time=17.091..17.091 rows=34,929 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,047kB
31. 7.439 7.439 ↑ 1.0 34,929 1

Seq Scan on dim_tickets sdt (cost=0.00..641.29 rows=34,929 width=13) (actual time=0.009..7.439 rows=34,929 loops=1)

32.          

CTE xaction

33. 22.938 176.671 ↓ 1.0 47,677 1

Hash Join (cost=2,504.79..23,733.47 rows=47,176 width=17) (actual time=114.358..176.671 rows=47,677 loops=1)

  • Hash Cond: ((ert_1.ticketno)::text = (sdt_1.ticket_number)::text)
34. 102.681 102.681 ↑ 1.0 45,937 1

Seq Scan on reserved_travels ert_1 (cost=0.00..20,583.42 rows=46,265 width=9) (actual time=63.019..102.681 rows=45,937 loops=1)

  • Filter: (etl_batch_file_id = 36)
  • Rows Removed by Filter: 45,937
35. 12.843 51.052 ↑ 1.0 35,617 1

Hash (cost=2,059.58..2,059.58 rows=35,617 width=17) (actual time=51.052..51.052 rows=35,617 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,321kB
36. 15.613 38.209 ↑ 1.0 35,617 1

Hash Join (cost=1,077.90..2,059.58 rows=35,617 width=17) (actual time=18.044..38.209 rows=35,617 loops=1)

  • Hash Cond: (t_1.ticket_id = sdt_1.ticket_id)
37. 4.805 4.805 ↑ 1.0 35,617 1

Seq Scan on fact_ticket_transactions t_1 (cost=0.00..888.17 rows=35,617 width=8) (actual time=0.010..4.805 rows=35,617 loops=1)

38. 10.011 17.791 ↑ 1.0 34,929 1

Hash (cost=641.29..641.29 rows=34,929 width=13) (actual time=17.791..17.791 rows=34,929 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,150kB
39. 7.780 7.780 ↑ 1.0 34,929 1

Seq Scan on dim_tickets sdt_1 (cost=0.00..641.29 rows=34,929 width=13) (actual time=0.009..7.780 rows=34,929 loops=1)

40. 27.263 230.945 ↓ 1.0 47,677 1

Sort (cost=4,605.74..4,723.68 rows=47,176 width=4) (actual time=226.326..230.945 rows=47,677 loops=1)

  • Sort Key: xaction.transaction_id
  • Sort Method: quicksort Memory: 3,601kB
41. 203.682 203.682 ↓ 1.0 47,677 1

CTE Scan on xaction (cost=0.00..943.52 rows=47,176 width=4) (actual time=114.361..203.682 rows=47,677 loops=1)

42. 49.837 1,992.378 ↓ 4.2 515,642 1

Materialize (cost=24,008.84..24,629.28 rows=124,089 width=184) (actual time=1,905.991..1,992.378 rows=515,642 loops=1)

43. 209.521 1,942.541 ↓ 1.5 181,594 1

Sort (cost=24,008.84..24,319.06 rows=124,089 width=184) (actual time=1,905.986..1,942.541 rows=181,594 loops=1)

  • Sort Key: r.transaction_id
  • Sort Method: external merge Disk: 14,792kB
44. 1,733.020 1,733.020 ↓ 1.5 181,594 1

CTE Scan on relationships r (cost=0.00..2,481.78 rows=124,089 width=184) (actual time=1,236.877..1,733.020 rows=181,594 loops=1)