explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IwAw : Optimization for: Optimization for: Optimization for: plan #3zH; plan #GOl; plan #JJa6

Settings

Optimization path:

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

HashAggregate (cost=1,707.81..1,711.81 rows=400 width=36) (actual time=0.038..0.038 rows=0 loops=1)

  • Group Key: ((fid.fid)::integer), cu.id, ('sis'::text)
2.          

CTE qr

3. 0.001 0.017 ↓ 0.0 0 1

Unique (cost=813.13..813.14 rows=2 width=98) (actual time=0.017..0.017 rows=0 loops=1)

4. 0.005 0.016 ↓ 0.0 0 1

Sort (cost=813.13..813.14 rows=2 width=98) (actual time=0.016..0.016 rows=0 loops=1)

  • Sort Key: qr1.pin, ((qr1.enabled_status_id = 0)) DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.000 0.011 ↓ 0.0 0 1

Subquery Scan on qr1 (cost=813.09..813.12 rows=2 width=98) (actual time=0.011..0.011 rows=0 loops=1)

6. 0.002 0.011 ↓ 0.0 0 1

Unique (cost=813.09..813.10 rows=2 width=373) (actual time=0.011..0.011 rows=0 loops=1)

7. 0.002 0.009 ↓ 0.0 0 1

Sort (cost=813.09..813.09 rows=2 width=373) (actual time=0.009..0.009 rows=0 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: 25kB
8. 0.002 0.007 ↓ 0.0 0 1

GroupAggregate (cost=810.86..813.08 rows=2 width=373) (actual time=0.007..0.007 rows=0 loops=1)

  • Group Key: ((si."row" ->> 'mail'::text))
  • Sort Key: ((si."row" ->> 'pin'::text))
  • Group Key: ((si."row" ->> 'pin'::text))
9. 0.003 0.005 ↓ 0.0 0 1

Sort (cost=810.86..810.87 rows=1 width=373) (actual time=0.005..0.005 rows=0 loops=1)

  • Sort Key: ((si."row" ->> 'mail'::text))
  • Sort Method: quicksort Memory: 25kB
10. 0.001 0.002 ↓ 0.0 0 1

Nested Loop Anti Join (cost=6.32..810.85 rows=1 width=373) (actual time=0.002..0.002 rows=0 loops=1)

11. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on staging_imports si (cost=0.00..12.38 rows=77 width=373) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (source = 'sh.customers'::text)
12. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on customers_fids cf (cost=6.32..10.36 rows=1 width=8) (never executed)

  • Recheck Cond: (((fid = ((si."row" ->> 'sis_fid'::text))::integer) AND (data_source_id = 'sis'::text)) OR ((fid = ((si."row" ->> 'shop_fid'::text))::integer) AND (data_source_id = 'ps_ilerna'::text)))
13. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=6.32..6.32 rows=1 width=0) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on customers_fids_pkey (cost=0.00..3.16 rows=1 width=0) (never executed)

  • Index Cond: ((fid = ((si."row" ->> 'sis_fid'::text))::integer) AND (data_source_id = 'sis'::text))
15. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on customers_fids_pkey (cost=0.00..3.16 rows=1 width=0) (never executed)

  • Index Cond: ((fid = ((si."row" ->> 'shop_fid'::text))::integer) AND (data_source_id = 'ps_ilerna'::text))
16. 0.003 0.036 ↓ 0.0 0 1

Append (cost=0.07..891.67 rows=400 width=36) (actual time=0.036..0.036 rows=0 loops=1)

17. 0.000 0.031 ↓ 0.0 0 1

Nested Loop (cost=0.07..865.99 rows=200 width=36) (actual time=0.031..0.031 rows=0 loops=1)

18. 0.006 0.031 ↓ 0.0 0 1

Hash Join (cost=0.07..860.99 rows=2 width=36) (actual time=0.031..0.031 rows=0 loops=1)

  • Hash Cond: ((cu.mail)::text = qr.mail)
19. 0.006 0.006 ↑ 25,375.0 1 1

Seq Scan on customers cu (cost=0.00..765.75 rows=25,375 width=27) (actual time=0.006..0.006 rows=1 loops=1)

20. 0.000 0.019 ↓ 0.0 0 1

Hash (cost=0.04..0.04 rows=2 width=64) (actual time=0.019..0.019 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
21. 0.019 0.019 ↓ 0.0 0 1

CTE Scan on qr (cost=0.00..0.04 rows=2 width=64) (actual time=0.019..0.019 rows=0 loops=1)

22. 0.000 0.000 ↓ 0.0 0

Function Scan on unnest fid (cost=0.00..1.00 rows=100 width=32) (never executed)

23. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.29..21.67 rows=200 width=36) (actual time=0.002..0.002 rows=0 loops=1)

24. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.29..16.67 rows=2 width=36) (actual time=0.002..0.002 rows=0 loops=1)

25. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on qr qr_1 (cost=0.00..0.04 rows=2 width=64) (actual time=0.001..0.001 rows=0 loops=1)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using customers_pin_idx on customers cu_1 (cost=0.29..8.30 rows=1 width=14) (never executed)

  • Index Cond: (pin = qr_1.pin)
27. 0.000 0.000 ↓ 0.0 0

Function Scan on unnest fid_1 (cost=0.00..1.00 rows=100 width=32) (never executed)

Planning time : 0.763 ms
Execution time : 0.152 ms