explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LZ7l

Settings
# exclusive inclusive rows x rows loops node
1. 0.210 2,077.859 ↓ 10.0 10 1

Limit (cost=134,453.72..134,453.75 rows=1 width=576) (actual time=2,077.634..2,077.859 rows=10 loops=1)

2. 0.019 2,077.649 ↓ 10.0 10 1

Unique (cost=134,453.72..134,453.75 rows=1 width=576) (actual time=2,077.632..2,077.649 rows=10 loops=1)

3. 5.249 2,077.630 ↓ 10.0 10 1

Sort (cost=134,453.72..134,453.72 rows=1 width=576) (actual time=2,077.629..2,077.630 rows=10 loops=1)

  • Sort Key: docs.doc_creation, docs.doc_publication_number, docs.doc_id, docs.doc_is_patent, docs.doc_title, docmetadatas.application_date, docmetadatas.priority_date_earliest, docmetadatas.publication_date, docmetadatas.inventor, docmetadatas.assignee, docmetadatas.cpc, dochtmls.doc_id
  • Sort Method: quicksort Memory: 333kB
4. 695.980 2,072.381 ↓ 932.0 932 1

Nested Loop Left Join (cost=134,151.77..134,453.71 rows=1 width=576) (actual time=268.707..2,072.381 rows=932 loops=1)

  • Join Filter: (docs.doc_id = dochtmls.doc_id)
  • Rows Removed by Join Filter: 5825932
5. 1.726 299.009 ↓ 932.0 932 1

Nested Loop Left Join (cost=134,151.77..134,171.99 rows=1 width=560) (actual time=267.527..299.009 rows=932 loops=1)

6. 1.517 290.759 ↓ 932.0 932 1

Nested Loop (cost=134,151.35..134,165.08 rows=1 width=98) (actual time=267.506..290.759 rows=932 loops=1)

7. 2.858 282.286 ↓ 1,739.0 1,739 1

Nested Loop Left Join (cost=134,150.92..134,157.86 rows=1 width=130) (actual time=267.465..282.286 rows=1,739 loops=1)

  • Filter: (corr_docs_labels_left.id IS NULL)
  • Rows Removed by Filter: 90
8. 3.409 272.112 ↓ 1,829.0 1,829 1

Merge Join (cost=134,150.50..134,150.64 rows=1 width=130) (actual time=267.435..272.112 rows=1,829 loops=1)

  • Merge Cond: (docs.doc_id = unionmetadataapplicationstatus.doc_id)
9. 1.500 139.986 ↓ 1,829.0 1,829 1

Sort (cost=67,184.73..67,184.74 rows=1 width=114) (actual time=139.461..139.986 rows=1,829 loops=1)

  • Sort Key: docs.doc_id
  • Sort Method: quicksort Memory: 336kB
10. 1.031 138.486 ↓ 1,829.0 1,829 1

Nested Loop (cost=66,965.06..67,184.72 rows=1 width=114) (actual time=131.388..138.486 rows=1,829 loops=1)

11. 2.248 131.968 ↓ 70.3 1,829 1

HashAggregate (cost=66,964.63..66,964.89 rows=26 width=1,048) (actual time=131.364..131.968 rows=1,829 loops=1)

  • Group Key: docmetadatafilters.doc_id, docmetadatafilters.label, docmetadatafilters.value
12. 2.587 129.720 ↓ 102.9 2,675 1

