explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IGUI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 254,241.097 ↑ 5.0 10 1

Limit (cost=3,857,278.46..3,857,286.83 rows=50 width=1,398) (actual time=254,241.067..254,241.097 rows=10 loops=1)

  • subscripti0_.lifecycle_change_date, subscripti0_.cancellation_choice, subscripti0_.cancellation_period, subscripti0_.overdue_amount, subscripti0_.overdue_period, subscripti0_.product_d
2. 0.022 254,241.091 ↑ 5.7 10 1

Unique (cost=3,857,278.46..3,857,288.00 rows=57 width=1,398) (actual time=254,241.065..254,241.091 rows=10 loops=1)

3. 0.145 254,241.069 ↑ 5.7 10 1

Sort (cost=3,857,278.46..3,857,278.60 rows=57 width=1,398) (actual time=254,241.064..254,241.069 rows=10 loops=1)

  • Sort Key: subscripti0_.creation_date DESC, subscripti0_.id, subscripti0_.version, subscripti0_.modification_date, subscripti0_.object_name, subscripti0_.owner_membership,
  • Sort Method: quicksort Memory: 30kB
4. 729.360 254,240.924 ↑ 5.7 10 1

Hash Join (cost=3,839,505.89..3,857,276.79 rows=57 width=1,398) (actual time=253,675.388..254,240.924 rows=10 loops=1)

  • Hash Cond: (((subscripti0_.id)::text = ("ANY_subquery".subscription)::text) AND (entryassoc1_.creation_date = "ANY_subquery".max))
5. 1.700 2.936 ↑ 22.8 10 1

Nested Loop (cost=1.12..17,770.30 rows=228 width=1,429) (actual time=2.064..2.936 rows=10 loops=1)

6. 0.000 1.236 ↑ 20.0 10 1

Index Scan using idx_subscription_site on subscription subscripti0_ (cost=0.56..3,230.43 rows=200 width=1,398) (actual time=1.143..1.236 rows=10 loops=1)

  • Index Cond: ((site)::text = 'DtbFJtkOT4Q'::text)
  • Filter: ((begin_period_end_date IS NOT NULL) AND ((lifecycle)::text = 'SUBL.A'::text))
  • Rows Removed by Filter: 43
7. 1.660 1.660 ↑ 6.0 1 10

Index Scan using idx_subscription_entry_subscription on subscription_entry entryassoc1_ (cost=0.56..72.64 rows=6 width=35) (actual time=0.166..0.166 rows=1 loops=10)

  • Index Cond: ((subscription)::text = (subscripti0_.id)::text)
  • Filter: (period_date < subscripti0_.begin_period_end_date)
8. 2,241.754 253,508.628 ↓ 114.7 4,587,785 1

Hash (cost=3,838,904.78..3,838,904.78 rows=40,000 width=31) (actual time=253,508.628..253,508.628 rows=4,587,785 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 128 (originally 1) Memory Usage: 3585kB
9. 8,245.273 251,266.874 ↓ 114.7 4,587,785 1

HashAggregate (cost=3,838,504.78..3,838,904.78 rows=40,000 width=31) (actual time=248,309.258..251,266.874 rows=4,587,785 loops=1)

  • Group Key: "ANY_subquery".max, ("ANY_subquery".subscription)::text
10. 2,231.065 243,021.601 ↓ 5.0 4,587,785 1

Subquery Scan on ANY_subquery (cost=66.68..3,833,878.81 rows=925,193 width=31) (actual time=0.971..243,021.601 rows=4,587,785 loops=1)

11. 7,436.576 240,790.536 ↓ 5.0 4,587,785 1

GroupAggregate (cost=66.68..3,824,626.88 rows=925,193 width=31) (actual time=0.970..240,790.536 rows=4,587,785 loops=1)

  • Group Key: subscripti2_.subscription
12. 16,850.369 233,353.960 ↓ 1.1 17,299,243 1

Merge Join (cost=66.68..3,737,185.67 rows=15,637,856 width=31) (actual time=0.041..233,353.960 rows=17,299,243 loops=1)

  • Merge Cond: ((subscripti3_.id)::text = (subscripti2_.subscription)::text)
13. 16,263.342 16,263.342 ↓ 1.0 4,597,244 1

Index Only Scan using subscription_pkey on subscription subscripti3_ (cost=0.56..1,096,754.81 rows=4,580,799 width=23) (actual time=0.033..16,263.342 rows=4,597,244 loops=1)

  • Heap Fetches: 3216617
14. 200,240.249 200,240.249 ↓ 1.1 17,299,243 1

Index Scan using idx_subscription_entry_subscription on subscription_entry subscripti2_ (cost=0.56..2,433,542.43 rows=15,637,856 width=31) (actual time=0.004..200,240.249 rows=17,299,243 loops=1)

Planning time : 6.526 ms
Execution time : 254,289.888 ms