explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OQE3

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.039 ↓ 0.0 0 1

Limit (cost=48,151.31..48,151.32 rows=1 width=48) (actual time=0.039..0.039 rows=0 loops=1)

2.          

CTE ets_provider

3. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on ets_providers (cost=0.00..1.05 rows=1 width=276) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: (code = '598368'::text)
  • Rows Removed by Filter: 4
4.          

CTE packages

5. 0.005 0.005 ↓ 0.0 0 1

Sort (cost=20,671.32..20,888.69 rows=86,945 width=104) (actual time=0.005..0.005 rows=0 loops=1)

  • Sort Key: ipl.number_version_fl, ipl.number_version_dl
  • Sort Method: quicksort Memory: 25kB
6. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=1,164.75..13,538.44 rows=86,945 width=104) (actual time=0.000..0.000 rows=0 loops=1)

7. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on ets_provider ep_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)

8. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ismv_packageable_lists ipl (cost=1,164.75..12,668.97 rows=86,945 width=104) (never executed)

  • Recheck Cond: (ets_provider_id = ep_1.id)
  • Filter: (type = ANY ('{Ismv::ObuList::Full::Out,Ismv::ObuList::Delta::Out,Ismv::ObuList::Delta::In,Ismv::ObuList::Full::In,Ismv::ObuList::Update:
9. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on relationship_381_fk (cost=0.00..1,143.01 rows=86,945 width=0) (never executed)

  • Index Cond: (ets_provider_id = ep_1.id)
10.          

CTE last_processed_version

11. 0.000 0.000 ↓ 0.0 0

Limit (cost=19,434.76..19,434.76 rows=1 width=112) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Sort (cost=19,434.76..19,552.83 rows=47,227 width=112) (never executed)

  • Sort Key: l2sr.created_at DESC
13. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=14,081.84..19,198.62 rows=47,227 width=112) (never executed)

  • Hash Cond: (l2sr.ipl_id = ipl_1.id)
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on l2_send_requests l2sr (cost=0.00..4,061.07 rows=188,907 width=12) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Hash (cost=12,995.03..12,995.03 rows=86,945 width=104) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,164.75..12,995.03 rows=86,945 width=104) (never executed)

17. 0.000 0.000 ↓ 0.0 0

CTE Scan on ets_provider ep_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ismv_packageable_lists ipl_1 (cost=1,164.75..12,125.56 rows=86,945 width=104) (never executed)

  • Recheck Cond: (ets_provider_id = ep_2.id)
19. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on relationship_381_fk (cost=0.00..1,143.01 rows=86,945 width=0) (never executed)

  • Index Cond: (ets_provider_id = ep_2.id)
20.          

CTE fl_after_last_version

21. 0.001 0.020 ↓ 0.0 0 1

Limit (cost=2,391.64..2,391.64 rows=1 width=111) (actual time=0.019..0.020 rows=0 loops=1)

22.          

Initplan (for Limit)

23. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_processed_version lpv (cost=0.00..0.02 rows=1 width=4) (never executed)

24. 0.004 0.019 ↓ 0.0 0 1

Sort (cost=2,391.62..2,391.62 rows=1 width=111) (actual time=0.019..0.019 rows=0 loops=1)

  • Sort Key: p.number_version_fl DESC
  • Sort Method: quicksort Memory: 25kB
25. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.00..2,391.61 rows=1 width=111) (actual time=0.015..0.015 rows=0 loops=1)

  • Join Filter: (p.ets_provider_id = e.id)
26. 0.015 0.015 ↓ 0.0 0 1

CTE Scan on ets_provider e (cost=0.00..0.02 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)

27. 0.000 0.000 ↓ 0.0 0

CTE Scan on packages p (cost=0.00..2,390.99 rows=48 width=111) (never executed)

  • Filter: ((number_version_fl > COALESCE($5, 0)) AND (plaza_state >= 1) AND (number_version_dl = 1))
28.          

CTE update_after_last_version

29. 0.001 0.005 ↓ 0.0 0 1

Limit (cost=2,826.40..2,826.41 rows=1 width=111) (actual time=0.005..0.005 rows=0 loops=1)

