explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XoUC

Settings
# exclusive inclusive rows x rows loops node
1. 74.158 1,504.004 ↓ 448.4 158,274 1

Sort (cost=143,286.02..143,286.90 rows=353 width=16) (actual time=1,489.109..1,504.004 rows=158,274 loops=1)

  • Sort Key: ah.creation DESC
  • Sort Method: external merge Disk: 4,016kB
2.          

CTE vcl_filtered

3. 243.474 292.579 ↓ 1.3 614,474 1

Bitmap Heap Scan on vcl vcl_1 (cost=16,966.37..118,984.92 rows=490,177 width=49) (actual time=56.116..292.579 rows=614,474 loops=1)

  • Recheck Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
  • Filter: ((office_id IS NULL) OR (office_id = 119))
  • Rows Removed by Filter: 155,097
  • Heap Blocks: exact=49,567
4. 49.105 49.105 ↓ 1.0 769,571 1

Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..16,843.83 rows=757,147 width=0) (actual time=49.105..49.105 rows=769,571 loops=1)

  • Index Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
5. 18.751 1,429.846 ↓ 448.4 158,274 1

Nested Loop (cost=15,227.04..24,286.17 rows=353 width=16) (actual time=653.399..1,429.846 rows=158,274 loops=1)

6. 75.119 1,094.547 ↓ 73.1 158,274 1

Hash Join (cost=15,226.62..22,846.93 rows=2,165 width=24) (actual time=653.385..1,094.547 rows=158,274 loops=1)

  • Hash Cond: (ah.workflow_id = version.workflow_id)
7. 14.280 366.699 ↓ 36.2 158,274 1

Subquery Scan on ah (cost=2.41..6,799.23 rows=4,369 width=24) (actual time=0.170..366.699 rows=158,274 loops=1)

8. 26.354 352.419 ↓ 36.2 158,274 1

Nested Loop (cost=2.41..6,755.54 rows=4,369 width=548) (actual time=0.169..352.419 rows=158,274 loops=1)

9.          

CTE action_ids

10. 0.012 0.012 ↑ 1.0 44 1

Values Scan on "*VALUES*" (cost=0.00..0.55 rows=44 width=4) (actual time=0.001..0.012 rows=44 loops=1)

11. 0.124 0.671 ↓ 1.1 19 1

Hash Join (cost=1.43..132.01 rows=17 width=12) (actual time=0.054..0.671 rows=19 loops=1)

  • Hash Cond: (a.id = ad.id)
12. 0.508 0.508 ↑ 1.0 1,829 1

Seq Scan on action a (cost=0.00..123.55 rows=1,829 width=8) (actual time=0.007..0.508 rows=1,829 loops=1)

  • Filter: (action_type = 1)
  • Rows Removed by Filter: 2,935
13. 0.011 0.039 ↑ 1.0 44 1

Hash (cost=0.88..0.88 rows=44 width=4) (actual time=0.039..0.039 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 0.028 0.028 ↑ 1.0 44 1

CTE Scan on action_ids ad (cost=0.00..0.88 rows=44 width=4) (actual time=0.004..0.028 rows=44 loops=1)

15. 325.394 325.394 ↓ 6.7 8,330 19

Index Scan using action_history_action_idx on action_history ah_1 (cost=0.43..377.21 rows=1,238 width=32) (actual time=0.015..17.126 rows=8,330 loops=19)

  • Index Cond: (action_id = a.id)
  • Filter: (project_id = 104)
  • Rows Removed by Filter: 478
16. 8.869 652.729 ↑ 2.7 54,936 1

Hash (cost=12,632.98..12,632.98 rows=149,058 width=16) (actual time=652.729..652.729 rows=54,936 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 1,678kB
17. 13.725 643.860 ↑ 2.7 54,936 1

Nested Loop (cost=11,029.41..12,632.98 rows=149,058 width=16) (actual time=506.346..643.860 rows=54,936 loops=1)

18. 124.698 520.263 ↓ 274.7 54,936 1

HashAggregate (cost=11,028.98..11,030.98 rows=200 width=8) (actual time=506.324..520.263 rows=54,936 loops=1)

  • Group Key: vcl.version_id
19. 395.565 395.565 ↓ 1.3 614,474 1

CTE Scan on vcl_filtered vcl (cost=0.00..9,803.54 rows=490,177 width=8) (actual time=56.120..395.565 rows=614,474 loops=1)

20. 109.872 109.872 ↑ 1.0 1 54,936

Index Scan using "versionPK" on version (cost=0.42..8.00 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=54,936)

  • Index Cond: (id = vcl.version_id)
21. 316.548 316.548 ↑ 1.0 1 158,274

Index Scan using "documentPK" on document (cost=0.42..0.65 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=158,274)

  • Index Cond: (id = version.document_id)
  • Filter: (project_id = 104)
Planning time : 1.430 ms
Execution time : 1,516.519 ms