explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pynu

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 2,321.175 ↑ 1.0 1 1

Aggregate (cost=5,092,375,236.83..5,092,375,236.84 rows=1 width=8) (actual time=2,321.175..2,321.175 rows=1 loops=1)

2. 1.462 2,321.126 ↑ 7.2 813 1

Unique (cost=5,092,375,119.75..5,092,375,163.65 rows=5,854 width=60) (actual time=2,318.821..2,321.126 rows=813 loops=1)

3. 10.175 2,319.664 ↓ 2.7 15,861 1

Sort (cost=5,092,375,119.75..5,092,375,134.38 rows=5,854 width=60) (actual time=2,318.820..2,319.664 rows=15,861 loops=1)

  • Sort Key: api_upload.system_uuid, api_report.rule_id
  • Sort Method: quicksort Memory: 1,624kB
4. 1,240.679 2,309.489 ↓ 2.7 15,861 1

Nested Loop (cost=13.84..5,092,374,753.43 rows=5,854 width=60) (actual time=58.605..2,309.489 rows=15,861 loops=1)

  • Join Filter: (api_report.rule_id = api_rule.id)
  • Rows Removed by Join Filter: 20,528,007
5. 4.037 146.560 ↓ 4.1 26,350 1

Nested Loop (cost=13.84..5,092,265,187.49 rows=6,399 width=20) (actual time=57.763..146.560 rows=26,350 loops=1)

6. 65.519 65.519 ↓ 1.1 2,484 1

Index Scan using api_upload_account_id_checked_on_index on api_upload (cost=0.56..357,849.14 rows=2,161 width=20) (actual time=57.661..65.519 rows=2,484 loops=1)

  • Index Cond: (((account)::text = '540155'::text) AND (checked_on >= '2019-02-27 00:00:00+00'::timestamp with time zone) AND (checked_on <= '2019-08-26 23:59:59+00'::timestamp with time zone))
  • Filter: (NOT is_satellite)
  • Rows Removed by Filter: 96,277
7. 24.281 77.004 ↑ 46.5 11 2,484

Index Only Scan using api_report_upload_rule_id_idx on api_report (cost=13.28..2,356,268.53 rows=511 width=8) (actual time=0.006..0.031 rows=11 loops=2,484)

  • Index Cond: (upload_id = api_upload.id)
  • Filter: ((NOT (hashed SubPlan 1)) AND (NOT (SubPlan 2)))
  • Rows Removed by Filter: 2
  • Heap Fetches: 461
8.          

SubPlan (for Index Only Scan)

9. 0.012 0.023 ↓ 1.2 23 1

Bitmap Heap Scan on api_ack u0 (cost=4.42..12.66 rows=19 width=4) (actual time=0.016..0.023 rows=23 loops=1)

  • Recheck Cond: ((account)::text = '540155'::text)
  • Heap Blocks: exact=5
10. 0.011 0.011 ↓ 1.5 28 1

Bitmap Index Scan on api_ack_account_0fd242e7_like (cost=0.00..4.42 rows=19 width=0) (actual time=0.011..0.011 rows=28 loops=1)

  • Index Cond: ((account)::text = '540155'::text)
11. 26.350 52.700 ↓ 0.0 0 26,350

Sort (cost=1,153.83..1,153.85 rows=9 width=85) (actual time=0.002..0.002 rows=0 loops=26,350)

  • Sort Key: u1.rule_id, api_upload_1.system_uuid, api_upload_1.checked_on
  • Sort Method: quicksort Memory: 25kB
12. 0.000 26.350 ↓ 0.0 0 26,350

Nested Loop (cost=11.28..1,153.69 rows=9 width=85) (actual time=0.001..0.001 rows=0 loops=26,350)

13. 0.000 26.350 ↓ 0.0 0 26,350

Nested Loop (cost=10.71..900.84 rows=221 width=89) (actual time=0.001..0.001 rows=0 loops=26,350)

14. 26.350 26.350 ↓ 0.0 0 26,350

Nested Loop (cost=0.43..16.48 rows=1 width=77) (actual time=0.001..0.001 rows=0 loops=26,350)

15. 0.000 0.000 ↓ 0.0 0 26,350

Index Only Scan using api_hostack_rule_id_account_system_uuid_91ea3cb1_uniq on api_hostack u2 (cost=0.15..8.17 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=26,350)

  • Index Cond: ((rule_id = api_report.rule_id) AND (account = '540155'::text))
  • Heap Fetches: 0
16. 0.000 0.000 ↓ 0.0 0

Index Scan using api_rule_pkey on api_rule u1 (cost=0.28..8.29 rows=1 width=61) (never executed)

  • Index Cond: (id = api_report.rule_id)
17. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on api_upload api_upload_1 (cost=10.28..882.16 rows=221 width=28) (never executed)

  • Recheck Cond: (system_uuid = u2.system_uuid)
18. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on api_upload_system_uuid_source_id_checked_on_feb91ea9_uniq (cost=0.00..10.22 rows=221 width=0) (never executed)

  • Index Cond: (system_uuid = u2.system_uuid)
19. 0.000 0.000 ↓ 0.0 0

Index Only Scan using api_report_upload_rule_id_idx on api_report u0_1 (cost=0.57..4.57 rows=7 width=12) (never executed)

  • Index Cond: ((upload_id = api_upload_1.id) AND (rule_id = api_report.rule_id))
  • Heap Fetches: 0
20. 921.776 922.250 ↑ 1.5 780 26,350

Materialize (cost=0.00..327.14 rows=1,139 width=4) (actual time=0.000..0.035 rows=780 loops=26,350)

21. 0.474 0.474 ↑ 1.1 1,016 1

Seq Scan on api_rule (cost=0.00..321.45 rows=1,139 width=4) (actual time=0.005..0.474 rows=1,016 loops=1)

  • Filter: active
  • Rows Removed by Filter: 94
Planning time : 1.183 ms
Execution time : 2,321.385 ms