explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K72J

Settings
# exclusive inclusive rows x rows loops node
1. 0.943 422,069.598 ↓ 5.8 5,213 1

Unique (cost=2,209,645.13..2,209,649.64 rows=902 width=203) (actual time=422,066.727..422,069.598 rows=5,213 loops=1)

2. 41.144 422,068.655 ↓ 5.8 5,213 1

Sort (cost=2,209,645.13..2,209,647.38 rows=902 width=203) (actual time=422,066.725..422,068.655 rows=5,213 loops=1)

  • Sort Key: s.hostname_id
  • Sort Method: quicksort Memory: 1588kB
3. 40.646 422,027.511 ↓ 5.8 5,213 1

Nested Loop Left Join (cost=237.61..2,209,600.85 rows=902 width=203) (actual time=214.431..422,027.511 rows=5,213 loops=1)

  • Join Filter: (s.error_id = e.id)
  • Rows Removed by Join Filter: 57343
4. 30.111 421,971.226 ↓ 5.8 5,213 1

Nested Loop (cost=237.61..2,209,448.89 rows=902 width=210) (actual time=214.377..421,971.226 rows=5,213 loops=1)

5. 148.084 150.599 ↑ 1.1 6,356 1

Bitmap Heap Scan on cloudscans_hostnames h (cost=237.04..24,769.26 rows=7,022 width=82) (actual time=3.301..150.599 rows=6,356 loops=1)

  • Recheck Cond: (datastore_vendor_id = '5300526000373760'::bigint)
  • Filter: enabled
  • Rows Removed by Filter: 1152
  • Heap Blocks: exact=4274
6. 2.515 2.515 ↓ 1.1 8,029 1

Bitmap Index Scan on cloudscans_hostnames_vendorid (cost=0.00..235.28 rows=7,313 width=0) (actual time=2.515..2.515 rows=8,029 loops=1)

  • Index Cond: (datastore_vendor_id = '5300526000373760'::bigint)
7. 410,133.992 421,790.516 ↑ 1.0 1 6,356

Index Scan using cloudscans_scans_by_hostname_timestamp on cloudscans_scans s (cost=0.57..311.11 rows=1 width=136) (actual time=65.963..66.361 rows=1 loops=6,356)

  • Index Cond: (hostname_id = h.id)
  • Filter: ((NOT no_result) AND (scanned_at = (SubPlan 2)))
  • Rows Removed by Filter: 83
8.          

SubPlan (forIndex Scan)

9. 529.842 11,656.524 ↑ 1.0 1 529,842

Result (cost=0.90..0.91 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=529,842)

10.          

Initplan (forResult)

11. 1,059.684 11,126.682 ↑ 1.0 1 529,842

Limit (cost=0.57..0.90 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=529,842)

12. 10,066.998 10,066.998 ↑ 65.0 1 529,842

Index Only Scan Backward using cloudscans_scans_by_hostname_timestamp on cloudscans_scans s2 (cost=0.57..21.96 rows=65 width=8) (actual time=0.019..0.019 rows=1 loops=529,842)

  • Index Cond: ((hostname_id = s.hostname_id) AND (scanned_at IS NOT NULL) AND (scanned_at <= '2019-08-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 232070
13. 15.622 15.639 ↑ 1.0 11 5,213

Materialize (cost=0.00..3.17 rows=11 width=25) (actual time=0.002..0.003 rows=11 loops=5,213)

14. 0.017 0.017 ↑ 1.0 11 1

Seq Scan on cloudscans_errortypes e (cost=0.00..3.11 rows=11 width=25) (actual time=0.010..0.017 rows=11 loops=1)

Planning time : 2.626 ms
Execution time : 422,070.265 ms