explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xTXQ : Optimization for: plan #J3mb

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.969 3.666 ↓ 2.1 357 1

Sort (cost=146.99..147.41 rows=168 width=1,730) (actual time=3.625..3.666 rows=357 loops=1)

  • Output: tag.id, tag.tag_type, tag.code, tag.name, brand_1.id, brand_1.created_at, brand_1.updated_at, brand_1.code, brand_1.name, brand_1.enabled, brand_1.statement_reference, brand_1.category, brand_1.locale, brand_1.timezone, brand_1.mfa_challenge_frequency, brand_1.order_created_notification_email_address, data_processor_1.id, data_processor_1.code, data_processor_1.name, data_processor_1.enabled, product_1.created_at, product_1.updated_at, product_1.id, product_1.parent_id, product_1.code, product_1.name, product_1.state, product_1.product_type, product_1.is_enabled_by_default, product_1.brand_id, product_1.data_processor_id, product_1.currency, product_1.locale, product_1.has_face_value, product_1.base_cost_multiplier, product_1.base_price_multiplier, product_1.availability, product_1.ignore_api_stock_reserve_thresholds, product_1.stocked_delivery_estimate, product_1.denomination_type, product_1.minimum_value, product_1.maximum_value, product_1.available_denominations, product_1.e_code_type, product_1.e_code_pin_source, product_1.e_code_usage_type, product_1.e_code_retention_policy, product_1.code_format_pattern, product_1.public_name, product_1.egift_name, product_1.description, product_1.primary_colour, product_1.redeemable_at, product_1.enable_digital_wrapping, product_1.barcode_format, product_1.barcode_format_string, product_1.terms_consumer, product_1.terms_buyer, product_1.redeem_markdown, product_1.refund_policy_markdown, product_1.reissuance_policy_markdown, product_1.product_url, product_1.brand_url, product_1.customer_service_url, product_1.customer_service_phone, product_1.balance_check_url, product_1.expiry_in_months, product_1.expiry_mode, product_1.show_absolute_expiry, product_1.show_discount, product_1.direct_terms_enabled, product_1.code_length, product_1.pin_length, product_1.billing_model
  • Sort Key: tag.name
  • Sort Method: quicksort Memory: 806kB
  • Buffers: shared hit=1118
2. 0.311 2.697 ↓ 2.1 357 1

Hash Left Join (cost=24.04..140.78 rows=168 width=1,730) (actual time=0.274..2.697 rows=357 loops=1)

  • Output: tag.id, tag.tag_type, tag.code, tag.name, brand_1.id, brand_1.created_at, brand_1.updated_at, brand_1.code, brand_1.name, brand_1.enabled, brand_1.statement_reference, brand_1.category, brand_1.locale, brand_1.timezone, brand_1.mfa_challenge_frequency, brand_1.order_created_notification_email_address, data_processor_1.id, data_processor_1.code, data_processor_1.name, data_processor_1.enabled, product_1.created_at, product_1.updated_at, product_1.id, product_1.parent_id, product_1.code, product_1.name, product_1.state, product_1.product_type, product_1.is_enabled_by_default, product_1.brand_id, product_1.data_processor_id, product_1.currency, product_1.locale, product_1.has_face_value, product_1.base_cost_multiplier, product_1.base_price_multiplier, product_1.availability, product_1.ignore_api_stock_reserve_thresholds, product_1.stocked_delivery_estimate, product_1.denomination_type, product_1.minimum_value, product_1.maximum_value, product_1.available_denominations, product_1.e_code_type, product_1.e_code_pin_source, product_1.e_code_usage_type, product_1.e_code_retention_policy, product_1.code_format_pattern, product_1.public_name, product_1.egift_name, product_1.description, product_1.primary_colour, product_1.redeemable_at, product_1.enable_digital_wrapping, product_1.barcode_format, product_1.barcode_format_string, product_1.terms_consumer, product_1.terms_buyer, product_1.redeem_markdown, product_1.refund_policy_markdown, product_1.reissuance_policy_markdown, product_1.product_url, product_1.brand_url, product_1.customer_service_url, product_1.customer_service_phone, product_1.balance_check_url, product_1.expiry_in_months, product_1.expiry_mode, product_1.show_absolute_expiry, product_1.show_discount, product_1.direct_terms_enabled, product_1.code_length, product_1.pin_length, product_1.billing_model
  • Inner Unique: true
  • Hash Cond: (product_1.brand_id = brand_1.id)
  • Buffers: shared hit=1118
3. 0.119 2.173 ↓ 2.1 357 1

