explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7jEh

Settings
# exclusive inclusive rows x rows loops node
1. 44.048 5,288.660 ↓ 18.0 18 1

Nested Loop Left Join (cost=10,066.16..2,168,478.66 rows=1 width=609) (actual time=390.855..5,288.660 rows=18 loops=1)

  • Filter: ((i_ts.id IS NULL) OR ((SubPlan 5) IS NOT NULL))
  • Rows Removed by Filter: 26175
2. 5.728 5,112.545 ↓ 26,193.0 26,193 1

Nested Loop (cost=10,065.73..2,168,429.17 rows=1 width=254) (actual time=42.112..5,112.545 rows=26,193 loops=1)

3. 22.904 5,054.431 ↓ 26,193.0 26,193 1

Nested Loop Left Join (cost=10,065.45..2,168,421.88 rows=1 width=230) (actual time=42.101..5,054.431 rows=26,193 loops=1)

4. 49.181 5,005.334 ↓ 26,193.0 26,193 1

Nested Loop Left Join (cost=10,065.30..2,168,416.99 rows=1 width=231) (actual time=42.095..5,005.334 rows=26,193 loops=1)

  • Join Filter: ((i_dc.document_type_xc)::text = (pcd.document_type_xc)::text)
  • Rows Removed by Join Filter: 157160
5. 2.900 4,903.767 ↓ 26,193.0 26,193 1

Nested Loop (cost=10,065.30..2,168,415.83 rows=1 width=262) (actual time=42.085..4,903.767 rows=26,193 loops=1)

6. 7.647 4,848.481 ↓ 26,193.0 26,193 1

Nested Loop (cost=10,065.03..2,168,408.39 rows=1 width=232) (actual time=42.076..4,848.481 rows=26,193 loops=1)

7. 13.380 4,814.641 ↓ 26,193.0 26,193 1

Nested Loop (cost=10,064.88..2,168,403.50 rows=1 width=212) (actual time=42.072..4,814.641 rows=26,193 loops=1)

8. 10.769 4,775.068 ↓ 26,193.0 26,193 1

Nested Loop (cost=10,064.74..2,168,398.60 rows=1 width=192) (actual time=42.066..4,775.068 rows=26,193 loops=1)

9. 60.167 4,711.913 ↓ 26,193.0 26,193 1

Nested Loop (cost=10,064.17..2,168,390.91 rows=1 width=188) (actual time=42.059..4,711.913 rows=26,193 loops=1)

  • Join Filter: ((bs_dep.country_id <> bs_dest.country_id) AND ((bs_dep.country_id = 6) OR (bs_dest.country_id = 6)))
  • Rows Removed by Join Filter: 72187
10. 79.414 4,553.366 ↓ 62.8 98,380 1

Nested Loop (cost=10,063.89..2,156,905.27 rows=1,567 width=184) (actual time=34.230..4,553.366 rows=98,380 loops=1)

11. 229.525 4,375.572 ↓ 62.8 98,380 1

Nested Loop (cost=10,063.60..2,145,447.06 rows=1,567 width=180) (actual time=34.222..4,375.572 rows=98,380 loops=1)

12. 39.961 3,257.234 ↓ 64.0 98,757 1

Nested Loop (cost=10,063.04..2,121,758.87 rows=1,542 width=139) (actual time=34.200..3,257.234 rows=98,757 loops=1)

13. 78.059 3,019.759 ↓ 64.0 98,757 1

Nested Loop (cost=10,062.47..2,109,886.31 rows=1,542 width=135) (actual time=34.193..3,019.759 rows=98,757 loops=1)

14. 31.194 2,842.943 ↓ 64.0 98,757 1

Nested Loop (cost=10,062.19..2,098,616.10 rows=1,542 width=135) (actual time=34.185..2,842.943 rows=98,757 loops=1)

15. 49.877 2,182.457 ↓ 59.7 104,882 1

Nested Loop (cost=10,061.62..2,085,173.37 rows=1,756 width=48) (actual time=34.176..2,182.457 rows=104,882 loops=1)

16. 0.000 1,607.465 ↓ 39.1 105,023 1

Nested Loop (cost=10,061.06..2,064,607.91 rows=2,683 width=32) (actual time=34.163..1,607.465 rows=105,023 loops=1)

17. 41.737 948.251 ↓ 82.9 172,373 1

Nested Loop (cost=10,060.50..1,782,107.38 rows=2,079 width=8) (actual time=31.128..948.251 rows=172,373 loops=1)

18. 166.323 194.470 ↓ 1.0 178,011 1