30.          

Initplan (for Limit)

31. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_processed_version lpv_1 (cost=0.00..0.02 rows=1 width=4) (never executed)

32. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_processed_version lpv_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_processed_version (cost=0.00..0.02 rows=1 width=4) (never executed)

34. 0.004 0.004 ↓ 0.0 0 1

Sort (cost=2,826.34..2,826.35 rows=1 width=111) (actual time=0.004..0.004 rows=0 loops=1)

  • Sort Key: p_1.number_version_fl DESC, p_1.number_version_dl DESC
  • Sort Method: quicksort Memory: 25kB
35. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0.00..2,826.33 rows=1 width=111) (actual time=0.000..0.000 rows=0 loops=1)

  • Join Filter: (p_1.ets_provider_id = e_1.id)
36. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on ets_provider e_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)

37. 0.000 0.000 ↓ 0.0 0

CTE Scan on packages p_1 (cost=0.00..2,825.71 rows=48 width=111) (never executed)

  • Filter: ((plaza_state >= 1) AND (type = 'Ismv::ObuList::Update::In'::text) AND ((number_version_fl > COALESCE($7, 0)) OR ((number_version_f
38.          

CTE last_versioned_package

39. 0.000 0.005 ↓ 0.0 0 1

Limit (cost=0.04..2,608.39 rows=1 width=111) (actual time=0.005..0.005 rows=0 loops=1)

40.          

Initplan (for Limit)

41. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_processed_version last_processed_version_1 (cost=0.00..0.02 rows=1 width=4) (never executed)

42. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_processed_version last_processed_version_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

43. 0.005 0.005 ↓ 0.0 0 1

CTE Scan on packages p_2 (cost=0.00..2,608.35 rows=1 width=111) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((plaza_state >= 1) AND (number_version_fl = $11) AND (number_version_dl = ($12 + 1)))
44.          

CTE next_available_versions

45. 0.001 0.032 ↓ 0.0 0 1

HashAggregate (cost=0.22..0.26 rows=3 width=111) (actual time=0.032..0.032 rows=0 loops=1)

  • Group Key: fl_after_last_version.id, fl_after_last_version.ets_provider_id, fl_after_last_version.request_ismv_exchange_log_id, fl_after_last_version.resent_
46. 0.001 0.031 ↓ 0.0 0 1

Append (cost=0.00..0.10 rows=3 width=111) (actual time=0.031..0.031 rows=0 loops=1)

47. 0.020 0.020 ↓ 0.0 0 1

CTE Scan on fl_after_last_version (cost=0.00..0.02 rows=1 width=111) (actual time=0.020..0.020 rows=0 loops=1)

48. 0.005 0.005 ↓ 0.0 0 1

CTE Scan on update_after_last_version ualv (cost=0.00..0.02 rows=1 width=111) (actual time=0.005..0.005 rows=0 loops=1)

49. 0.005 0.005 ↓ 0.0 0 1

CTE Scan on last_versioned_package lvp (cost=0.00..0.02 rows=1 width=111) (actual time=0.005..0.005 rows=0 loops=1)

50. 0.005 0.038 ↓ 0.0 0 1

Sort (cost=0.12..0.13 rows=1 width=48) (actual time=0.038..0.038 rows=0 loops=1)

  • Sort Key: nav.number_version_fl DESC, nav.number_version_dl DESC
  • Sort Method: quicksort Memory: 25kB
51. 0.000 0.033 ↓ 0.0 0 1

Hash Join (cost=0.03..0.11 rows=1 width=48) (actual time=0.033..0.033 rows=0 loops=1)

  • Hash Cond: (nav.ets_provider_id = ep.id)
52. 0.033 0.033 ↓ 0.0 0 1

CTE Scan on next_available_versions nav (cost=0.00..0.06 rows=3 width=20) (actual time=0.033..0.033 rows=0 loops=1)

53. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=36) (never executed)

54. 0.000 0.000 ↓ 0.0 0

CTE Scan on ets_provider ep (cost=0.00..0.02 rows=1 width=36) (never executed)

Planning time : 0.791 ms
Execution time : 0.263 ms