#	rows x	node
1.	↓ 0.0	→ Subquery Scan on q1 (cost=964,934.28..965,368.02 rows=34,699 width=782) (actual rows= loops=)
2.	↓ 0.0	→ Sort (cost=964,934.28..965,021.03 rows=34,699 width=818) (actual rows= loops=) Sort Key: (max((document.code)::text)), (max(version.sequential))
3.	↓ 0.0	→ GroupAggregate (cost=948,878.05..949,745.53 rows=34,699 width=818) (actual rows= loops=) Group Key: workflow.id, document.id, version.sequential
4.	↓ 0.0	→ Sort (cost=948,878.05..948,964.80 rows=34,699 width=782) (actual rows= loops=) Sort Key: workflow.id, document.id, version.sequential
5.	↓ 0.0	→ Hash Join (cost=65,060.65..934,280.79 rows=34,699 width=782) (actual rows= loops=) Hash Cond: (version.document_id = document.id) Join Filter: (((up.status = 1) AND (up.user_id = 12,252) 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)))
6.	↓ 0.0	→ Hash Semi Join (cost=43,150.01..830,061.16 rows=834,510 width=102) (actual rows= loops=) Hash Cond: (version.id = vcl.version_id) Join Filter: ((vcl.office_id IS NULL) OR (vcl.office_id = up.office_id))
7.	↓ 0.0	→ Hash Join (cost=23,257.25..254,917.94 rows=16,049,953 width=118) (actual rows= loops=) Hash Cond: (workflow.project_id = up.config_item_group_id)
8.	↓ 0.0	→ Hash Right Join (cost=23,174.38..45,826.11 rows=167,998 width=94) (actual rows= loops=) Hash Cond: (vat.version_id = version.id)
9.	↓ 0.0	→ Merge Left Join (cost=0.84..16,150.91 rows=155,103 width=46) (actual rows= loops=) Merge Cond: (vat.attachment_id = attachment.id)
10.	↓ 0.0	→ Index Scan using uc_version_attachmentattachment_id_col on version_attachment vat (cost=0.42..5,389.11 rows=155,103 width=16) (actual rows= loops=)
11.	↓ 0.0	→ Index Scan using "attachmentPK" on attachment (cost=0.42..8,387.94 rows=174,026 width=46) (actual rows= loops=)
12.	↓ 0.0	→ Hash (cost=19,432.56..19,432.56 rows=167,998 width=56) (actual rows= loops=)
13.	↓ 0.0	→ Hash Join (cost=6,410.87..19,432.56 rows=167,998 width=56) (actual rows= loops=) Hash Cond: (version.workflow_id = workflow.id)
14.	↓ 0.0	→ Seq Scan on version (cost=0.00..7,104.98 rows=167,998 width=28) (actual rows= loops=)
15.	↓ 0.0	→ Hash (cost=3,002.72..3,002.72 rows=167,772 width=36) (actual rows= loops=)
16.	↓ 0.0	→ Seq Scan on workflow (cost=0.00..3,002.72 rows=167,772 width=36) (actual rows= loops=)
17.	↓ 0.0	→ Hash (cost=50.72..50.72 rows=2,572 width=32) (actual rows= loops=)
18.	↓ 0.0	→ Seq Scan on user_project up (cost=0.00..50.72 rows=2,572 width=32) (actual rows= loops=)
19.	↓ 0.0	→ Hash (cost=19,764.71..19,764.71 rows=10,244 width=16) (actual rows= loops=)
20.	↓ 0.0	→ Bitmap Heap Scan on vcl (cost=246.83..19,764.71 rows=10,244 width=16) (actual rows= loops=) Recheck Cond: ((profile_id IS NULL) OR (profile_id = 2))
21.	↓ 0.0	→ BitmapOr (cost=246.83..246.83 rows=10,247 width=0) (actual rows= loops=)
22.	↓ 0.0	→ Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..219.34 rows=9,454 width=0) (actual rows= loops=) Index Cond: (profile_id IS NULL)
23.	↓ 0.0	→ Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..22.38 rows=793 width=0) (actual rows= loops=) Index Cond: (profile_id = 2)
24.	↓ 0.0	→ Hash (cost=11,329.84..11,329.84 rows=99,584 width=742) (actual rows= loops=)
25.	↓ 0.0	→ Seq Scan on document (cost=0.00..11,329.84 rows=99,584 width=742) (actual rows= loops=)