Bitmap Heap Scan on trip tr (cost=10,059.93..354,882.48 rows=175,934 width=12) (actual time=31.094..194.470 rows=178,011 loops=1)

  • Recheck Cond: (departure_datetime >= (CURRENT_TIMESTAMP - '2 days'::interval))
  • Heap Blocks: exact=22337
19. 28.147 28.147 ↓ 1.1 190,190 1

Bitmap Index Scan on "trip$departure_datetime$idx" (cost=0.00..10,015.94 rows=175,934 width=0) (actual time=28.147..28.147 rows=190,190 loops=1)

  • Index Cond: (departure_datetime >= (CURRENT_TIMESTAMP - '2 days'::interval))
20. 712.044 712.044 ↑ 1.0 1 178,011

Index Scan using "trip_route$pk" on trip_route tre (cost=0.57..8.11 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=178,011)

  • Index Cond: (id = tr.last_trip_route_stop_id)
  • Filter: (planned_arrival_time >= CURRENT_TIMESTAMP)
  • Rows Removed by Filter: 0
21. 689.492 689.492 ↑ 10.0 1 172,373

Index Scan using "journey_leg$trip_id$idx" on journey_leg jl (cost=0.56..135.78 rows=10 width=32) (actual time=0.002..0.004 rows=1 loops=172,373)

  • Index Cond: (trip_id = tr.id)
  • Filter: (bus_company_id = 90)
  • Rows Removed by Filter: 0
22. 525.115 525.115 ↑ 1.0 1 105,023

Index Scan using "transaction$pk" on transaction t (cost=0.56..7.67 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=105,023)

  • Index Cond: (id = jl.transaction_id)
  • Filter: ((sync_key IS NULL) AND ((type_xc)::text = 'TRANSACTION_TYPE.BUS_TICKET'::text))
  • Rows Removed by Filter: 0
23. 629.292 629.292 ↑ 1.0 1 104,882

Index Scan using "product_customer_details$uk" on product_customer_details pcd (cost=0.56..7.66 rows=1 width=95) (actual time=0.006..0.006 rows=1 loops=104,882)

  • Index Cond: (product_id = t.product_id)
24. 98.757 98.757 ↑ 1.0 1 98,757

Index Scan using "bus_line$pk" on bus_line bl (cost=0.28..7.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=98,757)

  • Index Cond: (id = tr.bus_line_id)
25. 197.514 197.514 ↑ 1.0 1 98,757

Index Scan using "trip_route$pk" on trip_route tre_dep (cost=0.57..7.70 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=98,757)

  • Index Cond: (id = jl.departure_route_stop_id)
26. 888.813 888.813 ↑ 2.0 1 98,757

Index Scan using "transaction_status$uk" on transaction_status ts (cost=0.57..15.34 rows=2 width=53) (actual time=0.009..0.009 rows=1 loops=98,757)

  • Index Cond: ((transaction_id = t.id) AND ((status_xc)::text = ANY ('{TRANSACTION_STATUS.SOLD,TRANSACTION_STATUS.RETURNED}'::text[])))
27. 98.380 98.380 ↑ 1.0 1 98,380

Index Scan using "bus_stop$pk" on bus_stop bs_dep (cost=0.28..7.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=98,380)

  • Index Cond: (id = jl.departure_bus_stop_id)
28. 98.380 98.380 ↑ 1.0 1 98,380

Index Scan using "bus_stop$pk" on bus_stop bs_dest (cost=0.28..7.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=98,380)

  • Index Cond: (id = jl.destination_bus_stop_id)
29. 52.386 52.386 ↑ 1.0 1 26,193

Index Scan using "trip_route$pk" on trip_route tre_dest (cost=0.57..7.70 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=26,193)

  • Index Cond: (id = jl.destination_route_stop_id)
30. 26.193 26.193 ↑ 1.0 1 26,193

Index Scan using "country$pk" on country cy_dep (cost=0.14..4.83 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=26,193)

  • Index Cond: (id = bs_dep.country_id)
31. 26.193 26.193 ↑ 1.0 1 26,193

Index Scan using "country$pk" on country cy_dest (cost=0.14..4.83 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=26,193)

  • Index Cond: (id = bs_dest.country_id)
32. 52.386 52.386 ↑ 1.0 1 26,193

Index Scan using "sales_point$pk" on sales_point sp (cost=0.28..7.42 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=26,193)

  • Index Cond: (id = ts.sales_point_id)
33. 52.386 52.386 ↑ 1.0 7 26,193

