explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bfd0

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 810.660 4,042.321 ↑ 1.0 10 1

Limit (cost=10,505.91..10,505.93 rows=10 width=1,683) (actual time=3,231.661..4,042.321 rows=10 loops=1)

2. 40.576 3,231.661 ↑ 70.3 10 1

Sort (cost=10,505.91..10,507.67 rows=703 width=1,683) (actual time=3,231.660..3,231.661 rows=10 loops=1)

  • Sort Key: (sum(document_ranking_documentrankingresult.rank_score)) DESC
  • Sort Method: top-N heapsort Memory: 44kB
3. 0.000 3,191.085 ↓ 46.1 32,403 1

Finalize GroupAggregate (cost=10,421.59..10,490.72 rows=703 width=1,683) (actual time=2,812.664..3,191.085 rows=32,403 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
4. 2,504.167 3,932.768 ↓ 78.3 32,403 1

Gather Merge (cost=10,421.59..10,478.51 rows=414 width=1,683) (actual time=2,812.610..3,932.768 rows=32,403 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 22.650 1,428.601 ↓ 39.1 16,202 2 / 2

Partial GroupAggregate (cost=9,421.58..9,431.93 rows=414 width=1,683) (actual time=1,278.538..1,428.601 rows=16,202 loops=2)

  • 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
6. 990.664 1,405.951 ↓ 39.2 16,240 2 / 2

Sort (cost=9,421.58..9,422.61 rows=414 width=1,683) (actual time=1,278.529..1,405.951 rows=16,240 loops=2)

  • 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
  • Sort Method: external merge Disk: 41,408kB
7. 19.534 415.287 ↓ 39.2 16,240 2 / 2

Hash Join (cost=65.49..9,403.58 rows=414 width=1,683) (actual time=4.083..415.287 rows=16,240 loops=2)

  • Hash Cond: (core_datasourcelocation.data_source_id = core_datasource.id)
8. 31.611 391.909 ↓ 39.2 16,240 2 / 2

Hash Join (cost=40.28..9,377.26 rows=414 width=1,654) (actual time=0.170..391.909 rows=16,240 loops=2)

  • 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. 76.320 360.171 ↓ 18.4 16,240 2 / 2

Nested Loop (cost=1.90..9,336.55 rows=883 width=1,623) (actual time=0.034..360.171 rows=16,240 loops=2)

10. 105.217 105.217 ↓ 9.2 16,240 2 / 2

Parallel Index Scan using document_ra_client__12dfc7_idx on document_ranking_documentrankingresult (cost=0.43..3,008.85 rows=1,765 width=12) (actual time=0.014..105.217 rows=16,240 loops=2)

  • Index Cond: ((client_id = 414,257) AND (job_id = 1,482,346) AND (organization_id = 537))
11. 178.630 178.635 ↑ 1.0 1 32,479 / 2

Index Scan using core_document_pkey on core_document (cost=1.47..3.59 rows=1 width=1,615) (actual time=0.011..0.011 rows=1 loops=32,479)

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

SubPlan (for Index Scan)

13. 0.004 0.004 ↓ 0.0 0 1 / 2

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

  • Filter: ((client_id = 414,257) AND (user_id = 1,048))
  • Rows Removed by Filter: 3
14. 0.050 0.098 ↑ 1.1 281 1 / 2

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

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

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

16.          

SubPlan (for Hash Join)

17. 0.004 0.029 ↓ 5.0 5 1 / 2

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

18. 0.009 0.009 ↓ 1.1 8 1 / 2

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

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

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.149 3.844 ↑ 1.1 251 2 / 2

Hash (cost=21.76..21.76 rows=276 width=37) (actual time=3.844..3.844 rows=251 loops=2)

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

Seq Scan on core_datasource (cost=0.00..21.76 rows=276 width=37) (actual time=3.537..3.695 rows=251 loops=2)

Planning time : 2.136 ms
Execution time : 4,061.131 ms