explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fOL1

Settings
# exclusive inclusive rows x rows loops node
1. 0.811 131.217 ↓ 1.6 3,085 1

Unique (cost=38,355.15..39,621.19 rows=1,962 width=9) (actual time=118.116..131.217 rows=3,085 loops=1)

2. 0.000 130.406 ↓ 1.6 3,085 1

Nested Loop (cost=38,355.15..39,616.28 rows=1,962 width=9) (actual time=118.114..130.406 rows=3,085 loops=1)

3. 0.596 119.478 ↓ 1.5 3,821 1

Merge Left Join (cost=38,354.73..38,393.64 rows=2,562 width=16) (actual time=118.089..119.478 rows=3,821 loops=1)

  • Merge Cond: (qm.object_id = qouf.object_id)
4. 1.903 118.878 ↓ 1.5 3,821 1

Sort (cost=38,354.57..38,360.98 rows=2,562 width=12) (actual time=118.082..118.878 rows=3,821 loops=1)

  • Sort Key: qm.object_id
  • Sort Method: quicksort Memory: 276kB
5. 18.526 116.975 ↓ 1.5 3,821 1

Hash Semi Join (cost=7,245.07..38,209.53 rows=2,562 width=12) (actual time=28.725..116.975 rows=3,821 loops=1)

  • Hash Cond: (qm.object_id = qfoc.object_id)
6. 69.749 69.749 ↑ 1.0 174,878 1

Seq Scan on qbank_media qm (cost=0.00..30,319.97 rows=174,878 width=8) (actual time=0.007..69.749 rows=174,878 loops=1)

  • Filter: ((replacedby IS NULL) AND ((parent_id IS NULL) OR (parent_id = id)))
7. 0.825 28.700 ↓ 1.7 4,404 1

Hash (cost=7,213.05..7,213.05 rows=2,562 width=4) (actual time=28.700..28.700 rows=4,404 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 219kB
8. 1.374 27.875 ↓ 1.7 4,404 1

Nested Loop (cost=1.55..7,213.05 rows=2,562 width=4) (actual time=0.279..27.875 rows=4,404 loops=1)

  • Join Filter: (qfc.child_folder_id = qfoc.folder_id)
9. 0.181 24.467 ↓ 2.5 678 1

Nested Loop (cost=1.13..6,913.51 rows=269 width=8) (actual time=0.136..24.467 rows=678 loops=1)

10. 0.460 2.558 ↑ 1.0 679 1

Nested Loop (cost=0.71..911.69 rows=702 width=12) (actual time=0.023..2.558 rows=679 loops=1)

11. 0.740 0.740 ↑ 1.0 679 1

Index Scan using pk_qbank_folder_closure_parent_child on qbank_folder_closure qfc (cost=0.42..428.56 rows=702 width=4) (actual time=0.013..0.740 rows=679 loops=1)

  • Index Cond: (parent_folder_id = 22588)
  • Filter: (depth <= 10)
12. 1.358 1.358 ↑ 1.0 1 679

Index Scan using pk_qbank_folder_id on qbank_folder qf (cost=0.29..0.68 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=679)

  • Index Cond: (id = qfc.child_folder_id)
13. 4.100 21.728 ↑ 1.0 1 679

Index Scan using pk_qbank_object_id on qbank_object qfo (cost=0.42..8.54 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=679)

  • Index Cond: (id = qf.object_id)
  • Filter: ((NOT deleted) AND (SubPlan 1))
  • Rows Removed by Filter: 0
14.          

SubPlan (for Index Scan)

15. 0.678 17.628 ↑ 2.0 1 678

HashSetOp Except (cost=6.64..9.13 rows=2 width=8) (actual time=0.026..0.026 rows=1 loops=678)

16. 0.000 16.950 ↑ 3.0 1 678

Append (cost=6.64..9.13 rows=3 width=8) (actual time=0.022..0.025 rows=1 loops=678)

17. 0.678 14.916 ↑ 2.0 1 678

Result (cost=6.64..6.67 rows=2 width=8) (actual time=0.021..0.022 rows=1 loops=678)

18. 0.000 14.238 ↑ 2.0 1 678

Unique (cost=6.64..6.65 rows=2 width=4) (actual time=0.021..0.021 rows=1 loops=678)

19. 2.712 14.238 ↑ 1.0 2 678

Sort (cost=6.64..6.65 rows=2 width=4) (actual time=0.020..0.021 rows=2 loops=678)

  • Sort Key: qbank_acl_group.object_id
  • Sort Method: quicksort Memory: 25kB
20. 0.000 11.526 ↑ 1.0 2 678

Append (cost=0.42..6.63 rows=2 width=4) (actual time=0.013..0.017 rows=2 loops=678)

21. 2.034 8.814 ↑ 1.0 1 678

GroupAggregate (cost=0.42..4.17 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=678)

  • Group Key: qbank_acl_group.object_id
  • Filter: ((bit_or(qbank_acl_group.permissions) & B'00000010'::"bit") = B'00000010'::"bit")
22. 0.678 6.780 ↑ 1.0 1 678

Nested Loop (cost=0.42..4.15 rows=1 width=10) (actual time=0.009..0.010 rows=1 loops=678)

23. 3.390 3.390 ↑ 1.0 1 678

Seq Scan on account_group_user (cost=0.00..1.70 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=678)

  • Filter: ((user_id = 1) AND ((startdate IS NULL) OR (startdate < now())) AND ((enddate IS NULL) OR (enddate > now())))
  • Rows Removed by Filter: 30
24. 2.712 2.712 ↑ 1.0 1 678

Index Scan using pk_acl_group_id on qbank_acl_group (cost=0.42..2.44 rows=1 width=14) (actual time=0.003..0.004 rows=1 loops=678)

  • Index Cond: ((qfo.id = object_id) AND (group_id = account_group_user.group_id))
25. 2.712 2.712 ↑ 1.0 1 678

Index Scan using pk_acl_user_id on qbank_acl_user (cost=0.42..2.45 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=678)

  • Index Cond: ((qfo.id = object_id) AND (user_id = 1))
  • Filter: ((permissions & B'00000010'::"bit") = B'00000010'::"bit")
26. 0.678 2.034 ↓ 0.0 0 678

Subquery Scan on *SELECT* 3 (cost=0.42..2.46 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=678)

27. 1.356 1.356 ↓ 0.0 0 678

Index Scan using pk_acl_user_id on qbank_acl_user qbank_acl_user_1 (cost=0.42..2.45 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=678)

  • Index Cond: ((qfo.id = object_id) AND (user_id = 1))
  • Filter: (permissions = B'00000000'::"bit")
  • Rows Removed by Filter: 1
28. 2.034 2.034 ↑ 3.7 6 678

Index Only Scan using pk_qbank_folder_object on qbank_folder_object qfoc (cost=0.42..0.84 rows=22 width=8) (actual time=0.002..0.003 rows=6 loops=678)

  • Index Cond: (folder_id = qf.id)
  • Heap Fetches: 0
29. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using pk_qbank_object_user_favourite_object_id_user_id on qbank_object_user_favourite qouf (cost=0.15..26.21 rows=11 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
30. 11.463 11.463 ↑ 1.0 1 3,821

Index Scan using pk_qbank_object_id on qbank_object qo (cost=0.42..0.47 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=3,821)

  • Index Cond: (id = qm.object_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
Planning time : 1.744 ms
Execution time : 131.601 ms