Seq Scan on igis_document_code i_dc (cost=0.00..1.07 rows=7 width=32) (actual time=0.001..0.002 rows=7 loops=26,193)

34. 26.193 26.193 ↑ 1.0 1 26,193

Index Scan using "country$pk" on country c (cost=0.14..4.83 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=26,193)

  • Index Cond: (id = pcd.citizenship_id)
35. 52.386 52.386 ↑ 1.0 1 26,193

Index Scan using "user_account$pk" on user_account ua (cost=0.28..7.29 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=26,193)

  • Index Cond: (id = ts.created_by_user_id)
36. 78.579 78.579 ↑ 1.0 1 26,193

Index Scan using "igis_transaction_status_info$uk" on igis_transaction_status_info i_ts (cost=0.43..7.51 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=26,193)

  • Index Cond: (transaction_status_id = ts.id)
37.          

SubPlan (forNested Loop Left Join)

38. 0.198 0.396 ↑ 1.0 1 18

Aggregate (cost=8.59..8.60 rows=1 width=32) (actual time=0.021..0.022 rows=1 loops=18)

39. 0.198 0.198 ↑ 1.0 1 18

Index Scan using "journey_leg_seat_info$journey_leg_id$idx" on journey_leg_seat_info jlsi (cost=0.56..8.58 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=18)

  • Index Cond: (journey_leg_id = jl.id)
40. 0.072 0.396 ↓ 0.0 0 18

Subquery Scan on oldinfo (cost=8.32..8.35 rows=1 width=465) (actual time=0.022..0.022 rows=0 loops=18)

  • Filter: (oldinfo.ordernumber = 1)
41. 0.108 0.324 ↓ 0.0 0 18

WindowAgg (cost=8.32..8.34 rows=1 width=481) (actual time=0.018..0.018 rows=0 loops=18)

42. 0.162 0.216 ↓ 0.0 0 18

Sort (cost=8.32..8.32 rows=1 width=473) (actual time=0.012..0.012 rows=0 loops=18)

  • Sort Key: pcdh.id
  • Sort Method: quicksort Memory: 25kB
43. 0.054 0.054 ↓ 0.0 0 18

Index Scan using "product_customer_details_history$product_customer_detail_id$idx" on product_customer_details_history pcdh (cost=0.29..8.31 rows=1 width=473) (actual time=0.003..0.003 rows=0 loops=18)

  • Index Cond: (product_customer_details_id = pcd.id)
  • Filter: (igis_export_on IS NULL)
44. 0.054 0.180 ↓ 0.0 0 18

Subquery Scan on newinfo (cost=8.32..8.35 rows=1 width=470) (actual time=0.010..0.010 rows=0 loops=18)

  • Filter: (newinfo.ordernumber = 1)
45. 0.036 0.126 ↓ 0.0 0 18

WindowAgg (cost=8.32..8.34 rows=1 width=486) (actual time=0.007..0.007 rows=0 loops=18)

46. 0.072 0.090 ↓ 0.0 0 18

Sort (cost=8.32..8.32 rows=1 width=478) (actual time=0.005..0.005 rows=0 loops=18)

  • Sort Key: pcdh_1.id DESC
  • Sort Method: quicksort Memory: 25kB
47. 0.018 0.018 ↓ 0.0 0 18

Index Scan using "product_customer_details_history$product_customer_detail_id$idx" on product_customer_details_history pcdh_1 (cost=0.29..8.31 rows=1 width=478) (actual time=0.001..0.001 rows=0 loops=18)

  • Index Cond: (product_customer_details_id = pcd.id)
  • Filter: (igis_export_on IS NULL)
48. 0.072 0.162 ↑ 1.0 1 18

Aggregate (cost=8.31..8.33 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=18)

49. 0.090 0.090 ↓ 0.0 0 18

Index Scan using "product_customer_details_history$product_customer_detail_id$idx" on product_customer_details_history pcdh_2 (cost=0.29..8.31 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=18)

  • Index Cond: (product_customer_details_id = pcd.id)
  • Filter: (igis_export_on IS NULL)
50. 0.000 52.354 ↓ 0.0 0 26,177

GroupAggregate (cost=0.29..8.32 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=26,177)

  • Group Key: pcdh_3.product_customer_details_id
51. 52.354 52.354 ↓ 0.0 0 26,177

Index Scan using "product_customer_details_history$product_customer_detail_id$idx" on product_customer_details_history pcdh_3 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=26,177)

  • Index Cond: (product_customer_details_id = pcd.id)
  • Filter: (igis_export_on IS NULL)
  • Rows Removed by Filter: 0