explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SBSh

Settings
# exclusive inclusive rows x rows loops node
1. 25.536 623,939.580 ↑ 1.0 1 1

Aggregate (cost=2,996,259.54..2,996,259.55 rows=1 width=0) (actual time=623,939.579..623,939.580 rows=1 loops=1)

2. 350,655.857 623,914.044 ↓ 63.4 25,366 1

Nested Loop (cost=2,906,445.42..2,996,258.54 rows=400 width=0) (actual time=800.730..623,914.044 rows=25,366 loops=1)

  • Join Filter: ((cu.pin = qr2.pin) OR ((cu.mail)::text = qr2.mail))
  • Rows Removed by Join Filter: 643408590
3. 41.001 41.001 ↑ 1.0 25,366 1

Seq Scan on customers cu (cost=0.00..765.66 rows=25,366 width=33) (actual time=0.006..41.001 rows=25,366 loops=1)

4. 272,355.362 273,217.186 ↓ 126.8 25,366 25,366

Materialize (cost=2,906,445.42..2,906,712.38 rows=200 width=64) (actual time=0.032..10.771 rows=25,366 loops=25,366)

5. 22.373 861.824 ↓ 126.8 25,366 1

Subquery Scan on qr2 (cost=2,906,445.42..2,906,711.38 rows=200 width=64) (actual time=800.706..861.824 rows=25,366 loops=1)

6. 24.188 839.451 ↓ 126.8 25,366 1

Unique (cost=2,906,445.42..2,906,709.38 rows=200 width=66) (actual time=800.705..839.451 rows=25,366 loops=1)

7. 72.735 815.263 ↑ 2.0 26,452 1

Sort (cost=2,906,445.42..2,906,577.40 rows=52,792 width=66) (actual time=800.702..815.263 rows=26,452 loops=1)

  • Sort Key: qr1.pin, ((qr1.enabled_status_id = 0)) DESC
  • Sort Method: quicksort Memory: 2843kB
8. 24.883 742.528 ↑ 2.0 26,452 1

Subquery Scan on qr1 (cost=2,901,380.54..2,902,304.40 rows=52,792 width=66) (actual time=653.765..742.528 rows=26,452 loops=1)

9. 36.986 717.645 ↑ 2.0 26,452 1

Unique (cost=2,901,380.54..2,901,644.50 rows=52,792 width=373) (actual time=653.751..717.645 rows=26,452 loops=1)

10. 153.266 680.659 ↑ 1.0 51,838 1

Sort (cost=2,901,380.54..2,901,512.52 rows=52,792 width=373) (actual time=653.749..680.659 rows=51,838 loops=1)

  • Sort Key: (NULLIF(first(((si."row" ->> 'mail'::text)) ORDER BY (si."row" @> '{"enabled_status_id": "0"}'::jsonb) DESC), ''::text)), ((first((si."row" ->> 'enabled_status_id'::text) ORDER BY (si."row" @> '{"enabled_status_id": "0"}'::jsonb) DESC))::smallint) DESC
  • Sort Method: quicksort Memory: 7836kB
11. 357.041 527.393 ↑ 1.0 51,838 1

GroupAggregate (cost=2,837,757.73..2,897,239.53 rows=52,792 width=373) (actual time=151.015..527.393 rows=51,838 loops=1)

  • Group Key: ((si."row" ->> 'mail'::text))
  • Sort Key: ((si."row" ->> 'pin'::text))
  • Group Key: ((si."row" ->> 'pin'::text))
12. 87.479 170.352 ↓ 1.0 26,538 1

Sort (cost=2,837,757.73..2,837,823.72 rows=26,396 width=373) (actual time=150.752..170.352 rows=26,538 loops=1)

  • Sort Key: ((si."row" ->> 'mail'::text))
  • Sort Method: quicksort Memory: 14452kB
13. 55.289 82.873 ↓ 1.0 26,538 1

Nested Loop Anti Join (cost=55.90..2,835,819.20 rows=26,396 width=373) (actual time=11.239..82.873 rows=26,538 loops=1)

  • Join Filter: (((cf.data_source_id = 'sis'::text) AND (si."row" @> ((('{"sis_fid": "'::text || (cf.fid)::text) || '"}'::text))::jsonb)) OR ((cf.data_source_id = 'ps_ilerna'::text) AND (si."row" @> ((('{"shop_fid": "'::text || (cf.fid)::text) || '"}'::text))::jsonb)))
14. 27.584 27.584 ↓ 1.0 26,538 1

Seq Scan on staging_imports si (cost=0.00..7,725.61 rows=26,422 width=373) (actual time=11.194..27.584 rows=26,538 loops=1)

  • Filter: (source = 'sh.customers'::text)
  • Rows Removed by Filter: 39511
15. 0.000 0.000 ↓ 0.0 0 26,538

Materialize (cost=55.90..947.28 rows=2,040 width=8) (actual time=0.000..0.000 rows=0 loops=26,538)

16. 0.018 0.024 ↓ 0.0 0 1

Hash Join (cost=55.90..937.08 rows=2,040 width=8) (actual time=0.024..0.024 rows=0 loops=1)

  • Hash Cond: (cuc.id = cf.customer_id)
17. 0.004 0.004 ↑ 25,366.0 1 1

Seq Scan on customers cuc (cost=0.00..765.66 rows=25,366 width=4) (actual time=0.004..0.004 rows=1 loops=1)

18. 0.002 0.002 ↓ 0.0 0 1

Hash (cost=30.40..30.40 rows=2,040 width=12) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
19. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on customers_fids cf (cost=0.00..30.40 rows=2,040 width=12) (actual time=0.000..0.000 rows=0 loops=1)

Planning time : 0.604 ms
Execution time : 623,941.302 ms