explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J2NO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 247.881 1,091.218 ↓ 133.1 26,622 1

GroupAggregate (cost=63,908,154.74..208,089,862.88 rows=200 width=82) (actual time=829.418..1,091.218 rows=26,622 loops=1)

  • Group Key: mcu.sid
2.          

CTE mcu

3. 92.583 762.943 ↑ 5,093.9 27,818 1

Recursive Union (cost=0.00..28,327,574.81 rows=141,701,922 width=363) (actual time=0.046..762.943 rows=27,818 loops=1)

4. 87.060 87.060 ↑ 1.0 26,622 1

Seq Scan on staging_imports si (cost=0.00..3,925.99 rows=26,622 width=363) (actual time=0.040..87.060 rows=26,622 loops=1)

  • Filter: (source = 'sh.customers'::text)
5. 121.700 583.300 ↑ 1,867.6 7,586 4

Nested Loop (cost=0.51..2,548,961.04 rows=14,167,530 width=363) (actual time=0.051..145.825 rows=7,586 loops=4)

6. 16.512 16.512 ↑ 38.3 6,954 4

WorkTable Scan on mcu mcu_1 (cost=0.00..5,324.40 rows=266,220 width=68) (actual time=0.001..4.128 rows=6,954 loops=4)

7. 139.090 445.088 ↑ 53.0 1 27,818

Bitmap Heap Scan on staging_imports si_1 (cost=0.51..7.30 rows=53 width=359) (actual time=0.015..0.016 rows=1 loops=27,818)

  • 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: (source = 'sh.customers'::text)
  • Heap Blocks: exact=30,299
8. 111.272 305.998 ↓ 0.0 0 27,818

BitmapOr (cost=0.51..0.51 rows=53 width=0) (actual time=0.011..0.011 rows=0 loops=27,818)

9. 111.272 111.272 ↑ 27.0 1 27,818

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

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

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=27,818)

  • Index Cond: ("row" @> ((('{"mail": '::text || (to_json(mcu_1.mail))::text) || '}'::text))::jsonb)
11. 38.429 843.337 ↑ 5,093.9 27,818 1

Sort (cost=35,580,579.94..35,934,834.74 rows=141,701,922 width=82) (actual time=829.376..843.337 rows=27,818 loops=1)

  • Sort Key: mcu.sid
  • Sort Method: quicksort Memory: 4,569kB
12. 804.908 804.908 ↑ 5,093.9 27,818 1

CTE Scan on mcu (cost=0.00..2,834,038.44 rows=141,701,922 width=82) (actual time=0.050..804.908 rows=27,818 loops=1)

Planning time : 0.271 ms
Execution time : 1,111.642 ms