explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uxi5

Settings
# exclusive inclusive rows x rows loops node
1. 0.084 237.949 ↑ 1.0 1 1

Result (cost=17,352,825.41..17,352,825.67 rows=1 width=32) (actual time=237.949..237.949 rows=1 loops=1)

2.          

CTE sal_base

3. 11.744 13.413 ↑ 1.5 14,337 1

Bitmap Heap Scan on sal_all (cost=267.23..23,191.83 rows=21,209 width=100) (actual time=3.198..13.413 rows=14,337 loops=1)

  • Recheck Cond: (org_domain = 'google.com'::text)
  • Heap Blocks: exact=12,283
4. 1.669 1.669 ↑ 1.5 14,337 1

Bitmap Index Scan on sal_all_org_domain_idx (cost=0.00..261.93 rows=21,209 width=0) (actual time=1.669..1.669 rows=14,337 loops=1)

  • Index Cond: (org_domain = 'google.com'::text)
5.          

CTE phone_base

6. 1.531 168.142 ↑ 1,207.1 2,741 1

GroupAggregate (cost=851,877.89..958,831.99 rows=3,308,651 width=85) (actual time=166.468..168.142 rows=2,741 loops=1)

  • Group Key: dir.member_lipid
7. 5.905 166.611 ↑ 2,010.0 2,852 1

Sort (cost=851,877.89..866,208.97 rows=5,732,434 width=41) (actual time=166.457..166.611 rows=2,852 loops=1)

  • Sort Key: dir.member_lipid
  • Sort Method: quicksort Memory: 320kB
8. 3.683 160.706 ↑ 2,010.0 2,852 1

Nested Loop (cost=477.76..1,821.34 rows=5,732,434 width=41) (actual time=4.929..160.706 rows=2,852 loops=1)

9. 5.310 6.651 ↓ 41.8 8,354 1

HashAggregate (cost=477.20..479.20 rows=200 width=32) (actual time=4.872..6.651 rows=8,354 loops=1)

  • Group Key: sal_base.member_lipid
10. 1.341 1.341 ↑ 1.5 14,337 1

CTE Scan on sal_base (cost=0.00..424.18 rows=21,209 width=32) (actual time=0.001..1.341 rows=14,337 loops=1)

11. 150.372 150.372 ↓ 0.0 0 8,354

Index Scan using dir_member_lipid_phone_type_idx on dir (cost=0.56..6.68 rows=3 width=41) (actual time=0.016..0.018 rows=0 loops=8,354)

  • Index Cond: ((member_lipid = sal_base.member_lipid) AND (phone_type = ANY ('{cell,desk}'::text[])))
12.          

CTE base

13. 6.710 228.532 ↑ 58,138.5 6,035 1

Merge Join (cost=573,456.47..5,844,822.57 rows=350,865,895 width=257) (actual time=219.327..228.532 rows=6,035 loops=1)

  • Merge Cond: (sal_base_1.member_lipid = phone_base.member_lipid)
14. 33.983 50.856 ↑ 1.5 14,300 1

Sort (cost=3,266.45..3,319.47 rows=21,209 width=224) (actual time=49.045..50.856 rows=14,300 loops=1)

  • Sort Key: sal_base_1.member_lipid
  • Sort Method: quicksort Memory: 2,869kB
15. 16.873 16.873 ↑ 1.5 14,337 1

CTE Scan on sal_base sal_base_1 (cost=0.00..424.18 rows=21,209 width=224) (actual time=3.199..16.873 rows=14,337 loops=1)

16. 0.532 170.966 ↑ 548.2 6,035 1

Materialize (cost=570,190.02..586,733.28 rows=3,308,651 width=64) (actual time=170.273..170.966 rows=6,035 loops=1)

17. 1.271 170.434 ↑ 1,207.1 2,741 1

Sort (cost=570,190.02..578,461.65 rows=3,308,651 width=64) (actual time=170.269..170.434 rows=2,741 loops=1)

  • Sort Key: phone_base.member_lipid
  • Sort Method: quicksort Memory: 388kB
18. 169.163 169.163 ↑ 1,207.1 2,741 1

CTE Scan on phone_base (cost=0.00..66,173.02 rows=3,308,651 width=64) (actual time=166.471..169.163 rows=2,741 loops=1)

19.          

CTE phone_counts

20. 5.681 237.390 ↑ 1.0 1 1

Aggregate (cost=10,525,976.85..10,525,976.86 rows=1 width=16) (actual time=237.390..237.390 rows=1 loops=1)

21. 231.709 231.709 ↑ 58,138.5 6,035 1

CTE Scan on base (cost=0.00..7,017,317.90 rows=350,865,895 width=33) (actual time=219.328..231.709 rows=6,035 loops=1)

22.          

CTE unpurchased_records

23. 0.003 0.011 ↑ 1.0 25 1

Limit (cost=0.00..1.00 rows=25 width=288) (actual time=0.003..0.011 rows=25 loops=1)

24. 0.008 0.008 ↑ 7,017,317.9 25 1

CTE Scan on base base_1 (cost=0.00..7,017,317.90 rows=175,432,948 width=288) (actual time=0.002..0.008 rows=25 loops=1)

  • Filter: (NOT is_purchased)
25.          

Initplan (for Result)

26. 237.446 237.446 ↑ 1.0 1 1

CTE Scan on phone_counts (cost=0.00..0.27 rows=1 width=32) (actual time=237.445..237.446 rows=1 loops=1)

27. 0.377 0.419 ↑ 1.0 1 1

Aggregate (cost=0.63..0.89 rows=1 width=32) (actual time=0.419..0.419 rows=1 loops=1)

28. 0.042 0.042 ↑ 1.0 25 1

CTE Scan on unpurchased_records (cost=0.00..0.50 rows=25 width=24) (actual time=0.010..0.042 rows=25 loops=1)

Planning time : 0.334 ms
Execution time : 238.932 ms