explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ojBl

Settings
# exclusive inclusive rows x rows loops node
1. 69.113 1,373.096 ↓ 448.4 158,274 1

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

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

CTE vcl_filtered

3. 244.808 288.727 ↓ 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=50.228..288.727 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. 43.919 43.919 ↓ 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=43.919..43.919 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,303.983 ↓ 448.4 158,274 1

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

6. 64.139 1,000.799 ↓ 73.1 158,274 1

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

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

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

8. 24.809 317.288 ↓ 36.2 158,274 1

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

9.          

CTE action_ids

10. 0.004 0.004 ↑ 1.0 44 1

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

11. 0.141 0.734 ↓ 1.1 19 1

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

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

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

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

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

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

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

15. 291.745 291.745 ↓ 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.010..15.355 rows=8,330 loops=19)

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

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

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

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

18. 101.802 487.687 ↓ 274.7 54,936 1

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

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

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