explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 53n : old plan

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 264.252 ↓ 0.0 0 1

Update on spec_households (cost=20,980.19..22,535,646.81 rows=1,797,594,133 width=514) (actual time=264.252..264.252 rows=0 loops=1)

2. 85.667 264.250 ↓ 0.0 0 1

Nested Loop (cost=20,980.19..22,535,646.81 rows=1,797,594,133 width=514) (actual time=264.250..264.250 rows=0 loops=1)

3. 178.583 178.583 ↑ 1.0 656,575 1

Seq Scan on spec_households (cost=0.00..18,271.47 rows=657,247 width=418) (actual time=0.092..178.583 rows=656,575 loops=1)

4. 0.000 0.000 ↓ 0.0 0 656,575

Materialize (cost=20,980.19..47,750.36 rows=2,735 width=18) (actual time=0.000..0.000 rows=0 loops=656,575)

5. 0.030 0.262 ↓ 0.0 0 1

Hash Join (cost=20,980.19..47,736.69 rows=2,735 width=18) (actual time=0.262..0.262 rows=0 loops=1)

  • Hash Cond: (c.spec_households_id = b.id)
6. 0.087 0.087 ↑ 718,533.0 1 1

Seq Scan on spec_household_panels c (cost=0.00..24,034.65 rows=718,533 width=14) (actual time=0.087..0.087 rows=1 loops=1)

  • Filter: ((household_status_acronym)::text <> ALL ('{MNI,IPQ,PRD,QFM,SUS,OOS}'::text[]))
  • Rows Removed by Filter: 1
7. 0.000 0.145 ↓ 0.0 0 1

Hash (cost=20,948.91..20,948.91 rows=2,502 width=20) (actual time=0.145..0.145 rows=0 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
8. 0.009 0.145 ↓ 0.0 0 1

Hash Join (cost=187.75..20,948.91 rows=2,502 width=20) (actual time=0.145..0.145 rows=0 loops=1)

  • Hash Cond: (b.specs_id = a.id)
9. 0.038 0.038 ↑ 657,247.0 1 1

Seq Scan on spec_households b (cost=0.00..18,271.47 rows=657,247 width=22) (actual time=0.038..0.038 rows=1 loops=1)

10. 0.000 0.098 ↓ 0.0 0 1

Hash (cost=187.31..187.31 rows=35 width=14) (actual time=0.098..0.098 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.004 0.098 ↓ 0.0 0 1

Bitmap Heap Scan on specs a (cost=98.25..187.31 rows=35 width=14) (actual time=0.098..0.098 rows=0 loops=1)

  • Recheck Cond: ((spec_id)::text = ANY ('{1178930,1178931,1178932,1178933,1178935,1178936,1178938,1178940,1178942,1178944,1178945,1178948,1178962,1178964,1178965,1178966,1178967,1178968,1178969,1178970,1178971,1178972,1178973,1178974,1178975,1178976,1178977,1178978,1178979,1178980,1178981,1178982,1178983,1178984,1178985}'::text[]))
12. 0.094 0.094 ↓ 0.0 0 1

Bitmap Index Scan on specs_spec_id_key (cost=0.00..98.24 rows=35 width=0) (actual time=0.094..0.094 rows=0 loops=1)

  • Index Cond: ((spec_id)::text = ANY ('{1178930,1178931,1178932,1178933,1178935,1178936,1178938,1178940,1178942,1178944,1178945,1178948,1178962,1178964,1178965,1178966,1178967,1178968,1178969,1178970,1178971,1178972,1178973,1178974,1178975,1178976,1178977,1178978,1178979,1178980,1178981,1178982,1178983,1178984,1178985}'::text[]))