explain.depesz.com

PostgreSQL's explain analyze made readable

Result: abGA

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 476,187.535 ↓ 23.0 23 1

Limit (cost=307,951.89..307,951.89 rows=1 width=53) (actual time=476,187.532..476,187.535 rows=23 loops=1)

2. 0.019 476,187.531 ↓ 23.0 23 1

Sort (cost=307,951.89..307,951.89 rows=1 width=53) (actual time=476,187.531..476,187.531 rows=23 loops=1)

  • Sort Key: (min(COALESCE(((documents.date_document)::date)::timestamp with time zone, access_to_doc.date_created))) DESC NULLS LAST, (max(documents.seqnum))
  • Sort Method: quicksort Memory: 28kB
3. 0.022 476,187.512 ↓ 23.0 23 1

GroupAggregate (cost=307,951.85..307,951.88 rows=1 width=53) (actual time=476,187.494..476,187.512 rows=23 loops=1)

  • Group Key: access_to_doc.document_id
4. 0.127 476,187.490 ↓ 55.0 55 1

Sort (cost=307,951.85..307,951.86 rows=1 width=61) (actual time=476,187.480..476,187.490 rows=55 loops=1)

  • Sort Key: access_to_doc.document_id
  • Sort Method: quicksort Memory: 32kB
5. 2,098.824 476,187.363 ↓ 55.0 55 1

Nested Loop Semi Join (cost=72,310.17..307,951.84 rows=1 width=61) (actual time=354,448.217..476,187.363 rows=55 loops=1)

  • Join Filter: ((documents.id)::text = (access_to_doc_1.document_id)::text)
6. 7.401 12,033.756 ↓ 3,317.0 3,317 1

Nested Loop Left Join (cost=1,082.72..234,066.24 rows=1 width=98) (actual time=262.668..12,033.756 rows=3,317 loops=1)

7. 8.964 12,026.355 ↓ 3,317.0 3,317 1

Nested Loop Left Join (cost=1,082.30..234,065.04 rows=1 width=107) (actual time=262.666..12,026.355 rows=3,317 loops=1)

8. 4.172 11,954.368 ↓ 3,317.0 3,317 1

Nested Loop Left Join (cost=1,081.88..234,063.82 rows=1 width=116) (actual time=262.539..11,954.368 rows=3,317 loops=1)

9. 46.264 10,813.676 ↓ 1,230.0 1,230 1

Nested Loop Left Join (cost=1,081.46..234,062.14 rows=1 width=116) (actual time=262.067..10,813.676 rows=1,230 loops=1)

  • Filter: ((access_to_doc.role_id = '6f2930c4-ac88-4cea-9b8b-00915a582843'::uuid) OR (document_recipients.emails @> '{Alina.Hulchevska@greif.com}'::character varying[]))
  • Rows Removed by Filter: 65,408
10. 40.112 7,568.788 ↓ 47.8 66,638 1

Nested Loop (cost=1,080.90..231,037.69 rows=1,394 width=123) (actual time=182.089..7,568.788 rows=66,638 loops=1)

11. 35.213 214.868 ↑ 1.0 71,704 1

Bitmap Heap Scan on access_to_doc (cost=1,080.35..65,200.87 rows=72,392 width=61) (actual time=181.544..214.868 rows=71,704 loops=1)

  • Recheck Cond: ((access_edrpou)::text = '20428705'::text)
  • Heap Blocks: exact=11,009
12. 179.655 179.655 ↑ 1.0 72,321 1

Bitmap Index Scan on ix_access_to_doc_access_edrpou (cost=0.00..1,062.25 rows=72,392 width=0) (actual time=179.655..179.655 rows=72,321 loops=1)

  • Index Cond: ((access_edrpou)::text = '20428705'::text)
13. 7,313.808 7,313.808 ↑ 1.0 1 71,704

Index Scan using documents_pkey on documents (cost=0.56..2.28 rows=1 width=99) (actual time=0.101..0.102 rows=1 loops=71,704)

  • Index Cond: ((id)::text = (access_to_doc.document_id)::text)
  • Filter: ((is_internal IS TRUE) AND (source = ANY ('{vchasno,vchasno_container}'::documentsource[])))
  • Rows Removed by Filter: 0
