explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0uzs

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 1.312 ↑ 1.0 1 1

Nested Loop (cost=111.79..111.90 rows=1 width=382) (actual time=1.310..1.312 rows=1 loops=1)

2.          

CTE store

3. 0.002 0.458 ↑ 1.0 1 1

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

4. 0.456 0.456 ↑ 1,850.0 1 1

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

5.          

CTE staff_member

6. 0.004 0.514 ↑ 1.0 1 1

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

7.          

CTE store_staff

8. 0.019 0.506 ↑ 2.0 1 1

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

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

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

10. 0.003 0.463 ↑ 1.0 1 1

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

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

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

12.          

Initplan (for Limit)

13. 0.003 0.510 ↑ 1.0 1 1

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

14. 0.507 0.507 ↑ 2.0 1 1

CTE Scan on store_staff (cost=0.00..0.04 rows=2 width=0) (actual time=0.506..0.507 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.004 0.389 ↑ 1.0 1 1

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

18.          

CTE store_customer

19. 0.184 0.240 ↓ 1.1 326 1

Hash Join (cost=0.03..20.27 rows=300 width=70) (actual time=0.011..0.240 rows=326 loops=1)

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

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

21. 0.000 0.001 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=2) (actual time=0.001..0.001 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.015 0.368 ↑ 1.0 1 1

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

25. 0.353 0.353 ↓ 1.1 326 1

CTE Scan on store_customer (cost=0.00..6.00 rows=300 width=0) (actual time=0.012..0.353 rows=326 loops=1)

26. 0.017 0.017 ↑ 4.9 61 1

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

27.          

CTE item

28. 0.001 0.318 ↑ 1.0 1 1

Limit (cost=65.50..66.36 rows=1 width=16) (actual time=0.318..0.318 rows=1 loops=1)

29. 0.002 0.317 ↑ 382.0 2 1

Nested Loop (cost=0.28..655.93 rows=764 width=16) (actual time=0.231..0.317 rows=2 loops=1)

30. 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)

31. 0.315 0.315 ↑ 381.5 2 1

Index Scan using idx_store_id_film_id on inventory (cost=0.28..648.28 rows=763 width=16) (actual time=0.228..0.315 rows=2 loops=1)

  • Index Cond: (store_id = store_2.store_id)
  • Filter: inventory_in_stock(inventory_id)
32.          

CTE rental

33. 0.030 0.083 ↑ 1.0 1 1

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

34. 0.051 0.053 ↑ 1.0 1 1

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

35. 0.001 0.002 ↑ 1.0 1 1

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

36. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

37. 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)

38. 0.000 0.000 ↑ 1.0 1 1

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

39. 0.002 1.227 ↑ 1.0 1 1

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

40. 0.002 0.906 ↑ 1.0 1 1

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

41. 0.515 0.515 ↑ 1.0 1 1

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

42. 0.389 0.389 ↑ 1.0 1 1

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

43. 0.319 0.319 ↑ 1.0 1 1

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

44. 0.084 0.084 ↑ 1.0 1 1

CTE Scan on rental (cost=0.00..0.02 rows=1 width=36) (actual time=0.082..0.084 rows=1 loops=1)

Planning time : 1.988 ms
Execution time : 1.818 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint rental_customer_id_fkey 0.158 ms 1 0.158 ms
for constraint rental_inventory_id_fkey 0.122 ms 1 0.122 ms
for constraint rental_staff_id_fkey 0.046 ms 1 0.046 ms