explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zjBu : Optimization for: plan #0uzs

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.285 ↓ 0.0 0 1

Nested Loop (cost=170.54..170.65 rows=1 width=382) (actual time=0.285..0.285 rows=0 loops=1)

2.          

CTE store

3. 0.001 0.003 ↑ 1.0 1 1

Limit (cost=2.85..2.87 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1)

4. 0.002 0.002 ↑ 925.0 2 1

Seq Scan on store (cost=0.00..28.50 rows=1,850 width=16) (actual time=0.002..0.002 rows=2 loops=1)

5.          

CTE staff_member

6. 0.011 0.040 ↑ 1.0 1 1

Limit (cost=14.80..14.82 rows=1 width=209) (actual time=0.040..0.040 rows=1 loops=1)

7.          

CTE store_staff

8. 0.006 0.023 ↑ 2.0 1 1

Hash Join (cost=0.03..14.73 rows=2 width=209) (actual time=0.022..0.023 rows=1 loops=1)

  • Hash Cond: (staff.store_id = store_1.store_id)
9. 0.011 0.011 ↑ 170.0 2 1

Seq Scan on staff (cost=0.00..13.40 rows=340 width=209) (actual time=0.010..0.011 rows=2 loops=1)

10. 0.001 0.006 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

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

CTE Scan on store store_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

12.          

Initplan (for Limit)

13. 0.005 0.029 ↑ 1.0 1 1

Aggregate (cost=0.04..0.06 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=1)

14. 0.024 0.024 ↑ 2.0 1 1

CTE Scan on store_staff (cost=0.00..0.04 rows=2 width=0) (actual time=0.023..0.024 rows=1 loops=1)

15. 0.000 0.000 ↑ 2.0 1 1

CTE Scan on store_staff store_staff_1 (cost=0.00..0.04 rows=2 width=209) (actual time=0.000..0.000 rows=1 loops=1)

16.          

CTE customer

17. 0.003 0.234 ↑ 1.0 1 1

Limit (cost=27.63..27.65 rows=1 width=121) (actual time=0.234..0.234 rows=1 loops=1)

18.          

CTE store_customer

19. 0.102 0.139 ↑ 1.1 273 1

Hash Join (cost=0.03..20.27 rows=300 width=70) (actual time=0.022..0.139 rows=273 loops=1)

  • Hash Cond: (customer_1.store_id = staff_member_1.store_id)
20. 0.034 0.034 ↑ 1.0 599 1

Seq Scan on customer customer_1 (cost=0.00..14.99 rows=599 width=70) (actual time=0.002..0.034 rows=599 loops=1)

21. 0.002 0.003 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=2) (actual time=0.003..0.003 rows=1 loops=1)

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

CTE Scan on staff_member staff_member_1 (cost=0.00..0.02 rows=1 width=2) (actual time=0.001..0.001 rows=1 loops=1)

23.          

Initplan (for Limit)

24. 0.010 0.226 ↑ 1.0 1 1

Aggregate (cost=6.75..6.76 rows=1 width=8) (actual time=0.226..0.226 rows=1 loops=1)

25. 0.216 0.216 ↑ 1.1 273 1

CTE Scan on store_customer (cost=0.00..6.00 rows=300 width=0) (actual time=0.023..0.216 rows=273 loops=1)

26. 0.005 0.005 ↑ 3.2 93 1

CTE Scan on store_customer store_customer_1 (cost=0.00..6.00 rows=300 width=121) (actual time=0.000..0.005 rows=93 loops=1)

27.          

CTE item

28. 0.001 0.004 ↓ 0.0 0 1

Limit (cost=125.09..125.11 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

29.          

CTE store_inventory

30. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.00..123.21 rows=76 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: (inventory.store_id = store_2.store_id)
31. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on store store_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

32. 0.002 0.002 ↓ 0.0 0 1

Sample Scan on inventory (cost=0.00..121.28 rows=153 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Sampling: system ('10'::real)
  • Filter: inventory_in_stock(inventory_id)
33.          

Initplan (for Limit)

34. 0.000 0.003 ↑ 1.0 1 1

Aggregate (cost=1.71..1.72 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

35. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on store_inventory (cost=0.00..1.52 rows=76 width=0) (actual time=0.003..0.003 rows=0 loops=1)

36. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on store_inventory store_inventory_1 (cost=0.00..1.52 rows=76 width=16) (actual time=0.000..0.000 rows=0 loops=1)

37.          

CTE rental

38. 0.001 0.002 ↓ 0.0 0 1

Insert on rental rental_1 (cost=0.00..0.10 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=1)

39. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.10 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1)

40. 0.001 0.001 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.05 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

41. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on staff_member staff_member_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

42. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on customer customer_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

43. 0.000 0.000 ↓ 0.0 0 1

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

44. 0.001 0.285 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.08 rows=1 width=346) (actual time=0.285..0.285 rows=0 loops=1)

45. 0.001 0.280 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.05 rows=1 width=330) (actual time=0.280..0.280 rows=1 loops=1)

46. 0.044 0.044 ↑ 1.0 1 1

CTE Scan on staff_member (cost=0.00..0.02 rows=1 width=209) (actual time=0.044..0.044 rows=1 loops=1)

47. 0.235 0.235 ↑ 1.0 1 1

CTE Scan on customer (cost=0.00..0.02 rows=1 width=121) (actual time=0.235..0.235 rows=1 loops=1)

48. 0.004 0.004 ↓ 0.0 0 1

CTE Scan on item (cost=0.00..0.02 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

49. 0.000 0.000 ↓ 0.0 0

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

Planning time : 0.639 ms
Execution time : 0.422 ms