explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jw5x

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 21,103.078 ↑ 1.0 1 1

Aggregate (cost=801,124.51..801,124.52 rows=1 width=0) (actual time=21,103.077..21,103.078 rows=1 loops=1)

  • Buffers: shared hit=254046 read=368886 dirtied=17 written=485
2. 0.272 21,103.072 ↓ 0.0 0 1

Nested Loop (cost=775,532.11..801,124.39 rows=45 width=0) (actual time=21,103.072..21,103.072 rows=0 loops=1)

  • Buffers: shared hit=254046 read=368886 dirtied=17 written=485
3. 0.248 21,081.520 ↑ 2.9 70 1

HashAggregate (cost=775,531.55..775,533.55 rows=200 width=11) (actual time=21,081.438..21,081.520 rows=70 loops=1)

  • Group Key: ("ANY_subquery".att_value_8)::text
  • Buffers: shared hit=253439 read=368395 dirtied=17 written=485
4. 0.167 21,081.272 ↑ 314.0 70 1

Subquery Scan on ANY_subquery (cost=774,982.02..775,476.60 rows=21,981 width=11) (actual time=20,959.723..21,081.272 rows=70 loops=1)

  • Buffers: shared hit=253439 read=368395 dirtied=17 written=485
5. 1,184.936 21,081.105 ↑ 314.0 70 1

HashAggregate (cost=774,982.02..775,256.79 rows=21,981 width=13) (actual time=20,959.706..21,081.105 rows=70 loops=1)

  • Group Key: bus_ent_instance_1.att_value_8, bus_ent_instance_1.att_value_10
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 391199
  • Buffers: shared hit=253439 read=368395 dirtied=17 written=485
6. 18,791.594 19,896.169 ↑ 4.8 391,343 1

Bitmap Heap Scan on bus_ent_instance bus_ent_instance_1 (cost=88,780.27..760,785.85 rows=1,892,823 width=13) (actual time=1,396.494..19,896.169 rows=391,343 loops=1)

  • Recheck Cond: (((bus_ent_inst_name_pre)::text = 'NC'::text) AND (reg_status = 0))
  • Filter: ((ent_sta_id <> 1042) AND (ent_sta_id <> 1038))
  • Rows Removed by Filter: 1575025
  • Heap Blocks: exact=596992
  • Buffers: shared hit=253439 read=368395 dirtied=17 written=485
7. 1,104.575 1,104.575 ↑ 1.0 1,983,723 1

Bitmap Index Scan on ix_bus_ent_instance_attn_namenum_01 (cost=0.00..88,307.07 rows=1,984,579 width=0) (actual time=1,104.575..1,104.575 rows=1,983,723 loops=1)

  • Index Cond: (((bus_ent_inst_name_pre)::text = 'NC'::text) AND (reg_status = 0))
  • Buffers: shared hit=25 read=24817
8. 21.280 21.280 ↓ 0.0 0 70

Index Scan using ix_bus_ent_instance_atts_namenum_08 on bus_ent_instance (cost=0.56..127.94 rows=1 width=11) (actual time=0.304..0.304 rows=0 loops=70)

  • Index Cond: (((bus_ent_inst_name_pre)::text = 'NC'::text) AND ((att_value_8)::text = ("ANY_subquery".att_value_8)::text) AND (reg_status = 0))
  • Filter: ((ent_sta_id <> 1042) AND (ent_sta_id <> 1038) AND (bus_ent_inst_create_data > (now() - '24:00:00'::interval)))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=607 read=491