explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6NVj : Optimization for: plan #J2NO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 53.798 990.998 ↓ 126.8 25,351 1

HashAggregate (cost=208,039,974.74..208,039,976.74 rows=200 width=106) (actual time=969.978..990.998 rows=25,351 loops=1)

  • Group Key: first(mcu.pin ORDER BY (mcu.active = 0) DESC, mcu.created_at DESC, mcu.sis_fid DESC)
2.          

CTE mcu

3. 72.697 643.821 ↑ 5,426.7 26,059 1

Recursive Union (cost=0.00..28,643,902.65 rows=141,414,675 width=367) (actual time=0.050..643.821 rows=26,059 loops=1)

4. 63.092 63.092 ↑ 1.0 25,907 1

Seq Scan on staging_imports si (cost=0.00..3,991.67 rows=26,595 width=363) (actual time=0.044..63.092 rows=25,907 loops=1)

  • Filter: ((NOT ("row" @> '{"enabled_status_id": "-1"}'::jsonb)) AND (source = 'sh.customers'::text))
  • Rows Removed by Filter: 715
5. 102.083 508.032 ↑ 1,072.6 13,182 2

Nested Loop (cost=0.51..2,581,161.75 rows=14,138,808 width=367) (actual time=0.066..254.016 rows=13,182 loops=2)

6. 15.064 15.064 ↑ 20.4 13,030 2

WorkTable Scan on mcu mcu_1 (cost=0.00..5,319.00 rows=265,950 width=68) (actual time=0.002..7.532 rows=13,030 loops=2)

7. 130.295 390.885 ↑ 53.0 1 26,059

Bitmap Heap Scan on staging_imports si_1 (cost=0.51..7.43 rows=53 width=363) (actual time=0.014..0.015 rows=1 loops=26,059)

  • Recheck Cond: (("row" @> ((('{"pin": '::text || (to_json(mcu_1.pin))::text) || '}'::text))::jsonb) OR ("row" @> ((('{"mail": '::text || (to_json(mcu_1.mail))::text) || '}'::text))::jsonb))
  • Rows Removed by Index Recheck: 0
  • Filter: ((NOT ("row" @> '{"enabled_status_id": "-1"}'::jsonb)) AND (source = 'sh.customers'::text))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=26848
8. 104.236 260.590 ↓ 0.0 0 26,059

BitmapOr (cost=0.51..0.51 rows=53 width=0) (actual time=0.010..0.010 rows=0 loops=26,059)

9. 78.177 78.177 ↑ 27.0 1 26,059

Bitmap Index Scan on staging_imports_row_idx (cost=0.00..0.24 rows=27 width=0) (actual time=0.003..0.003 rows=1 loops=26,059)

  • Index Cond: ("row" @> ((('{"pin": '::text || (to_json(mcu_1.pin))::text) || '}'::text))::jsonb)
10. 78.177 78.177 ↑ 27.0 1 26,059

Bitmap Index Scan on staging_imports_row_idx (cost=0.00..0.24 rows=27 width=0) (actual time=0.003..0.003 rows=1 loops=26,059)

  • Index Cond: ("row" @> ((('{"mail": '::text || (to_json(mcu_1.mail))::text) || '}'::text))::jsonb)
11. 227.693 937.200 ↓ 129.5 25,907 1

GroupAggregate (cost=35,506,385.27..179,395,819.58 rows=200 width=82) (actual time=695.952..937.200 rows=25,907 loops=1)

  • Group Key: mcu.sid
12. 32.781 709.507 ↑ 5,426.7 26,059 1

Sort (cost=35,506,385.27..35,859,921.96 rows=141,414,675 width=82) (actual time=695.910..709.507 rows=26,059 loops=1)

  • Sort Key: mcu.sid
  • Sort Method: quicksort Memory: 4329kB
13. 676.726 676.726 ↑ 5,426.7 26,059 1

CTE Scan on mcu (cost=0.00..2,828,293.50 rows=141,414,675 width=82) (actual time=0.052..676.726 rows=26,059 loops=1)

Planning time : 0.344 ms
Execution time : 1,011.585 ms