explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zKr

Settings
# exclusive inclusive rows x rows loops node
1. 2.580 423,370.807 ↓ 1.2 605 1

GroupAggregate (cost=5,922,441.44..5,922,715.95 rows=506 width=63) (actual time=423,349.656..423,370.807 rows=605 loops=1)

  • Group Key: changes.member_id, ma.email
2.          

CTE changes

3. 0.726 421,762.277 ↑ 54.6 605 1

Unique (cost=5,817,382.00..5,817,547.19 rows=33,037 width=25) (actual time=421,760.734..421,762.277 rows=605 loops=1)

4. 2.828 421,761.551 ↑ 45.6 724 1

Sort (cost=5,817,382.00..5,817,464.59 rows=33,037 width=25) (actual time=421,760.732..421,761.551 rows=724 loops=1)

  • Sort Key: ma_1.sqsp_id
  • Sort Method: quicksort Memory: 81kB
5. 41.249 421,758.723 ↑ 45.6 724 1

Gather (cost=1,001.12..5,814,902.28 rows=33,037 width=25) (actual time=48,050.664..421,758.723 rows=724 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
6. 276,003.228 421,717.474 ↑ 51.6 80 9

Nested Loop (cost=1.12..5,810,598.58 rows=4,130 width=25) (actual time=49,252.996..421,717.474 rows=80 loops=9)

  • Join Filter: ((GREATEST(ma_1.dw_changed_date, w_1.dw_changed_date, mp_1.dw_changed_date) >= '2019-05-13 20:00:00'::timestamp without time zone) AND (GREATEST(ma_1.dw_changed_date, w_1.dw_changed_date, mp_1.dw_changed_date) <= '2019-05-13 20:01:00'::timestamp without time zone))
  • Rows Removed by Join Filter: 314170
7. 135,609.876 145,714.069 ↓ 2.4 1,552,184 9

Nested Loop (cost=0.56..4,918,952.57 rows=659,356 width=66) (actual time=0.677..145,714.069 rows=1,552,184 loops=9)

8. 10,104.132 10,104.132 ↑ 1.2 2,186,941 9

Parallel Seq Scan on member_permissions mp_1 (cost=0.00..2,369,414.52 rows=2,572,161 width=58) (actual time=0.067..10,104.132 rows=2,186,941 loops=9)

  • Filter: (dw_valid_to_date IS NULL)
  • Rows Removed by Filter: 684161
9. 0.061 0.061 ↑ 1.0 1 19,682,472

Index Scan using i_sqsp_member_accounts_uniqa on member_accounts ma_1 (cost=0.56..0.98 rows=1 width=33) (actual time=0.060..0.061 rows=1 loops=19,682,472)

  • Index Cond: ((sqsp_id)::text = (mp_1.member_account_id)::text)
  • Filter: ((last_login_on >= '2016-11-01 00:00:00+00'::timestamp with time zone) AND (((email)::text = ''::text) IS NOT TRUE) AND (((sqsp_data ->> 'inUniversalHoldoutGroup'::text))::boolean IS NOT TRUE))
  • Rows Removed by Filter: 0
10. 0.177 0.177 ↓ 0.0 0 13,969,658

Index Scan using i_sqsp_websites_uniqa on websites w_1 (cost=0.56..1.34 rows=1 width=33) (actual time=0.165..0.177 rows=0 loops=13,969,658)

  • Index Cond: ((sqsp_id)::text = (mp_1.website_id)::text)
  • Filter: (status = ANY ('{2,8}'::integer[]))
  • Rows Removed by Filter: 1
11. 8.327 423,350.223 ↓ 5.1 2,572 1

Sort (cost=104,894.26..104,895.52 rows=506 width=516) (actual time=423,349.458..423,350.223 rows=2,572 loops=1)

  • Sort Key: changes.member_id, ma.email
  • Sort Method: quicksort Memory: 2676kB
12. 6.708 423,341.896 ↓ 5.1 2,572 1

Nested Loop (cost=1.69..104,871.53 rows=506 width=516) (actual time=421,761.387..423,341.896 rows=2,572 loops=1)

13. 3.953 422,829.380 ↑ 1.8 5,008 1

Nested Loop (cost=1.12..92,780.87 rows=8,974 width=541) (actual time=421,761.312..422,829.380 rows=5,008 loops=1)

14. 1.336 421,784.222 ↑ 10.8 605 1

Nested Loop (cost=0.56..84,705.16 rows=6,513 width=80) (actual time=421,760.828..421,784.222 rows=605 loops=1)

15. 421,762.921 421,762.921 ↑ 54.6 605 1

CTE Scan on changes (cost=0.00..660.74 rows=33,037 width=32) (actual time=421,760.740..421,762.921 rows=605 loops=1)

16. 19.965 19.965 ↑ 1.0 1 605

Index Scan using i_sqsp_member_accounts_uniqa on member_accounts ma (cost=0.56..2.53 rows=1 width=48) (actual time=0.031..0.033 rows=1 loops=605)

  • Index Cond: ((sqsp_id)::text = (changes.member_id)::text)
  • Filter: ((last_login_on >= '2016-11-01 00:00:00+00'::timestamp with time zone) AND (((email)::text = ''::text) IS NOT TRUE))
17. 1,041.205 1,041.205 ↑ 1.2 8 605

Index Scan using member_permissions__member_account_id on member_permissions mp (cost=0.56..1.14 rows=10 width=511) (actual time=0.446..1.721 rows=8 loops=605)

  • Index Cond: ((member_account_id)::text = (ma.sqsp_id)::text)
  • Filter: (dw_valid_to_date IS NULL)
  • Rows Removed by Filter: 8
18. 505.808 505.808 ↑ 1.0 1 5,008

Index Scan using i_sqsp_websites_uniqa on websites w (cost=0.56..1.34 rows=1 width=25) (actual time=0.092..0.101 rows=1 loops=5,008)

  • Index Cond: ((sqsp_id)::text = (mp.website_id)::text)
  • Filter: (status = ANY ('{2,8}'::integer[]))
  • Rows Removed by Filter: 0
19.          

SubPlan (forGroupAggregate)

20. 18.004 18.004 ↑ 50.0 2 2,572

Result (cost=0.00..0.52 rows=100 width=4) (actual time=0.006..0.007 rows=2 loops=2,572)

Planning time : 53.471 ms
Execution time : 424,728.642 ms