Nested Loop Left Join (cost=10.98..127.29 rows=168 width=1,631) (actual time=0.058..2.173 rows=357 loops=1)

  • Output: tag.id, tag.tag_type, tag.code, tag.name, product_1.created_at, product_1.updated_at, product_1.id, product_1.parent_id, product_1.code, product_1.name, product_1.state, product_1.product_type, product_1.is_enabled_by_default, product_1.brand_id, product_1.data_processor_id, product_1.currency, product_1.locale, product_1.has_face_value, product_1.base_cost_multiplier, product_1.base_price_multiplier, product_1.availability, product_1.ignore_api_stock_reserve_thresholds, product_1.stocked_delivery_estimate, product_1.denomination_type, product_1.minimum_value, product_1.maximum_value, product_1.available_denominations, product_1.e_code_type, product_1.e_code_pin_source, product_1.e_code_usage_type, product_1.e_code_retention_policy, product_1.code_format_pattern, product_1.public_name, product_1.egift_name, product_1.description, product_1.primary_colour, product_1.redeemable_at, product_1.enable_digital_wrapping, product_1.barcode_format, product_1.barcode_format_string, product_1.terms_consumer, product_1.terms_buyer, product_1.redeem_markdown, product_1.refund_policy_markdown, product_1.reissuance_policy_markdown, product_1.product_url, product_1.brand_url, product_1.customer_service_url, product_1.customer_service_phone, product_1.balance_check_url, product_1.expiry_in_months, product_1.expiry_mode, product_1.show_absolute_expiry, product_1.show_discount, product_1.direct_terms_enabled, product_1.code_length, product_1.pin_length, product_1.billing_model, data_processor_1.id, data_processor_1.code, data_processor_1.name, data_processor_1.enabled
  • Buffers: shared hit=1113
4. 0.013 0.032 ↑ 1.0 3 1

Hash Join (cost=8.60..10.34 rows=3 width=104) (actual time=0.021..0.032 rows=3 loops=1)

  • Output: tag.id, tag.tag_type, tag.code, tag.name
  • Hash Cond: (tag.id = product_tag.tag_id)
  • Buffers: shared hit=5
5. 0.008 0.008 ↑ 1.1 41 1

Seq Scan on public.tag (cost=0.00..1.44 rows=44 width=104) (actual time=0.004..0.008 rows=41 loops=1)

  • Output: tag.id, tag.tag_type, tag.code, tag.name
  • Buffers: shared hit=1
6. 0.004 0.011 ↑ 1.0 3 1

Hash (cost=8.56..8.56 rows=3 width=8) (actual time=0.011..0.011 rows=3 loops=1)

  • Output: product_tag.tag_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
7. 0.007 0.007 ↑ 1.0 3 1

Index Scan using ix_product_tag_product_id on public.product_tag (cost=0.28..8.56 rows=3 width=8) (actual time=0.006..0.007 rows=3 loops=1)

  • Output: product_tag.tag_id
  • Index Cond: (9 = product_tag.product_id)
  • Buffers: shared hit=4
8. 0.681 2.022 ↓ 2.0 119 3

Hash Left Join (cost=2.39..39.97 rows=60 width=1,535) (actual time=0.020..0.674 rows=119 loops=3)

  • Output: product_tag_1.tag_id, product_1.created_at, product_1.updated_at, product_1.id, product_1.parent_id, product_1.code, product_1.name, product_1.state, product_1.product_type, product_1.is_enabled_by_default, product_1.brand_id, product_1.data_processor_id, product_1.currency, product_1.locale, product_1.has_face_value, product_1.base_cost_multiplier, product_1.base_price_multiplier, product_1.availability, product_1.ignore_api_stock_reserve_thresholds, product_1.stocked_delivery_estimate, product_1.denomination_type, product_1.minimum_value, product_1.maximum_value, product_1.available_denominations, product_1.e_code_type, product_1.e_code_pin_source, product_1.e_code_usage_type, product_1.e_code_retention_policy, product_1.code_format_pattern, product_1.public_name, product_1.egift_name, product_1.description, product_1.primary_colour, product_1.redeemable_at, product_1.enable_digital_wrapping, product_1.barcode_format, product_1.barcode_format_string, product_1.terms_consumer, product_1.terms_buyer, product_1.redeem_markdown, product_1.refund_policy_markdown, product_1.reissuance_policy_markdown, product_1.product_url, product_1.brand_url, product_1.customer_service_url, product_1.customer_service_phone, product_1.balance_check_url, product_1.expiry_in_months, product_1.expiry_mode, product_1.show_absolute_expiry, product_1.show_discount, product_1.direct_terms_enabled, product_1.code_length, product_1.pin_length, product_1.billing_model, data_processor_1.id, data_processor_1.code, data_processor_1.name, data_processor_1.enabled
  • Inner Unique: true
  • Hash Cond: (product_1.data_processor_id = data_processor_1.id)
  • Buffers: shared hit=1108
9. 0.432 1.329 ↓ 2.0 119 3

