explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5oiI : Optimization for: plan #Bfd0

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.013 7,405.864 ↑ 1.0 10 1

Limit (cost=4,344.28..4,344.30 rows=10 width=3,168) (actual time=7,405.852..7,405.864 rows=10 loops=1)

2. 39.886 7,405.851 ↑ 19.9 10 1

Sort (cost=4,344.28..4,344.78 rows=199 width=3,168) (actual time=7,405.850..7,405.851 rows=10 loops=1)

  • Sort Key: (sum(document_ranking_documentrankingresult.rank_score)) DESC
  • Sort Method: top-N heapsort Memory: 41kB
3. 113.354 7,365.965 ↓ 98.1 19,513 1

GroupAggregate (cost=4,334.01..4,339.98 rows=199 width=3,168) (actual time=6,898.733..7,365.965 rows=19,513 loops=1)

  • Group Key: core_document.id, (CASE WHEN core_datasource.is_public THEN core_datasource.logo_url ELSE core_document.image_url END), core_datasource.is_public, core_datasourcelocation.name, ai_documentmeta.id, ai_documentmetasummarization.id
4. 665.269 7,252.611 ↓ 98.3 19,570 1

Sort (cost=4,334.01..4,334.51 rows=199 width=3,168) (actual time=6,898.710..7,252.611 rows=19,570 loops=1)

  • Sort Key: core_document.id, (CASE WHEN core_datasource.is_public THEN core_datasource.logo_url ELSE core_document.image_url END), core_datasource.is_public, core_datasourcelocation.name, ai_documentmeta.id, ai_documentmetasummarization.id
  • Sort Method: external merge Disk: 25,008kB
5. 18.947 6,587.342 ↓ 98.3 19,570 1

Nested Loop Left Join (cost=66.31..4,326.41 rows=199 width=3,168) (actual time=12.494..6,587.342 rows=19,570 loops=1)

6. 27.662 6,509.685 ↓ 98.3 19,570 1

Nested Loop Left Join (cost=65.91..4,193.68 rows=199 width=2,240) (actual time=12.465..6,509.685 rows=19,570 loops=1)

7. 20.502 6,266.753 ↓ 98.3 19,570 1

Hash Join (cost=65.49..4,093.20 rows=199 width=1,725) (actual time=12.449..6,266.753 rows=19,570 loops=1)

  • Hash Cond: (core_datasourcelocation.data_source_id = core_datasource.id)
8. 34.939 6,246.027 ↓ 98.3 19,570 1

Hash Join (cost=40.28..4,067.46 rows=199 width=1,696) (actual time=12.208..6,246.027 rows=19,570 loops=1)

  • Hash Cond: (core_document.data_source_location_id = core_datasourcelocation.id)
  • Join Filter: (((hashed SubPlan 1) AND core_datasourcelocation.is_enabled) OR (core_document.organization_id = 537))
9. 43.783 6,210.703 ↓ 46.0 19,570 1

Nested Loop (cost=1.90..4,027.95 rows=425 width=1,665) (actual time=11.786..6,210.703 rows=19,570 loops=1)

10. 5,168.850 5,168.850 ↓ 23.0 19,570 1

Index Scan using document_ra_client__12dfc7_idx on document_ranking_documentrankingresult (cost=0.43..912.04 rows=851 width=12) (actual time=11.713..5,168.850 rows=19,570 loops=1)

  • Index Cond: ((client_id = 414,253) AND (job_id = 1,482,346) AND (organization_id = 537))
11. 998.044 998.070 ↑ 1.0 1 19,570

Index Scan using core_document_pkey on core_document (cost=1.47..3.66 rows=1 width=1,657) (actual time=0.051..0.051 rows=1 loops=19,570)

  • Index Cond: (id = document_ranking_documentrankingresult.document_id)
  • Filter: (NOT (hashed SubPlan 2))
12.          

SubPlan (for Index Scan)

13. 0.026 0.026 ↓ 0.0 0 1

Seq Scan on document_ranking_dismisseddocument u0_1 (cost=0.00..1.04 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=1)

  • Filter: ((client_id = 414,253) AND (user_id = 1,048))
  • Rows Removed by Filter: 3
14. 0.154 0.324 ↑ 1.1 281 1

Hash (cost=13.07..13.07 rows=307 width=36) (actual time=0.324..0.324 rows=281 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
15. 0.170 0.170 ↑ 1.1 281 1

Seq Scan on core_datasourcelocation (cost=0.00..13.07 rows=307 width=36) (actual time=0.025..0.170 rows=281 loops=1)

16.          

SubPlan (for Hash Join)

17. 0.010 0.061 ↓ 5.0 5 1

Nested Loop (cost=0.55..21.47 rows=1 width=4) (actual time=0.034..0.061 rows=5 loops=1)

18. 0.019 0.019 ↓ 1.1 8 1

Index Scan using core_datasourceorganizationbinding_organization_id_919f6233 on core_datasourceorganizationbinding u0 (cost=0.27..7.91 rows=7 width=4) (actual time=0.012..0.019 rows=8 loops=1)

  • Index Cond: (organization_id = 537)
  • Filter: is_enabled
19. 0.032 0.032 ↑ 1.0 1 8

Index Scan using core_datasourcesettings_pkey on core_datasource u1 (cost=0.27..1.86 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: (id = u0.data_source_id)
  • Filter: is_public
  • Rows Removed by Filter: 0
20. 0.083 0.224 ↑ 1.1 251 1

Hash (cost=21.76..21.76 rows=276 width=37) (actual time=0.224..0.224 rows=251 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
21. 0.141 0.141 ↑ 1.1 251 1

Seq Scan on core_datasource (cost=0.00..21.76 rows=276 width=37) (actual time=0.011..0.141 rows=251 loops=1)

22. 215.270 215.270 ↑ 1.0 1 19,570

Index Scan using ai_documentmeta_document_id_key on ai_documentmeta (cost=0.42..0.50 rows=1 width=515) (actual time=0.011..0.011 rows=1 loops=19,570)

  • Index Cond: (core_document.id = document_id)
23. 58.710 58.710 ↓ 0.0 0 19,570

Index Scan using ai_documentmetasummarization_document_meta_id_6694f9ae_uniq on ai_documentmetasummarization (cost=0.40..0.67 rows=1 width=928) (actual time=0.003..0.003 rows=0 loops=19,570)

  • Index Cond: (ai_documentmeta.id = document_meta_id)
Planning time : 269.612 ms
Execution time : 7,422.729 ms