explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FXho

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 137,179.991 ↑ 1.0 150 1

Limit (cost=6,231,944.12..6,231,947.12 rows=150 width=331) (actual time=137,175.445..137,179.991 rows=150 loops=1)

2.          

CTE search

3. 2,230.002 133,642.810 ↓ 4.1 145,139 1

Unique (cost=6,230,963.63..6,231,140.99 rows=35,472 width=511) (actual time=130,225.745..133,642.810 rows=145,139 loops=1)

4. 2,665.115 131,412.808 ↓ 4.1 145,139 1

Sort (cost=6,230,963.63..6,231,052.31 rows=35,472 width=511) (actual time=130,225.731..131,412.808 rows=145,139 loops=1)

  • Sort Key: qm.object_id DESC, qm.uploaded DESC
  • Sort Method: external merge Disk: 66,784kB
5. 3,419.628 128,747.693 ↓ 4.1 145,139 1

Nested Loop Left Join (cost=1.28..6,220,159.44 rows=35,472 width=511) (actual time=6.017..128,747.693 rows=145,139 loops=1)

6. 3,239.498 124,021.814 ↓ 4.1 145,139 1

Nested Loop Left Join (cost=1.12..6,213,763.69 rows=35,472 width=498) (actual time=5.941..124,021.814 rows=145,139 loops=1)

7. 3,207.808 118,895.509 ↓ 4.1 145,139 1

Nested Loop (cost=0.70..6,188,441.80 rows=35,472 width=206) (actual time=5.869..118,895.509 rows=145,139 loops=1)

8. 3,333.774 114,091.172 ↓ 4.1 145,139 1

Nested Loop (cost=0.42..6,178,059.35 rows=35,472 width=205) (actual time=5.694..114,091.172 rows=145,139 loops=1)

9. 1,731.398 1,731.398 ↓ 1.0 145,368 1

Seq Scan on qbank_media qm (cost=0.00..98,417.46 rows=144,704 width=129) (actual time=4.054..1,731.398 rows=145,368 loops=1)

  • Filter: ((replacedby IS NULL) AND ((parent_id IS NULL) OR (parent_id = id)) AND (mediastatus_id = 4))
  • Rows Removed by Filter: 161,297
10. 3,074.530 109,026.000 ↑ 1.0 1 145,368

Index Scan using pk_qbank_object_id on qbank_object qo (cost=0.42..42.01 rows=1 width=80) (actual time=0.750..0.750 rows=1 loops=145,368)

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

SubPlan (for Index Scan)

12. 3,193.058 105,951.470 ↑ 2.0 1 145,139

HashSetOp Except (cost=37.17..45.68 rows=2 width=8) (actual time=0.730..0.730 rows=1 loops=145,139)

13. 5,079.865 102,758.412 ↑ 3.0 1 145,139

Append (cost=37.17..45.68 rows=3 width=8) (actual time=0.625..0.708 rows=1 loops=145,139)

14. 4,063.892 93,904.933 ↑ 2.0 1 145,139

Result (cost=37.17..37.20 rows=2 width=8) (actual time=0.611..0.647 rows=1 loops=145,139)

15. 4,354.170 89,841.041 ↑ 2.0 1 145,139

Unique (cost=37.17..37.18 rows=2 width=4) (actual time=0.597..0.619 rows=1 loops=145,139)

16. 4,644.448 85,486.871 ↑ 2.0 1 145,139

Sort (cost=37.17..37.17 rows=2 width=4) (actual time=0.581..0.589 rows=1 loops=145,139)

  • Sort Key: (1)
  • Sort Method: quicksort Memory: 25kB
17. 5,370.143 80,842.423 ↑ 2.0 1 145,139

Append (cost=4.73..37.16 rows=2 width=4) (actual time=0.500..0.557 rows=1 loops=145,139)

18. 4,209.031 73,585.473 ↑ 1.0 1 145,139

Subquery Scan on *SELECT* 1 (cost=4.73..28.68 rows=1 width=4) (actual time=0.486..0.507 rows=1 loops=145,139)

19. 7,111.811 69,376.442 ↑ 1.0 1 145,139

GroupAggregate (cost=4.73..28.67 rows=1 width=8) (actual time=0.471..0.478 rows=1 loops=145,139)

  • Group Key: qbank_acl_group.object_id
  • Filter: ((bit_or(qbank_acl_group.permissions) & '00000010'::"bit") = '00000010'::"bit")
20. 11,465.981 62,264.631 ↓ 1.5 3 145,139

Nested Loop (cost=4.73..28.63 rows=2 width=10) (actual time=0.072..0.429 rows=3 loops=145,139)

  • Join Filter: (account_group.id = qbank_acl_group.group_id)
