explain.depesz.com

PostgreSQL's explain analyze made readable

Result: frCx

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 8,825.059 ↓ 0.0 0 1

Nested Loop (cost=28,835.66..29,224.98 rows=100 width=1,425) (actual time=8,825.059..8,825.059 rows=0 loops=1)

2.          

CTE valid_organization_services

3. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_services_pkey on organization_services os (cost=0.08..3.89 rows=1 width=1,425) (never executed)

4. 0.002 8,825.059 ↓ 0.0 0 1

CTE Scan on valid_organization_services vos (cost=0.00..0.60 rows=100 width=4) (actual time=8,825.059..8,825.059 rows=0 loops=1)

  • Index Cond: (id = vos.id)
5. 0.000 8,825.057 ↓ 0.0 0 1

Limit (cost=12,663.83..28,835.57 rows=100 width=4) (actual time=8,825.057..8,825.057 rows=0 loops=1)

6. 0.001 8,825.057 ↓ 0.0 0 1

Group (cost=12,663.83..95,301.46 rows=511 width=4) (actual time=8,825.057..8,825.057 rows=0 loops=1)

  • Group Key: os_1.id
7. 5,427.214 8,825.056 ↓ 0.0 0 1

Nested Loop (cost=12,663.83..95,301.20 rows=511 width=4) (actual time=8,825.056..8,825.056 rows=0 loops=1)

  • Rows Removed by Join Filter: 72343188
  • Join Filter: (os_1.organization_id = active_cals.id)
8. 215.217 215.217 ↓ 8.3 8,487 1

Nested Loop (cost=0.14..32,998.09 rows=1,018 width=8) (actual time=0.042..215.217 rows=8,487 loops=1)

9. 2,901.373 3,182.625 ↑ 1.4 8,524 8,487

Materialize (cost=12,663.69..12,748.88 rows=12,171 width=4) (actual time=0.008..0.375 rows=8,524 loops=8,487)

10. 59.536 59.536 ↑ 1.0 1 14,884

Index Scan using services_index_organization_service_id_calendar_id on services s (cost=0.06..1.79 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=14,884)

11. 153.989 153.989 ↓ 3.4 14,884 1

Index Scan using organization_services_pkey on organization_services os_1 (cost=0.08..25,208.55 rows=4,354 width=8) (actual time=0.031..153.989 rows=14,884 loops=1)

12. 67.727 67.727 ↑ 1.4 8,524 1

HashAggregate (cost=12,663.69..12,700.20 rows=12,171 width=4) (actual time=66.420..67.727 rows=8,524 loops=1)

  • Rows Removed by Filter: 112792
  • Rows Removed by Filter: 0
  • Index Cond: (organization_service_id = os_1.id)
  • Group Key: active_cals.id
  • Filter: ((last_synced IS NOT NULL) AND (requires_reauthentication IS NOT TRUE) AND (last_synced <= 1543819174) AND ((last_sync_attempted IS NULL) OR (last_sync_attempted <= 1543904674)))
  • Filter: ((import_enabled IS TRUE) AND (last_synced <= 1543819174))
13.          

CTE active_cals

14. 2.656 63.635 ↑ 1.4 8,524 1

Append (cost=0.00..109.54 rows=12,171 width=4) (actual time=33.154..63.635 rows=8,524 loops=1)

15. 0.008 0.008 ↑ 1.0 1 1

Result (cost=0.00..0.00 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)

16. 27.207 27.207 ↑ 1.3 4,819 1

Nested Loop (cost=0.08..6,821.85 rows=6,081 width=4) (actual time=0.020..27.207 rows=4,819 loops=1)

17. 0.000 33.764 ↑ 1.6 3,705 1

HashAggregate (cost=5,707.94..5,726.21 rows=6,090 width=4) (actual time=33.152..33.764 rows=3,705 loops=1)

  • Group Key: organizations.id
18. 28.470 28.470 ↑ 1.3 4,819 1

CTE Scan on active_orgs (cost=0.00..36.49 rows=6,081 width=4) (actual time=0.020..28.470 rows=4,819 loops=1)

19. 0.000 34.545 ↑ 1.6 3,705 1

CTE Scan on active_cals (cost=0.00..36.54 rows=6,090 width=4) (actual time=33.154..34.545 rows=3,705 loops=1)

20. 31.551 31.551 ↑ 1.5 4,098 1

Nested Loop (cost=0.08..5,704.89 rows=6,090 width=4) (actual time=0.049..31.551 rows=4,098 loops=1)

21. 13.386 13.386 ↑ 3.7 4,819 1

Nested Loop (cost=0.00..1,410.68 rows=17,984 width=4) (actual time=0.010..13.386 rows=4,819 loops=1)

22. 0.000 14.457 ↑ 1.0 1 4,819

Index Scan using organizations_pkey on organizations organizations_1 (cost=0.08..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=4,819)

  • Rows Removed by Join Filter: 49135
  • Join Filter: ((subscriptions.expires)::double precision > expired_1."time")
  • Index Cond: (id = subscriptions.organization_id)
  • Filter: (pricing_version = 'v4'::text)
23. 9.221 9.221 ↓ 1.0 53,954 1

Seq Scan on subscriptions (cost=0.00..1,194.86 rows=53,953 width=8) (actual time=0.007..9.221 rows=53,954 loops=1)

24. 17.678 17.678 ↑ 2.2 4,098 1

Nested Loop (cost=0.00..1,075.25 rows=9,202 width=4) (actual time=0.035..17.678 rows=4,098 loops=1)

25. 12.294 12.294 ↑ 1.0 1 4,098

Index Scan using organizations_pkey on organizations (cost=0.08..0.50 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=4,098)

26. 0.000 0.002 ↑ 1.0 1 1

CTE Scan on expired expired_1 (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Rows Removed by Join Filter: 23436
  • Join Filter: ((calendars.billing_expires)::double precision > expired."time")
  • Index Cond: (id = calendars.organization_id)
  • Filter: (pricing_version = 'v3'::text)
27. 15.696 15.696 ↑ 1.0 27,534 1

Seq Scan on calendars (cost=0.00..964.82 rows=27,606 width=12) (actual time=0.006..15.696 rows=27,534 loops=1)

28. 0.010 0.010 ↑ 1.0 1 1

CTE Scan on expired (cost=0.00..0.01 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)