explain.depesz.com

PostgreSQL's explain analyze made readable

Result: isvz

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 12,534.292 ↑ 4,957.0 7 1

Subquery Scan on q1 (cost=964,934.28..965,368.02 rows=34,699 width=782) (actual time=12,534.290..12,534.292 rows=7 loops=1)

2. 0.018 12,534.289 ↑ 4,957.0 7 1

Sort (cost=964,934.28..965,021.03 rows=34,699 width=818) (actual time=12,534.289..12,534.289 rows=7 loops=1)

  • Sort Key: (max((document.code)::text)), (max(version.sequential))
  • Sort Method: quicksort Memory: 28kB
3. 0.097 12,534.271 ↑ 4,957.0 7 1

GroupAggregate (cost=948,878.05..949,745.53 rows=34,699 width=818) (actual time=12,534.188..12,534.271 rows=7 loops=1)

  • Group Key: workflow.id, document.id, version.sequential
4. 0.171 12,534.174 ↑ 159.9 217 1

Sort (cost=948,878.05..948,964.80 rows=34,699 width=782) (actual time=12,534.146..12,534.174 rows=217 loops=1)

  • Sort Key: workflow.id, document.id, version.sequential
  • Sort Method: quicksort Memory: 136kB
5. 5,597.385 12,534.003 ↑ 159.9 217 1

Hash Join (cost=65,060.65..934,280.79 rows=34,699 width=782) (actual time=8,021.727..12,534.003 rows=217 loops=1)

  • Hash Cond: (version.document_id = document.id)
  • Join Filter: (((up.status = 1) AND (up.user_id = 12252) AND ((lower((document.code)::text) ~~ '%ucr%'::text) OR (lower((document.title)::text) ~~ '%ucr%'::text) OR (lower((document.code)::text) ~~ '%civil%'::text) OR (lower((document.title)::text) ~~ '%civil%'::text))) OR ((document.content ->> 'model'::text) ~~* '%ucr%'::text) OR ((document.content ->> 'model'::text) ~~* '%civil%'::text) OR ((((attachment.file_name)::text ~~* '%ucr%'::text) OR ((attachment.file_name)::text ~~* '%civil%'::text)) AND (workflow.project_id = 260)))
  • Rows Removed by Join Filter: 288333
6. 1,842.549 6,736.231 ↑ 2.9 288,550 1

Hash Semi Join (cost=43,150.01..830,061.16 rows=834,510 width=102) (actual time=299.711..6,736.231 rows=288,550 loops=1)

  • Hash Cond: (version.id = vcl.version_id)
  • Join Filter: ((vcl.office_id IS NULL) OR (vcl.office_id = up.office_id))
  • Rows Removed by Join Filter: 1262402
7. 4,207.077 4,886.745 ↓ 1.4 22,501,292 1

Hash Join (cost=23,257.25..254,917.94 rows=16,049,953 width=118) (actual time=292.426..4,886.745 rows=22,501,292 loops=1)

  • Hash Cond: (workflow.project_id = up.config_item_group_id)
8. 168.043 678.882 ↓ 1.3 215,025 1

Hash Right Join (cost=23,174.38..45,826.11 rows=167,998 width=94) (actual time=291.627..678.882 rows=215,025 loops=1)

  • Hash Cond: (vat.version_id = version.id)
9. 70.732 219.440 ↓ 1.0 155,147 1

Merge Left Join (cost=0.84..16,150.91 rows=155,103 width=46) (actual time=0.037..219.440 rows=155,147 loops=1)

  • Merge Cond: (vat.attachment_id = attachment.id)
10. 64.835 64.835 ↓ 1.0 155,147 1

Index Scan using uc_version_attachmentattachment_id_col on version_attachment vat (cost=0.42..5,389.11 rows=155,103 width=16) (actual time=0.023..64.835 rows=155,147 loops=1)

11. 83.873 83.873 ↑ 1.0 174,017 1

Index Scan using "attachmentPK" on attachment (cost=0.42..8,387.94 rows=174,026 width=46) (actual time=0.010..83.873 rows=174,017 loops=1)

12. 55.948 291.399 ↑ 1.0 167,825 1

Hash (cost=19,432.56..19,432.56 rows=167,998 width=56) (actual time=291.399..291.399 rows=167,825 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2414kB
13. 127.637 235.451 ↑ 1.0 167,825 1

Hash Join (cost=6,410.87..19,432.56 rows=167,998 width=56) (actual time=50.670..235.451 rows=167,825 loops=1)

  • Hash Cond: (version.workflow_id = workflow.id)
14. 57.276 57.276 ↑ 1.0 167,993 1

Seq Scan on version (cost=0.00..7,104.98 rows=167,998 width=28) (actual time=0.009..57.276 rows=167,993 loops=1)

15. 31.428 50.538 ↓ 1.0 167,829 1

Hash (cost=3,002.72..3,002.72 rows=167,772 width=36) (actual time=50.538..50.538 rows=167,829 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3314kB
16. 19.110 19.110 ↓ 1.0 167,829 1

Seq Scan on workflow (cost=0.00..3,002.72 rows=167,772 width=36) (actual time=0.008..19.110 rows=167,829 loops=1)

17. 0.380 0.786 ↓ 1.0 2,574 1

Hash (cost=50.72..50.72 rows=2,572 width=32) (actual time=0.786..0.786 rows=2,574 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 193kB
18. 0.406 0.406 ↓ 1.0 2,574 1

Seq Scan on user_project up (cost=0.00..50.72 rows=2,572 width=32) (actual time=0.011..0.406 rows=2,574 loops=1)

19. 1.441 6.937 ↓ 1.0 10,404 1

Hash (cost=19,764.71..19,764.71 rows=10,244 width=16) (actual time=6.937..6.937 rows=10,404 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 616kB
20. 4.675 5.496 ↓ 1.0 10,404 1

Bitmap Heap Scan on vcl (cost=246.83..19,764.71 rows=10,244 width=16) (actual time=1.242..5.496 rows=10,404 loops=1)

  • Recheck Cond: ((profile_id IS NULL) OR (profile_id = 2))
  • Heap Blocks: exact=2266
21. 0.001 0.821 ↓ 0.0 0 1

BitmapOr (cost=246.83..246.83 rows=10,247 width=0) (actual time=0.821..0.821 rows=0 loops=1)

22. 0.814 0.814 ↓ 1.1 10,404 1

Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..219.34 rows=9,454 width=0) (actual time=0.814..0.814 rows=10,404 loops=1)

  • Index Cond: (profile_id IS NULL)
23. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..22.38 rows=793 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (profile_id = 2)
24. 123.767 200.387 ↓ 1.0 99,590 1

Hash (cost=11,329.84..11,329.84 rows=99,584 width=742) (actual time=200.387..200.387 rows=99,590 loops=1)

  • Buckets: 8192 Batches: 32 Memory Usage: 2531kB
25. 76.620 76.620 ↓ 1.0 99,590 1

Seq Scan on document (cost=0.00..11,329.84 rows=99,584 width=742) (actual time=0.036..76.620 rows=99,590 loops=1)

Planning time : 2.639 ms
Execution time : 12,534.454 ms