Nested Loop (cost=0.56..37.96 rows=60 width=1,462) (actual time=0.015..0.443 rows=119 loops=3)

  • Output: product_tag_1.tag_id, product_1.created_at, product_1.updated_at, product_1.id, product_1.parent_id, product_1.code, product_1.name, product_1.state, product_1.product_type, product_1.is_enabled_by_default, product_1.brand_id, product_1.data_processor_id, product_1.currency, product_1.locale, product_1.has_face_value, product_1.base_cost_multiplier, product_1.base_price_multiplier, product_1.availability, product_1.ignore_api_stock_reserve_thresholds, product_1.stocked_delivery_estimate, product_1.denomination_type, product_1.minimum_value, product_1.maximum_value, product_1.available_denominations, product_1.e_code_type, product_1.e_code_pin_source, product_1.e_code_usage_type, product_1.e_code_retention_policy, product_1.code_format_pattern, product_1.public_name, product_1.egift_name, product_1.description, product_1.primary_colour, product_1.redeemable_at, product_1.enable_digital_wrapping, product_1.barcode_format, product_1.barcode_format_string, product_1.terms_consumer, product_1.terms_buyer, product_1.redeem_markdown, product_1.refund_policy_markdown, product_1.reissuance_policy_markdown, product_1.product_url, product_1.brand_url, product_1.customer_service_url, product_1.customer_service_phone, product_1.balance_check_url, product_1.expiry_in_months, product_1.expiry_mode, product_1.show_absolute_expiry, product_1.show_discount, product_1.direct_terms_enabled, product_1.code_length, product_1.pin_length, product_1.billing_model
  • Inner Unique: true
  • Buffers: shared hit=1107
10. 0.183 0.183 ↓ 2.0 119 3

Index Scan using ix_product_tag_tag_id on public.product_tag product_tag_1 (cost=0.28..3.42 rows=60 width=16) (actual time=0.010..0.061 rows=119 loops=3)

  • Output: product_tag_1.product_id, product_tag_1.tag_id
  • Index Cond: (tag.id = product_tag_1.tag_id)
  • Buffers: shared hit=34
11. 0.714 0.714 ↑ 1.0 1 357

Index Scan using product_pkey on public.product product_1 (cost=0.28..0.58 rows=1 width=1,454) (actual time=0.002..0.002 rows=1 loops=357)

  • Output: product_1.created_at, product_1.updated_at, product_1.id, product_1.parent_id, product_1.code, product_1.name, product_1.state, product_1.product_type, product_1.is_enabled_by_default, product_1.brand_id, product_1.data_processor_id, product_1.currency, product_1.locale, product_1.has_face_value, product_1.base_cost_multiplier, product_1.base_price_multiplier, product_1.availability, product_1.ignore_api_stock_reserve_thresholds, product_1.stocked_delivery_estimate, product_1.denomination_type, product_1.minimum_value, product_1.maximum_value, product_1.available_denominations, product_1.e_code_type, product_1.e_code_pin_source, product_1.e_code_usage_type, product_1.e_code_retention_policy, product_1.code_format_pattern, product_1.public_name, product_1.egift_name, product_1.description, product_1.primary_colour, product_1.redeemable_at, product_1.enable_digital_wrapping, product_1.barcode_format, product_1.barcode_format_string, product_1.terms_consumer, product_1.terms_buyer, product_1.redeem_markdown, product_1.refund_policy_markdown, product_1.reissuance_policy_markdown, product_1.product_url, product_1.brand_url, product_1.customer_service_url, product_1.customer_service_phone, product_1.balance_check_url, product_1.expiry_in_months, product_1.expiry_mode, product_1.show_absolute_expiry, product_1.show_discount, product_1.direct_terms_enabled, product_1.code_length, product_1.pin_length, product_1.billing_model
  • Index Cond: (product_1.id = product_tag_1.product_id)
  • Buffers: shared hit=1073
12. 0.008 0.012 ↓ 1.1 40 1

Hash (cost=1.37..1.37 rows=37 width=73) (actual time=0.012..0.012 rows=40 loops=1)

  • Output: data_processor_1.id, data_processor_1.code, data_processor_1.name, data_processor_1.enabled
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
13. 0.004 0.004 ↓ 1.1 40 1

Seq Scan on public.data_processor data_processor_1 (cost=0.00..1.37 rows=37 width=73) (actual time=0.002..0.004 rows=40 loops=1)

  • Output: data_processor_1.id, data_processor_1.code, data_processor_1.name, data_processor_1.enabled
  • Buffers: shared hit=1
14. 0.127 0.213 ↓ 1.0 363 1

Hash (cost=8.58..8.58 rows=358 width=99) (actual time=0.213..0.213 rows=363 loops=1)

  • Output: brand_1.id, brand_1.created_at, brand_1.updated_at, brand_1.code, brand_1.name, brand_1.enabled, brand_1.statement_reference, brand_1.category, brand_1.locale, brand_1.timezone, brand_1.mfa_challenge_frequency, brand_1.order_created_notification_email_address
  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=5
15. 0.086 0.086 ↓ 1.0 363 1

Seq Scan on public.brand brand_1 (cost=0.00..8.58 rows=358 width=99) (actual time=0.003..0.086 rows=363 loops=1)

  • Output: brand_1.id, brand_1.created_at, brand_1.updated_at, brand_1.code, brand_1.name, brand_1.enabled, brand_1.statement_reference, brand_1.category, brand_1.locale, brand_1.timezone, brand_1.mfa_challenge_frequency, brand_1.order_created_notification_email_address
  • Buffers: shared hit=5