explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nkiF

Settings
# exclusive inclusive rows x rows loops node
1. 0.658 298.956 ↓ 2.4 169 1

GroupAggregate (cost=31,857.84..31,859.24 rows=70 width=17) (actual time=298.204..298.956 rows=169 loops=1)

  • Group Key: st.assetidval, st.status
2. 0.000 298.298 ↓ 41.1 2,880 1

Sort (cost=31,857.84..31,858.02 rows=70 width=13) (actual time=298.183..298.298 rows=2,880 loops=1)

  • Sort Key: st.assetidval, st.status
  • Sort Method: quicksort Memory: 232kB
3. 35.932 316.140 ↓ 41.1 2,880 1

Gather (cost=9,335.40..31,855.70 rows=70 width=13) (actual time=22.556..316.140 rows=2,880 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 10.327 280.208 ↓ 33.1 960 3

Hash Join (cost=8,335.40..30,848.70 rows=29 width=13) (actual time=72.019..280.208 rows=960 loops=3)

  • Hash Cond: (st.apptblidval = it.id)
5. 7.635 208.278 ↓ 1.8 29,878 3

Hash Join (cost=33.12..22,485.33 rows=16,210 width=13) (actual time=10.339..208.278 rows=29,878 loops=3)

  • Hash Cond: (st.assetidval = vessels.id)
6. 200.452 200.452 ↑ 1.8 71,273 3

Parallel Index Scan using status_isvalid on status st (cost=0.56..22,123.62 rows=124,898 width=13) (actual time=10.061..200.452 rows=71,273 loops=3)

  • Index Cond: (isvalid = true)
  • Filter: (isvalid AND ((colname)::text = ANY ('{lastrepdate,lastinspdate}'::text[])) AND ((appname)::text = 'ISM PMS'::text) AND ((apptblname)::text = 'items'::text) AND ((assettable)::text = 'vessels'::text))
  • Rows Removed by Filter: 33444
7. 0.018 0.191 ↓ 1.0 140 3

Hash (cost=30.81..30.81 rows=139 width=4) (actual time=0.191..0.191 rows=140 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
8. 0.173 0.173 ↓ 1.0 140 3

Index Only Scan using vessels_id_vslstatus on vessels (cost=0.27..30.81 rows=139 width=4) (actual time=0.043..0.173 rows=140 loops=3)

  • Index Cond: (vslstatus = 'Active'::text)
  • Heap Fetches: 70
9. 5.466 61.603 ↓ 2.2 9,018 3

Hash (cost=8,251.68..8,251.68 rows=4,049 width=4) (actual time=61.603..61.603 rows=9,018 loops=3)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 446kB
10. 40.685 56.137 ↓ 2.2 9,018 3

Nested Loop (cost=35.93..8,251.68 rows=4,049 width=4) (actual time=1.103..56.137 rows=9,018 loops=3)

11. 15.035 15.433 ↓ 2.4 2,079 3

Bitmap Heap Scan on machdefs md (cost=35.50..2,767.90 rows=850 width=4) (actual time=1.083..15.433 rows=2,079 loops=3)

  • Recheck Cond: ((((pms_importance)::integer & 2) = 2) OR (application = 'Critical Equipment Performance Test'::text))
  • Filter: ((rhdue IS NULL) AND (((((pms_importance)::integer & 2) = 2) AND (application <> 'Spare Parts Short Inventory Report'::text)) OR (application = 'Critical Equipment Performance Test'::text)))
  • Rows Removed by Filter: 2658
  • Heap Blocks: exact=1635
12. 0.001 0.398 ↓ 0.0 0 3

BitmapOr (cost=35.50..35.50 rows=3,097 width=0) (actual time=0.398..0.398 rows=0 loops=3)

13. 0.293 0.293 ↓ 1.6 3,804 3

Bitmap Index Scan on machdefs_pms_importance_2 (cost=0.00..25.72 rows=2,346 width=0) (actual time=0.293..0.293 rows=3,804 loops=3)

  • Index Cond: (((pms_importance)::integer & 2) = 2)
14. 0.104 0.104 ↓ 1.3 940 3

Bitmap Index Scan on machdefs_application (cost=0.00..9.35 rows=751 width=0) (actual time=0.104..0.104 rows=940 loops=3)

  • Index Cond: (application = 'Critical Equipment Performance Test'::text)
15. 0.019 0.019 ↑ 2.8 4 6,237

Index Scan using items_defid on items it (cost=0.43..6.34 rows=11 width=8) (actual time=0.007..0.019 rows=4 loops=6,237)

  • Index Cond: (defid = md.defid)