explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IfoX : broup by

Settings
# exclusive inclusive rows x rows loops node
1. 236.090 220,599.406 ↑ 1.0 100,000 1

Sort (cost=1,494,190,496.32..1,494,190,746.32 rows=100,000 width=162) (actual time=220,560.148..220,599.406 rows=100,000 loops=1)

  • Sort Key: batch_search.batch_date DESC, batch_search_query.query_number
  • Sort Method: external merge Disk: 15488kB
2. 317.212 220,363.316 ↑ 1.0 100,000 1

Hash Left Join (cost=235,435.92..1,494,173,987.50 rows=100,000 width=162) (actual time=1,666.491..220,363.316 rows=100,000 loops=1)

  • Hash Cond: ((batch_search_query.query = countbyquery.query) AND (batch_search.uuid = countbyquery.search_uuid))
3. 63.835 83.783 ↑ 1.0 100,000 1

Hash Join (cost=4.50..2,309.12 rows=100,000 width=134) (actual time=0.465..83.783 rows=100,000 loops=1)

  • Hash Cond: (batch_search_query.search_uuid = batch_search.uuid)
4. 19.620 19.620 ↑ 1.0 100,000 1

Seq Scan on batch_search_query (cost=0.00..2,031.00 rows=100,000 width=50) (actual time=0.087..19.620 rows=100,000 loops=1)

5. 0.136 0.328 ↑ 1.0 100 1

Hash (cost=3.25..3.25 rows=100 width=121) (actual time=0.328..0.328 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
6. 0.192 0.192 ↑ 1.0 100 1

Seq Scan on batch_search (cost=0.00..3.25 rows=100 width=121) (actual time=0.038..0.192 rows=100 loops=1)

  • Filter: ((state)::text = 'QUEUED'::text)
7. 33.498 1,662.321 ↑ 1.0 100,000 1

Hash (cost=232,954.42..232,954.42 rows=100,000 width=54) (actual time=1,662.321..1,662.321 rows=100,000 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2638kB
8. 15.365 1,628.823 ↑ 1.0 100,000 1

Subquery Scan on countbyquery (cost=212,073.00..232,954.42 rows=100,000 width=54) (actual time=1,185.016..1,628.823 rows=100,000 loops=1)

9. 24.879 1,613.458 ↑ 1.0 100,000 1

Finalize GroupAggregate (cost=212,073.00..231,954.42 rows=100,000 width=54) (actual time=1,185.015..1,613.458 rows=100,000 loops=1)

  • Group Key: batch_search_query_1.query, batch_search_result.search_uuid
10. 0.000 1,588.579 ↑ 1.0 100,000 1

Gather Merge (cost=212,073.00..230,204.42 rows=100,000 width=54) (actual time=1,185.008..1,588.579 rows=100,000 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
11. 230.940 3,059.956 ↑ 2.0 50,000 2

Partial GroupAggregate (cost=211,072.99..217,954.41 rows=100,000 width=54) (actual time=1,174.067..1,529.978 rows=50,000 loops=2)

  • Group Key: batch_search_query_1.query, batch_search_result.search_uuid
12. 1,932.864 2,829.016 ↑ 1.2 500,000 2

Sort (cost=211,072.99..212,543.34 rows=588,142 width=46) (actual time=1,174.049..1,414.508 rows=500,000 loops=2)

  • Sort Key: batch_search_query_1.query, batch_search_result.search_uuid
  • Sort Method: external merge Disk: 27888kB
  • Worker 0: Sort Method: external merge Disk: 27928kB
13. 393.630 896.152 ↑ 1.2 500,000 2

Parallel Hash Left Join (cost=29,464.93..136,616.91 rows=588,142 width=46) (actual time=268.591..448.076 rows=500,000 loops=2)

  • Hash Cond: ((batch_search_query_1.query = batch_search_result.query) AND (batch_search_query_1.search_uuid = batch_search_result.search_uuid))
14. 16.242 16.242 ↑ 1.2 50,000 2

Parallel Seq Scan on batch_search_query batch_search_query_1 (cost=0.00..1,619.24 rows=58,824 width=46) (actual time=0.016..8.121 rows=50,000 loops=2)

15. 314.148 486.280 ↓ 1.2 500,000 2

Parallel Hash (cost=19,551.77..19,551.77 rows=416,677 width=46) (actual time=243.140..243.140 rows=500,000 loops=2)

  • Buckets: 65536 Batches: 32 Memory Usage: 3072kB
16. 172.132 172.132 ↓ 1.2 500,000 2

Parallel Seq Scan on batch_search_result (cost=0.00..19,551.77 rows=416,677 width=46) (actual time=0.073..86.066 rows=500,000 loops=2)

17.          

SubPlan (forHash Left Join)

18. 63,800.000 218,300.000 ↑ 1.0 1 100,000

Aggregate (cost=14,939.30..14,939.31 rows=1 width=8) (actual time=2.183..2.183 rows=1 loops=100,000)

19. 109,000.000 154,500.000 ↑ 1.0 10,000 100,000

Bitmap Heap Scan on batch_search_result batch_search_result_1 (cost=457.93..14,914.30 rows=10,000 width=0) (actual time=0.464..1.545 rows=10,000 loops=100,000)

  • Recheck Cond: (search_uuid = batch_search.uuid)
  • Heap Blocks: exact=15477000
20. 45,500.000 45,500.000 ↑ 1.0 10,000 100,000

Bitmap Index Scan on batch_search_result_uuid (cost=0.00..455.43 rows=10,000 width=0) (actual time=0.455..0.455 rows=10,000 loops=100,000)

  • Index Cond: (search_uuid = batch_search.uuid)
Planning time : 2.457 ms
Execution time : 220,637.428 ms