explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mScS : Optimization for: plan #x2GT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 0.226 ↓ 0.0 0 1

Limit (cost=7.13..7.29 rows=1 width=9,718) (actual time=0.226..0.226 rows=0 loops=1)

2. 0.002 0.223 ↓ 0.0 0 1

Unique (cost=7.13..7.29 rows=1 width=9,718) (actual time=0.223..0.223 rows=0 loops=1)

3. 0.061 0.221 ↓ 0.0 0 1

Sort (cost=7.13..7.13 rows=1 width=9,718) (actual time=0.221..0.221 rows=0 loops=1)

  • Sort Key: subscripti0_.creation_date DESC, subscripti0_.id, subscripti0_.version, subscripti0_.modification_date, subscripti0_.object_name, subscripti0_.owner_membership, subscripti0_.lifecycle_change_date, subscripti0_.cancellation_choice, subscripti0_.cancellation_period, subscripti0_.overdue_amount, subscripti0_.overdue_period, subscripti0_.product_display, subscripti0_.product_path, subscripti0_.product_sku, subscripti0_.regular_period, subscripti0_.regular_period_discount_duration, subscripti0_.regular_period_price_discount_amount, subscripti0_.regular_period_price_list_amount, subscripti0_.regular_period_price_shipping_amount, subscripti0_.regular_period_product, subscripti0_.reminder_period, subscripti0_.send_payment_overdue, subscripti0_.send_reminder_enabled, subscripti0_.account, subscripti0_.start_date, subscripti0_.begin_period_end_date, subscripti0_.canceled_date, subscripti0_.currency, subscripti0_.custom_reference_id, subscripti0_.customer_external_reference, subscripti0_.deactivation_date, subscripti0_.end_date, subscripti0_.import_identifier, subscripti0_.last_dunning_step, subscripti0_.last_dunning_step_date, subscripti0_.event_context, subscripti0_.manual_renew, subscripti0_.master_payment, subscripti0_.mode, subscripti0_.next_date, subscripti0_.notification_date, subscripti0_.offer_coupon_instance, subscripti0_.overdue_notifications_sent, subscripti0_.payment_method_expiration_date, subscripti0_.price_decoration, subscripti0_.primary_external_reference, subscripti0_.quantity, subscripti0_.status, subscripti0_.receiver_address, subscripti0_.receiver_contact, subscripti0_.referrer, subscripti0_.regular_period_campaign, subscripti0_.regular_period_offer_type, subscripti0_.regular_period_subtotal, subscripti0_.source, subscripti0_.source_campaign, subscripti0_.source_key, subscripti0_.store_tax_price_mode, subscripti0_.tags, subscripti0_.tax_exemption_data, subscripti0_.trial_reminder_days, subscripti0_.trial_reminder_enabled, subscripti0_.type, subscripti0_.using_new_notifications
  • Sort Method: quicksort Memory: 25kB
4. 0.034 0.160 ↓ 0.0 0 1

Hash Join (cost=4.17..7.12 rows=1 width=9,718) (actual time=0.160..0.160 rows=0 loops=1)

  • Hash Cond: ((entryassoc1_.subscription)::text = (subscripti0_.id)::text)
  • Join Filter: ((entryassoc1_.period_date < subscripti0_.begin_period_end_date) AND (SubPlan 1))
  • Rows Removed by Join Filter: 2
5. 0.022 0.022 ↓ 1.1 86 1

Seq Scan on subscription_entry entryassoc1_ (cost=0.00..2.75 rows=75 width=35) (actual time=0.009..0.022 rows=86 loops=1)

6. 0.010 0.036 ↑ 1.0 1 1

Hash (cost=4.16..4.16 rows=1 width=9,718) (actual time=0.036..0.036 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.026 0.026 ↑ 1.0 1 1

Index Scan using idx_subscription_site on subscription subscripti0_ (cost=0.14..4.16 rows=1 width=9,718) (actual time=0.011..0.026 rows=1 loops=1)

  • Index Cond: ((site)::text = 'TY1L8QRlRHY'::text)
  • Filter: ((begin_period_end_date IS NOT NULL) AND ((lifecycle)::text = 'SUBL.A'::text))
  • Rows Removed by Filter: 8
8.          

SubPlan (for Hash Join)

9. 0.009 0.068 ↑ 1.0 1 1

GroupAggregate (cost=0.14..7.12 rows=1 width=31) (actual time=0.067..0.068 rows=1 loops=1)

  • Group Key: subscripti2_.subscription
10. 0.002 0.059 ↓ 2.0 2 1

Nested Loop (cost=0.14..7.10 rows=1 width=31) (actual time=0.055..0.059 rows=2 loops=1)

11. 0.015 0.015 ↓ 2.0 2 1

Seq Scan on subscription_entry subscripti2_ (cost=0.00..2.94 rows=1 width=31) (actual time=0.014..0.015 rows=2 loops=1)

  • Filter: ((subscription)::text = (subscripti0_.id)::text)
  • Rows Removed by Filter: 84
12. 0.042 0.042 ↑ 1.0 1 2

Index Only Scan using subscription_pkey on subscription subscripti3_ (cost=0.14..4.16 rows=1 width=23) (actual time=0.020..0.021 rows=1 loops=2)

  • Index Cond: (id = (subscripti0_.id)::text)
  • Heap Fetches: 2