explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JwY8

Settings
# exclusive inclusive rows x rows loops node
1. 11.572 4,011.616 ↑ 1.0 1 1

Aggregate (cost=6,032,401.36..6,032,401.37 rows=1 width=8) (actual time=4,011.616..4,011.616 rows=1 loops=1)

2. 78.597 4,000.044 ↓ 34.9 102,188 1

Subquery Scan on sq (cost=6,011,915.44..6,032,394.04 rows=2,926 width=0) (actual time=3,209.951..4,000.044 rows=102,188 loops=1)

  • Filter: (sq.row_num = 1)
  • Rows Removed by Filter: 601909
3. 423.069 3,921.447 ↓ 1.2 704,097 1

WindowAgg (cost=6,011,915.44..6,025,080.25 rows=585,103 width=36) (actual time=3,209.949..3,921.447 rows=704,097 loops=1)

4. 831.920 3,498.378 ↓ 1.2 704,097 1

Sort (cost=6,011,915.44..6,013,378.19 rows=585,103 width=28) (actual time=3,209.942..3,498.378 rows=704,097 loops=1)

  • Sort Key: api_upload.system_uuid, api_report.rule_id, api_upload.checked_on
  • Sort Method: external merge Disk: 26208kB
5. 0.000 2,666.458 ↓ 1.2 704,097 1

Gather (cost=88,310.92..5,941,867.45 rows=585,103 width=28) (actual time=545.310..2,666.458 rows=704,097 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 147.239 2,714.521 ↑ 1.0 234,699 3

Hash Join (cost=87,310.92..5,882,357.15 rows=243,793 width=28) (actual time=531.129..2,714.521 rows=234,699 loops=3)

  • Hash Cond: (api_report.rule_id = api_rule.id)
7. 1,776.313 2,565.008 ↓ 1.5 403,152 3

Nested Loop (cost=87,120.66..5,881,451.36 rows=271,485 width=28) (actual time=528.517..2,565.008 rows=403,152 loops=3)

8. 257.261 788.649 ↑ 1.3 35,898 3

Parallel Bitmap Heap Scan on api_upload (cost=87,102.23..307,665.33 rows=46,741 width=28) (actual time=528.019..788.649 rows=35,898 loops=3)

  • Recheck Cond: ((checked_on >= '2019-07-25 00:00:00+00'::timestamp with time zone) AND (checked_on <= '2019-08-01 23:59:59+00'::timestamp with time zone) AND ((account)::text = '729650'::text))
  • Rows Removed by Index Recheck: 384404
  • Heap Blocks: exact=12183
9. 1.857 531.388 ↓ 0.0 0 1

BitmapAnd (cost=87,102.23..87,102.23 rows=112,178 width=0) (actual time=531.388..531.388 rows=0 loops=1)

10. 190.197 190.197 ↓ 1.0 1,302,985 1

Bitmap Index Scan on api_upload_checked_on_01d47541 (cost=0.00..30,969.77 rows=1,266,121 width=0) (actual time=190.197..190.197 rows=1,302,985 loops=1)

  • Index Cond: ((checked_on >= '2019-07-25 00:00:00+00'::timestamp with time zone) AND (checked_on <= '2019-08-01 23:59:59+00'::timestamp with time zone))
11. 339.334 339.334 ↓ 1.0 2,666,576 1

Bitmap Index Scan on api_upload_account_e808b66a_like (cost=0.00..56,076.12 rows=2,601,008 width=0) (actual time=339.334..339.334 rows=2,666,576 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
12. 0.000 0.046 ↑ 87.3 11 107,695

Index Only Scan using api_report_upload_id_rule_id_1a55a613_uniq on api_report (cost=18.42..109.65 rows=960 width=8) (actual time=0.017..0.046 rows=11 loops=107,695)

  • Index Cond: (upload_id = api_upload.id)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 1
  • Heap Fetches: 415288
13.          

SubPlan (forIndex Only Scan)

14. 0.156 0.156 ↑ 1.0 355 3

Seq Scan on api_ack u0 (cost=0.00..16.96 rows=355 width=4) (actual time=0.016..0.156 rows=355 loops=3)

  • Filter: ((account)::text = '729650'::text)
  • Rows Removed by Filter: 442
15. 0.312 2.274 ↑ 1.0 942 3

Hash (cost=178.49..178.49 rows=942 width=4) (actual time=2.274..2.274 rows=942 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
16. 1.962 1.962 ↑ 1.0 942 3

Seq Scan on api_rule (cost=0.00..178.49 rows=942 width=4) (actual time=0.061..1.962 rows=942 loops=3)

  • Filter: active
  • Rows Removed by Filter: 107
Planning time : 1.058 ms
Execution time : 4,025.906 ms