explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 599I

Settings
# exclusive inclusive rows x rows loops node
1. 66.164 1,312.943 ↓ 448.4 158,274 1

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

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

CTE vcl_filtered

3. 234.333 285.777 ↓ 1.3 614,474 1

Bitmap Heap Scan on vcl vcl_1 (cost=16,966.37..118,984.92 rows=490,177 width=8) (actual time=57.451..285.777 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. 51.444 51.444 ↓ 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=51.444..51.444 rows=769,571 loops=1)

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

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

6. 60.631 958.221 ↓ 73.1 158,274 1

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

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

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

8. 24.104 303.761 ↓ 36.2 158,274 1

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

9.          

CTE action_ids

10. 0.015 0.015 ↑ 1.0 44 1

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

11. 0.121 0.661 ↓ 1.1 19 1

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

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

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

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

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

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

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

15. 278.996 278.996 ↓ 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.013..14.684 rows=8,330 loops=19)

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

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

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

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

18. 92.253 470.296 ↓ 274.7 54,936 1

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

  • Group Key: vcl.id
19. 378.043 378.043 ↓ 1.3 614,474 1

CTE Scan on vcl_filtered vcl (cost=0.00..9,803.54 rows=490,177 width=8) (actual time=57.452..378.043 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.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.467 ms
Execution time : 1,322.116 ms