explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kml

Settings
# exclusive inclusive rows x rows loops node
1. 55.271 1,452.932 ↑ 1.6 25,682 1

HashAggregate (cost=140,415.62..140,815.62 rows=40,000 width=36) (actual time=1,436.874..1,452.932 rows=25,682 loops=1)

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

CTE qr

3. 24.560 1,002.275 ↓ 126.9 25,376 1

Unique (cost=136,403.46..136,536.20 rows=200 width=98) (actual time=961.441..1,002.275 rows=25,376 loops=1)

4. 77.708 977.715 ↑ 1.0 26,462 1

Sort (cost=136,403.46..136,469.83 rows=26,548 width=98) (actual time=961.439..977.715 rows=26,462 loops=1)

  • Sort Key: qr1.pin, ((qr1.enabled_status_id = 0)) DESC
  • Sort Method: quicksort Memory: 4,501kB
5. 24.419 900.007 ↑ 1.0 26,462 1

Subquery Scan on qr1 (cost=133,988.08..134,452.67 rows=26,548 width=98) (actual time=810.066..900.007 rows=26,462 loops=1)

6. 37.505 875.588 ↑ 1.0 26,462 1

Unique (cost=133,988.08..134,120.82 rows=26,548 width=359) (actual time=810.053..875.588 rows=26,462 loops=1)

7. 159.141 838.083 ↓ 2.0 51,857 1

Sort (cost=133,988.08..134,054.45 rows=26,548 width=359) (actual time=810.050..838.083 rows=51,857 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: 8,943kB
8. 391.117 678.942 ↓ 2.0 51,857 1

GroupAggregate (cost=101,925.46..132,037.29 rows=26,548 width=359) (actual time=268.086..678.942 rows=51,857 loops=1)

  • Group Key: ((si."row" ->> 'mail'::text))
  • Sort Key: ((si."row" ->> 'pin'::text))
  • Group Key: ((si."row" ->> 'pin'::text))
9. 89.131 287.825 ↓ 2.0 26,549 1

Sort (cost=101,925.46..101,958.65 rows=13,274 width=359) (actual time=267.814..287.825 rows=26,549 loops=1)

  • Sort Key: ((si."row" ->> 'mail'::text))
  • Sort Method: quicksort Memory: 14,458kB
10. 48.890 198.694 ↓ 2.0 26,549 1

Nested Loop Anti Join (cost=0.37..101,016.44 rows=13,274 width=359) (actual time=0.036..198.694 rows=26,549 loops=1)

11. 17.059 17.059 ↑ 1.0 26,549 1

Seq Scan on staging_imports si (cost=0.00..1,679.86 rows=26,549 width=359) (actual time=0.007..17.059 rows=26,549 loops=1)

  • Filter: (source = 'sh.customers'::text)
12. 26.549 132.745 ↓ 0.0 0 26,549

Bitmap Heap Scan on customers_fids cf (cost=0.37..4.40 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=26,549)

  • 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. 53.098 106.196 ↓ 0.0 0 26,549

BitmapOr (cost=0.37..0.37 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=26,549)

14. 26.549 26.549 ↓ 0.0 0 26,549

Bitmap Index Scan on customers_fids_pkey (cost=0.00..0.18 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=26,549)

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

Bitmap Index Scan on customers_fids_pkey (cost=0.00..0.18 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=26,549)

  • Index Cond: ((fid = ((si."row" ->> 'shop_fid'::text))::integer) AND (data_source_id = 'ps_ilerna'::text))
16. 42.681 1,397.661 ↓ 1.3 51,362 1

Append (cost=1,082.96..3,579.43 rows=40,000 width=36) (actual time=991.757..1,397.661 rows=51,362 loops=1)

17. 43.314 1,184.945 ↓ 1.3 25,681 1

Nested Loop (cost=1,082.96..1,589.71 rows=20,000 width=36) (actual time=991.755..1,184.945 rows=25,681 loops=1)

18. 32.092 1,090.881 ↓ 126.9 25,375 1

Hash Join (cost=1,082.96..1,089.71 rows=200 width=36) (actual time=991.734..1,090.881 rows=25,375 loops=1)

  • Hash Cond: (qr.mail = (cu.mail)::text)
19. 1,028.661 1,028.661 ↓ 126.9 25,376 1

CTE Scan on qr (cost=0.00..4.00 rows=200 width=64) (actual time=961.444..1,028.661 rows=25,376 loops=1)

20. 15.688 30.128 ↑ 1.0 25,375 1

Hash (cost=765.76..765.76 rows=25,376 width=27) (actual time=30.128..30.128 rows=25,375 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,741kB
21. 14.440 14.440 ↑ 1.0 25,376 1

Seq Scan on customers cu (cost=0.00..765.76 rows=25,376 width=27) (actual time=0.012..14.440 rows=25,376 loops=1)

22. 50.750 50.750 ↑ 100.0 1 25,375

Function Scan on unnest fid (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.002 rows=1 loops=25,375)

23. 44.199 170.035 ↓ 1.3 25,681 1

Nested Loop (cost=1,082.96..1,589.71 rows=20,000 width=36) (actual time=29.719..170.035 rows=25,681 loops=1)

24. 30.174 75.086 ↓ 126.9 25,375 1

Hash Join (cost=1,082.96..1,089.71 rows=200 width=36) (actual time=29.700..75.086 rows=25,375 loops=1)

  • Hash Cond: (qr_1.pin = cu_1.pin)
25. 15.363 15.363 ↓ 126.9 25,376 1

CTE Scan on qr qr_1 (cost=0.00..4.00 rows=200 width=64) (actual time=0.002..15.363 rows=25,376 loops=1)

26. 14.985 29.549 ↑ 1.0 25,375 1

Hash (cost=765.76..765.76 rows=25,376 width=14) (actual time=29.549..29.549 rows=25,375 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,397kB
27. 14.564 14.564 ↑ 1.0 25,376 1

Seq Scan on customers cu_1 (cost=0.00..765.76 rows=25,376 width=14) (actual time=0.011..14.564 rows=25,376 loops=1)

28. 50.750 50.750 ↑ 100.0 1 25,375

Function Scan on unnest fid_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.002 rows=1 loops=25,375)

Planning time : 0.761 ms
Execution time : 1,464.039 ms