explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iOsa

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 24,313.775 ↑ 1.0 150 1

Limit (cost=92,793.58..92,796.58 rows=150 width=331) (actual time=24,310.297..24,313.775 rows=150 loops=1)

2.          

CTE search

3. 3,617.855 18,761.568 ↓ 9.0 145,139 1

Unique (cost=92,346.53..92,426.90 rows=16,074 width=511) (actual time=13,077.229..18,761.568 rows=145,139 loops=1)

4.          

Initplan (for Unique)

5. 0.059 7.742 ↑ 3.0 1 1

GroupAggregate (cost=4.30..10.50 rows=3 width=36) (actual time=7.729..7.742 rows=1 loops=1)

  • Group Key: account_group_user.user_id
6. 7.649 7.683 ↑ 1.0 3 1

Bitmap Heap Scan on account_group_user (cost=4.30..10.45 rows=3 width=8) (actual time=7.657..7.683 rows=3 loops=1)

  • Recheck Cond: (user_id = 23)
  • Heap Blocks: exact=1
7. 0.034 0.034 ↑ 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.028..0.034 rows=3 loops=1)

  • Index Cond: (user_id = 23)
8. 4,231.226 15,135.971 ↓ 9.0 145,139 1

Sort (cost=92,336.04..92,376.22 rows=16,074 width=511) (actual time=13,077.212..15,135.971 rows=145,139 loops=1)

  • Sort Key: qm.object_id DESC, qm.uploaded DESC
  • Sort Method: external merge Disk: 66,792kB
9. 0.000 10,904.745 ↓ 9.0 145,139 1

Gather (cost=1,051.66..87,531.07 rows=16,074 width=511) (actual time=57.957..10,904.745 rows=145,139 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $0
  • Workers Launched: 2
10. 1,274.105 11,078.035 ↓ 7.2 48,380 3 / 3

Hash Left Join (cost=51.66..84,923.67 rows=6,698 width=511) (actual time=40.921..11,078.035 rows=48,380 loops=3)

  • Hash Cond: (qm.object_id = qouf.object_id)
11. 1,825.332 9,803.856 ↓ 7.2 48,380 3 / 3

Nested Loop Left Join (cost=19.74..84,840.68 rows=6,698 width=498) (actual time=40.702..9,803.856 rows=48,380 loops=3)

12. 1,272.577 6,865.792 ↓ 7.2 48,380 3 / 3

Hash Join (cost=19.32..80,064.96 rows=6,698 width=206) (actual time=40.564..6,865.792 rows=48,380 loops=3)

  • Hash Cond: (qm.extension_id = qbank_extension.id)
13. 2,598.231 5,569.339 ↓ 7.2 48,380 3 / 3

Nested Loop (cost=0.42..80,028.38 rows=6,698 width=205) (actual time=16.596..5,569.339 rows=48,380 loops=3)

14. 881.348 881.348 ↓ 3.6 52,244 3 / 3

Parallel Seq Scan on qbank_object qo (cost=0.00..18,234.88 rows=14,700 width=80) (actual time=2.502..881.348 rows=52,244 loops=3)

  • Filter: ((NOT deleted) AND (23 <> ALL (user_no_read)) AND ((23 = ANY (user_read)) OR ($0 && group_read)))
  • Rows Removed by Filter: 53,951
15. 2,089.760 2,089.760 ↑ 1.0 1 156,732 / 3

Index Scan using fki_qbank_media_objectid on qbank_media qm (cost=0.42..4.19 rows=1 width=129) (actual time=0.028..0.040 rows=1 loops=156,732)

  • Index Cond: (object_id = qo.id)
  • Filter: ((replacedby IS NULL) AND ((parent_id IS NULL) OR (parent_id = id)) AND (mediastatus_id = 4))
  • Rows Removed by Filter: 0
16. 13.197 23.876 ↑ 1.0 662 3 / 3

Hash (cost=10.62..10.62 rows=662 width=9) (actual time=23.869..23.876 rows=662 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
17. 10.679 10.679 ↑ 1.0 662 3 / 3

Seq Scan on qbank_extension (cost=0.00..10.62 rows=662 width=9) (actual time=0.031..10.679 rows=662 loops=3)

18. 1,112.732 1,112.732 ↑ 1.0 1 145,139 / 3

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.023..0.023 rows=1 loops=145,139)

  • Index Cond: (qm.id = media_id)
19. 0.021 0.074 ↓ 0.0 0 3 / 3

Hash (cost=31.78..31.78 rows=11 width=8) (actual time=0.067..0.074 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
20. 0.023 0.053 ↓ 0.0 0 3 / 3

Bitmap Heap Scan on qbank_object_user_favourite qouf (cost=21.11..31.78 rows=11 width=8) (actual time=0.046..0.053 rows=0 loops=3)

  • Recheck Cond: (user_id = 23)
21. 0.030 0.030 ↓ 0.0 0 3 / 3

Bitmap Index Scan on pk_qbank_object_user_favourite_object_id_user_id (cost=0.00..21.11 rows=11 width=0) (actual time=0.023..0.030 rows=0 loops=3)

  • Index Cond: (user_id = 23)
22.          

Initplan (for Limit)

23. 1,520.955 11,228.273 ↑ 1.0 1 1

Aggregate (cost=361.67..361.68 rows=1 width=8) (actual time=11,228.262..11,228.273 rows=1 loops=1)

24. 9,707.318 9,707.318 ↓ 9.0 145,139 1

CTE Scan on search search_1 (cost=0.00..321.48 rows=16,074 width=0) (actual time=0.007..9,707.318 rows=145,139 loops=1)

25. 24,309.673 24,309.673 ↑ 40.2 400 1

CTE Scan on search (cost=0.00..321.48 rows=16,074 width=331) (actual time=24,305.536..24,309.673 rows=400 loops=1)

Planning time : 16.866 ms
Execution time : 24,368.258 ms