Gather (cost=1,000.42..66,964.44 rows=26 width=1,048) (actual time=0.866..129.720 rows=2,675 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 0.131 127.133 ↓ 46.9 892 3 / 3

Parallel Append (cost=0.42..65,961.84 rows=19 width=1,048) (actual time=42.352..127.133 rows=892 loops=3)

14. 0.267 90.905 ↓ 551.0 551 3 / 3

Nested Loop (cost=0.56..38,958.10 rows=1 width=34) (actual time=48.622..90.905 rows=551 loops=3)

15. 83.790 83.790 ↑ 1.5 978 3 / 3

Parallel Seq Scan on docs docs_1 (cost=0.00..26,813.59 rows=1,432 width=16) (actual time=48.564..83.790 rows=978 loops=3)

  • Filter: ((NOT doc_deleted) AND (workspace_id = '27640d72-970a-4451-aab3-d3dd372212d4'::uuid))
  • Rows Removed by Filter: 300793
16. 6.848 6.848 ↑ 1.0 1 2,935 / 3

Index Only Scan using doc_metadata_filters_row on doc_metadata_filters docmetadatafilters (cost=0.56..8.47 rows=1 width=34) (actual time=0.007..0.007 rows=1 loops=2,935)

  • Index Cond: ((doc_id = docs_1.doc_id) AND (label = 'legal_status'::text) AND (value = 'Alive'::text))
  • Heap Fetches: 1654
17. 3.332 36.097 ↓ 34.0 510 2 / 3

Nested Loop (cost=0.42..27,003.38 rows=15 width=40) (actual time=21.414..54.146 rows=510 loops=2)

18. 11.537 11.537 ↓ 2.8 10,614 2 / 3

Parallel Seq Scan on doc_family_member_metadata_filters docfamilymembermetadatafilters (cost=0.00..4,113.16 rows=3,811 width=40) (actual time=0.009..17.306 rows=10,614 loops=2)

  • Filter: (((label)::text = 'legal_status'::text) AND ((value)::text = 'Alive'::text))
  • Rows Removed by Filter: 80827
19. 21.228 21.228 ↓ 0.0 0 21,228 / 3

Index Scan using docs_pkey on docs docs_2 (cost=0.42..6.01 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=21,228)

  • Index Cond: (doc_id = docfamilymembermetadatafilters.doc_id)
  • Filter: ((NOT doc_deleted) AND (workspace_id = '27640d72-970a-4451-aab3-d3dd372212d4'::uuid))
  • Rows Removed by Filter: 1
20. 5.487 5.487 ↑ 1.0 1 1,829

Index Scan using docs_pkey on docs (cost=0.42..8.45 rows=1 width=98) (actual time=0.003..0.003 rows=1 loops=1,829)

  • Index Cond: (doc_id = docmetadatafilters.doc_id)
  • Filter: ((NOT doc_deleted) AND (workspace_id = '27640d72-970a-4451-aab3-d3dd372212d4'::uuid))
21. 1.521 128.717 ↓ 70.3 1,829 1

Sort (cost=66,965.76..66,965.83 rows=26 width=16) (actual time=127.964..128.717 rows=1,829 loops=1)

  • Sort Key: unionmetadataapplicationstatus.doc_id
  • Sort Method: quicksort Memory: 134kB
22. 0.267 127.196 ↓ 70.3 1,829 1

Subquery Scan on unionmetadataapplicationstatus (cost=66,964.63..66,965.15 rows=26 width=16) (actual time=126.356..127.196 rows=1,829 loops=1)

23. 2.350 126.929 ↓ 70.3 1,829 1

HashAggregate (cost=66,964.63..66,964.89 rows=26 width=1,048) (actual time=126.354..126.929 rows=1,829 loops=1)

  • Group Key: docmetadatafilters_1.doc_id, docmetadatafilters_1.label, docmetadatafilters_1.value
24. 2.170 124.579 ↓ 102.9 2,675 1

Gather (cost=1,000.42..66,964.44 rows=26 width=1,048) (actual time=0.520..124.579 rows=2,675 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 0.124 122.409 ↓ 46.9 892 3 / 3

Parallel Append (cost=0.42..65,961.84 rows=19 width=1,048) (actual time=38.251..122.409 rows=892 loops=3)

26. 0.061 90.562 ↓ 551.0 551 3 / 3

Nested Loop (cost=0.56..38,958.10 rows=1 width=34) (actual time=46.699..90.562 rows=551 loops=3)

27. 83.653 83.653 ↑ 1.5 978 3 / 3

Parallel Seq Scan on docs docs_3 (cost=0.00..26,813.59 rows=1,432 width=16) (actual time=46.643..83.653 rows=978 loops=3)

  • Filter: ((NOT doc_deleted) AND (workspace_id = '27640d72-970a-4451-aab3-d3dd372212d4'::uuid))
  • Rows Removed by Filter: 300793
28. 6.848 6.848 ↑ 1.0 1 2,935 / 3

Index Only Scan using doc_metadata_filters_row on doc_metadata_filters docmetadatafilters_1 (cost=0.56..8.47 rows=1 width=34) (actual time=0.006..0.007 rows=1 loops=2,935)

  • Index Cond: ((doc_id = docs_3.doc_id) AND (label = 'legal_status'::text) AND (value = 'Alive'::text))
  • Heap Fetches: 1654
29. 0.571 31.723 ↓ 34.0 510 2 / 3

Nested Loop (cost=0.42..27,003.38 rows=15 width=40) (actual time=18.518..47.585 rows=510 loops=2)

30. 9.925 9.925 ↓ 2.8 10,614 2 / 3

Parallel Seq Scan on doc_family_member_metadata_filters docfamilymembermetadatafilters_1 (cost=0.00..4,113.16 rows=3,811 width=40) (actual time=0.010..14.887 rows=10,614 loops=2)

  • Filter: (((label)::text = 'legal_status'::text) AND ((value)::text = 'Alive'::text))
  • Rows Removed by Filter: 80827
31. 21.228 21.228 ↓ 0.0 0 21,228 / 3

Index Scan using docs_pkey on docs docs_4 (cost=0.42..6.01 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=21,228)

  • Index Cond: (doc_id = docfamilymembermetadatafilters_1.doc_id)
  • Filter: ((NOT doc_deleted) AND (workspace_id = '27640d72-970a-4451-aab3-d3dd372212d4'::uuid))
  • Rows Removed by Filter: 1
32. 7.316 7.316 ↓ 0.0 0 1,829

Index Scan using corr_doc_labels_rows on corr_docs_labels corr_docs_labels_left (cost=0.42..7.21 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1,829)

  • Index Cond: ((label_id = '71a7d406-7d75-4dbb-8535-8534f6068501'::uuid) AND (docs.doc_id = doc_id) AND (corr_docs_labels_deleted = false))
  • Filter: (NOT corr_docs_labels_deleted)
33. 6.956 6.956 ↑ 1.0 1 1,739

Index Only Scan using corr_doc_labels_rows on corr_docs_labels corr_docs_labels_inner (cost=0.42..7.21 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,739)

  • Index Cond: ((label_id = '9d3b58c9-e8ff-4d68-baa4-4176af09aac8'::uuid) AND (doc_id = docs.doc_id) AND (corr_docs_labels_deleted = false))
  • Filter: (NOT corr_docs_labels_deleted)
  • Heap Fetches: 932
34. 6.524 6.524 ↑ 1.0 1 932

Index Scan using doc_metadata_doc_id on doc_metadata docmetadatas (cost=0.42..6.90 rows=1 width=478) (actual time=0.006..0.007 rows=1 loops=932)

  • Index Cond: (docs.doc_id = doc_id)
35. 1,077.392 1,077.392 ↑ 1.0 6,252 932

Seq Scan on doc_html dochtmls (cost=0.00..203.57 rows=6,252 width=16) (actual time=0.002..1.156 rows=6,252 loops=932)

  • Filter: (NOT doc_html_deleted)
  • Rows Removed by Filter: 5