21. 24,383.352 45,573.646 ↓ 1.5 3 145,139

Nested Loop (cost=4.30..11.71 rows=2 width=8) (actual time=0.037..0.314 rows=3 loops=145,139)

  • Join Filter: (account_group_user.group_id = account_group.id)
  • Rows Removed by Join Filter: 9
22. 6,676.394 6,676.394 ↓ 1.2 5 145,139

Seq Scan on account_group (cost=0.00..1.07 rows=4 width=4) (actual time=0.009..0.046 rows=5 loops=145,139)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 2
23. 14,513.816 14,513.900 ↑ 1.5 2 725,695

Materialize (cost=4.30..10.49 rows=3 width=4) (actual time=0.007..0.020 rows=2 loops=725,695)

24. 0.049 0.084 ↑ 1.0 3 1

Bitmap Heap Scan on account_group_user (cost=4.30..10.48 rows=3 width=4) (actual time=0.053..0.084 rows=3 loops=1)

  • Recheck Cond: (user_id = 23)
  • Filter: (((startdate IS NULL) OR (startdate < now())) AND ((enddate IS NULL) OR (enddate > now())))
  • Heap Blocks: exact=1
25. 0.035 0.035 ↑ 1.0 3 1

Bitmap Index Scan on pk_account_user_group_id (cost=0.00..4.30 rows=3 width=0) (actual time=0.029..0.035 rows=3 loops=1)

  • Index Cond: (user_id = 23)
26. 5,225.004 5,225.004 ↑ 1.0 1 435,417

Index Scan using pk_acl_group_id on qbank_acl_group (cost=0.43..8.45 rows=1 width=14) (actual time=0.012..0.012 rows=1 loops=435,417)

  • Index Cond: ((qo.id = object_id) AND (group_id = account_group_user.group_id))
27. 1,886.807 1,886.807 ↓ 0.0 0 145,139

Index Scan using pk_acl_user_id on qbank_acl_user (cost=0.43..8.46 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=145,139)

  • Index Cond: ((qo.id = object_id) AND (user_id = 23))
  • Filter: ((permissions & '00000010'::"bit") = '00000010'::"bit")
28. 2,177.085 3,773.614 ↓ 0.0 0 145,139

Subquery Scan on *SELECT* 3 (cost=0.43..8.46 rows=1 width=8) (actual time=0.026..0.026 rows=0 loops=145,139)

29. 1,596.529 1,596.529 ↓ 0.0 0 145,139

Index Scan using pk_acl_user_id on qbank_acl_user qbank_acl_user_1 (cost=0.43..8.46 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=145,139)

  • Index Cond: ((qo.id = object_id) AND (user_id = 23))
  • Filter: ((permissions & '00000010'::"bit") = '00000000'::"bit")
  • Rows Removed by Filter: 0
30. 1,596.529 1,596.529 ↑ 1.0 1 145,139

Index Scan using pk_qbank_extension_id on qbank_extension (cost=0.28..0.29 rows=1 width=9) (actual time=0.011..0.011 rows=1 loops=145,139)

  • Index Cond: (id = qm.extension_id)
31. 1,886.807 1,886.807 ↑ 1.0 1 145,139

Index Scan using uq_qbank_media_summary_mediaid on qbank_media_summary qms (cost=0.42..0.71 rows=1 width=296) (actual time=0.013..0.013 rows=1 loops=145,139)

  • Index Cond: (qm.id = media_id)
32. 1,306.251 1,306.251 ↓ 0.0 0 145,139

Index Only Scan using pk_qbank_object_user_favourite_object_id_user_id on qbank_object_user_favourite qouf (cost=0.15..0.18 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=145,139)

  • Index Cond: ((object_id = qm.object_id) AND (user_id = 23))
  • Heap Fetches: 0
33.          

Initplan (for Limit)

34. 1,094.932 6,945.157 ↑ 1.0 1 1

Aggregate (cost=798.12..798.13 rows=1 width=8) (actual time=6,945.144..6,945.157 rows=1 loops=1)

35. 5,850.225 5,850.225 ↓ 4.1 145,139 1

CTE Scan on search search_1 (cost=0.00..709.44 rows=35,472 width=0) (actual time=0.006..5,850.225 rows=145,139 loops=1)

36. 137,175.754 137,175.754 ↑ 88.7 400 1

CTE Scan on search (cost=0.00..709.44 rows=35,472 width=331) (actual time=137,170.931..137,175.754 rows=400 loops=1)

Planning time : 10.105 ms
Execution time : 137,354.931 ms