Subquery Scan on q1  (cost=964934.28..965368.02 rows=34699 width=782)
  ->  Sort  (cost=964934.28..965021.03 rows=34699 width=818)
        Sort Key: (max((document.code)::text)), (max(version.sequential))
        ->  GroupAggregate  (cost=948878.05..949745.53 rows=34699 width=818)
              Group Key: workflow.id, document.id, version.sequential
              ->  Sort  (cost=948878.05..948964.80 rows=34699 width=782)
                    Sort Key: workflow.id, document.id, version.sequential
                    ->  Hash Join  (cost=65060.65..934280.79 rows=34699 width=782)
                          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)))
                          ->  Hash Semi Join  (cost=43150.01..830061.16 rows=834510 width=102)
                                Hash Cond: (version.id = vcl.version_id)
                                Join Filter: ((vcl.office_id IS NULL) OR (vcl.office_id = up.office_id))
                                ->  Hash Join  (cost=23257.25..254917.94 rows=16049953 width=118)
                                      Hash Cond: (workflow.project_id = up.config_item_group_id)
                                      ->  Hash Right Join  (cost=23174.38..45826.11 rows=167998 width=94)
                                            Hash Cond: (vat.version_id = version.id)
                                            ->  Merge Left Join  (cost=0.84..16150.91 rows=155103 width=46)
                                                  Merge Cond: (vat.attachment_id = attachment.id)
                                                  ->  Index Scan using uc_version_attachmentattachment_id_col on version_attachment vat  (cost=0.42..5389.11 rows=155103 width=16)
                                                  ->  Index Scan using "attachmentPK" on attachment  (cost=0.42..8387.94 rows=174026 width=46)
                                            ->  Hash  (cost=19432.56..19432.56 rows=167998 width=56)
                                                  ->  Hash Join  (cost=6410.87..19432.56 rows=167998 width=56)
                                                        Hash Cond: (version.workflow_id = workflow.id)
                                                        ->  Seq Scan on version  (cost=0.00..7104.98 rows=167998 width=28)
                                                        ->  Hash  (cost=3002.72..3002.72 rows=167772 width=36)
                                                              ->  Seq Scan on workflow  (cost=0.00..3002.72 rows=167772 width=36)
                                      ->  Hash  (cost=50.72..50.72 rows=2572 width=32)
                                            ->  Seq Scan on user_project up  (cost=0.00..50.72 rows=2572 width=32)
                                ->  Hash  (cost=19764.71..19764.71 rows=10244 width=16)
                                      ->  Bitmap Heap Scan on vcl  (cost=246.83..19764.71 rows=10244 width=16)
                                            Recheck Cond: ((profile_id IS NULL) OR (profile_id = 2))
                                            ->  BitmapOr  (cost=246.83..246.83 rows=10247 width=0)
                                                  ->  Bitmap Index Scan on vcl_profile_id_version_id_idx  (cost=0.00..219.34 rows=9454 width=0)
                                                        Index Cond: (profile_id IS NULL)
                                                  ->  Bitmap Index Scan on vcl_profile_id_version_id_idx  (cost=0.00..22.38 rows=793 width=0)
                                                        Index Cond: (profile_id = 2)
                          ->  Hash  (cost=11329.84..11329.84 rows=99584 width=742)
                                ->  Seq Scan on document  (cost=0.00..11329.84 rows=99584 width=742)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.000 ms	0.0 %
Bitmap Index Scan	2	0.000 ms	0.0 %
BitmapOr	1	0.000 ms	0.0 %
GroupAggregate	1	0.000 ms	0.0 %
Hash	5	0.000 ms	0.0 %
Hash Join	3	0.000 ms	0.0 %
Hash Right Join	1	0.000 ms	0.0 %
Hash Semi Join	1	0.000 ms	0.0 %
Index Scan	2	0.000 ms	0.0 %
Merge Left Join	1	0.000 ms	0.0 %
Seq Scan	4	0.000 ms	0.0 %
Sort	2	0.000 ms	0.0 %
Subquery Scan	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
attachment	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
document	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
user_project	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
vcl	1	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %
version	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
version_attachment	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
workflow	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4t1pg

Color mode:

Visible columns:

Per node type stats

Per table stats