explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eSAk

Settings
# exclusive inclusive rows x rows loops node
1. 53.366 1,514.787 ↑ 1.6 25,682 1

HashAggregate (cost=483,047.26..483,447.26 rows=40,000 width=36) (actual time=1,499.415..1,514.787 rows=25,682 loops=1)

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

CTE qr

3. 25.173 1,065.422 ↓ 126.9 25,376 1

Unique (cost=479,554.93..479,687.67 rows=200 width=98) (actual time=1,024.064..1,065.422 rows=25,376 loops=1)

4. 77.857 1,040.249 ↑ 1.0 26,462 1

Sort (cost=479,554.93..479,621.30 rows=26,548 width=98) (actual time=1,024.062..1,040.249 rows=26,462 loops=1)

  • Sort Key: qr1.pin, ((qr1.enabled_status_id = 0)) DESC
  • Sort Method: quicksort Memory: 4501kB
5. 24.573 962.392 ↑ 1.0 26,462 1

Subquery Scan on qr1 (cost=477,139.55..477,604.14 rows=26,548 width=98) (actual time=873.344..962.392 rows=26,462 loops=1)

6. 37.901 937.819 ↑ 1.0 26,462 1

Unique (cost=477,139.55..477,272.29 rows=26,548 width=359) (actual time=873.331..937.819 rows=26,462 loops=1)

7. 154.751 899.918 ↓ 2.0 51,857 1

Sort (cost=477,139.55..477,205.92 rows=26,548 width=359) (actual time=873.329..899.918 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: 8943kB
8. 386.360 745.167 ↓ 2.0 51,857 1

GroupAggregate (cost=445,076.94..475,188.76 rows=26,548 width=359) (actual time=339.585..745.167 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.483 358.807 ↓ 2.0 26,549 1

Sort (cost=445,076.94..445,110.12 rows=13,274 width=359) (actual time=339.291..358.807 rows=26,549 loops=1)

  • Sort Key: ((si."row" ->> 'mail'::text))
  • Sort Method: quicksort Memory: 14458kB
10. 30.383 269.324 ↓ 2.0 26,549 1

Seq Scan on staging_imports si (cost=0.00..444,167.92 rows=13,274 width=359) (actual time=0.080..269.324 rows=26,549 loops=1)

  • Filter: ((source = 'sh.customers'::text) AND (NOT (SubPlan 1)))
11.          

SubPlan (for Seq Scan)

12. 53.098 238.941 ↓ 0.0 0 26,549

Unique (cost=16.66..16.67 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=26,549)

13. 26.549 185.843 ↓ 0.0 0 26,549

Sort (cost=16.66..16.66 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=26,549)

  • Sort Key: (1)
  • Sort Method: quicksort Memory: 25kB
14. 53.098 159.294 ↓ 0.0 0 26,549

Append (cost=0.29..16.65 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=26,549)

15. 53.098 53.098 ↓ 0.0 0 26,549

Index Only Scan using customers_fids_pkey on customers_fids cf (cost=0.29..8.31 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=26,549)

  • Index Cond: ((fid = ((si."row" ->> 'sis_fid'::text))::integer) AND (data_source_id = 'sis'::text))
  • Heap Fetches: 25682
16. 53.098 53.098 ↓ 0.0 0 26,549

Index Only Scan using customers_fids_pkey on customers_fids cf_1 (cost=0.29..8.31 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=26,549)

  • Index Cond: ((fid = ((si."row" ->> 'shop_fid'::text))::integer) AND (data_source_id = 'ps_ilerna'::text))
  • Heap Fetches: 0
17. 43.670 1,461.421 ↓ 1.3 51,362 1

Append (cost=823.04..3,059.59 rows=40,000 width=36) (actual time=1,054.220..1,461.421 rows=51,362 loops=1)

18. 45.732 1,249.876 ↓ 1.3 25,681 1

Nested Loop (cost=823.04..1,329.79 rows=20,000 width=36) (actual time=1,054.219..1,249.876 rows=25,681 loops=1)

19. 31.251 1,153.394 ↓ 126.9 25,375 1

Hash Join (cost=823.04..829.79 rows=200 width=36) (actual time=1,054.194..1,153.394 rows=25,375 loops=1)

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

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

21. 15.804 30.091 ↓ 1.8 25,375 1

Hash (cost=650.24..650.24 rows=13,824 width=27) (actual time=30.091..30.091 rows=25,375 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1741kB
22. 14.287 14.287 ↓ 1.8 25,376 1

Seq Scan on customers cu (cost=0.00..650.24 rows=13,824 width=27) (actual time=0.013..14.287 rows=25,376 loops=1)

23. 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)

24. 44.852 167.875 ↓ 1.3 25,681 1

Nested Loop (cost=823.04..1,329.79 rows=20,000 width=36) (actual time=28.695..167.875 rows=25,681 loops=1)

25. 28.389 72.273 ↓ 126.9 25,375 1

Hash Join (cost=823.04..829.79 rows=200 width=36) (actual time=28.678..72.273 rows=25,375 loops=1)

  • Hash Cond: (qr_1.pin = cu_1.pin)
26. 15.234 15.234 ↓ 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.234 rows=25,376 loops=1)

27. 14.693 28.650 ↓ 1.8 25,375 1

Hash (cost=650.24..650.24 rows=13,824 width=14) (actual time=28.650..28.650 rows=25,375 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1397kB
28. 13.957 13.957 ↓ 1.8 25,376 1

Seq Scan on customers cu_1 (cost=0.00..650.24 rows=13,824 width=14) (actual time=0.011..13.957 rows=25,376 loops=1)

29. 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.745 ms
Execution time : 1,526.688 ms