14. 3,198.624 3,198.624 ↓ 0.0 0 66,638

Index Scan using ix_document_recipients_document_id on document_recipients (cost=0.56..2.15 rows=1 width=93) (actual time=0.048..0.048 rows=0 loops=66,638)

  • Index Cond: ((document_id)::text = (documents.id)::text)
15. 1,136.520 1,136.520 ↓ 3.0 3 1,230

Index Scan using uix_document_tag on document_tags (cost=0.42..1.67 rows=1 width=53) (actual time=0.541..0.924 rows=3 loops=1,230)

  • Index Cond: ((document_id)::text = (documents.id)::text)
  • Filter: (company_id = '5bf3552c-045c-4ddb-a2f4-b1fc6abd3f92'::uuid)
16. 63.023 63.023 ↑ 1.0 1 3,317

Index Scan using ix_companies_edrpou on companies (cost=0.42..1.21 rows=1 width=25) (actual time=0.012..0.019 rows=1 loops=3,317)

  • Index Cond: ((edrpou)::text = (documents.edrpou_owner)::text)
17. 0.000 0.000 ↓ 0.0 0 3,317

Index Only Scan using ix_companies_edrpou on companies companies_1 (cost=0.42..1.19 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=3,317)

  • Index Cond: (edrpou = (document_recipients.edrpou)::text)
  • Heap Fetches: 0
18. 2,285.413 462,054.783 ↓ 0.0 0 3,317

Hash Join (cost=71,227.45..72,556.52 rows=1 width=74) (actual time=139.299..139.299 rows=0 loops=3,317)

  • Hash Cond: ((access_to_doc_1.document_id)::text = (documents_1.id)::text)
  • Join Filter: (((access_to_doc.document_id)::text = (access_to_doc_1.document_id)::text) AND (COALESCE(documents_1.date_document, access_to_doc_1.date_created) <= '2019-09-12 23:59:59'::timestamp without time zone) AND (COALESCE(documents_1.date_document, access_to_doc_1.date_created) >= '2019-09-09 00:00:00'::timestamp without time zone))
  • Rows Removed by Join Filter: 1
19. 13,496.873 459,722.932 ↑ 8.0 8,746 3,317

Unique (cost=71,225.16..71,587.12 rows=70,334 width=46) (actual time=127.411..138.596 rows=8,746 loops=3,317)

20. 352,699.927 446,226.059 ↑ 1.0 71,180 3,317

Sort (cost=71,225.16..71,406.14 rows=72,392 width=46) (actual time=127.410..134.527 rows=71,180 loops=3,317)

  • Sort Key: access_to_doc_1.document_id, ((access_to_doc_1.role_id = '6f2930c4-ac88-4cea-9b8b-00915a582843'::uuid)) DESC NULLS LAST, access_to_doc_1.date_created
  • Sort Method: quicksort Memory: 13,155kB
21. 78,188.324 93,526.132 ↑ 1.0 71,704 3,317

Bitmap Heap Scan on access_to_doc access_to_doc_1 (cost=1,080.35..65,381.85 rows=72,392 width=46) (actual time=5.830..28.196 rows=71,704 loops=3,317)

  • Recheck Cond: ((access_edrpou)::text = '20428705'::text)
  • Heap Blocks: exact=36,516,853
22. 15,337.808 15,337.808 ↑ 1.0 72,321 3,317

Bitmap Index Scan on ix_access_to_doc_access_edrpou (cost=0.00..1,062.25 rows=72,392 width=0) (actual time=4.624..4.624 rows=72,321 loops=3,317)

  • Index Cond: ((access_edrpou)::text = '20428705'::text)
23. 6.634 46.438 ↑ 1.0 1 3,317

Hash (cost=2.28..2.28 rows=1 width=45) (actual time=0.014..0.014 rows=1 loops=3,317)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 39.804 39.804 ↑ 1.0 1 3,317

Index Scan using documents_pkey on documents documents_1 (cost=0.56..2.28 rows=1 width=45) (actual time=0.012..0.012 rows=1 loops=3,317)

  • Index Cond: ((id)::text = (access_to_doc.document_id)::text)
Planning time : 55.289 ms
Execution time : 476,188.144 ms