explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 69YY

Settings
# exclusive inclusive rows x rows loops node
1. 1.614 39.667 ↓ 31.7 1,777 1

Hash Left Join (cost=7.00..70.19 rows=56 width=1,744) (actual time=0.084..39.667 rows=1,777 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, product_1.state_reason
  • Inner Unique: true
  • Hash Cond: (product_1.data_processor_id = data_processor_1.id)
  • Buffers: shared hit=9129
2. 1.762 38.033 ↓ 31.7 1,777 1

Nested Loop Left Join (cost=5.17..68.20 rows=56 width=1,671) (actual time=0.050..38.033 rows=1,777 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, product_1.state_reason, 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
  • Inner Unique: true
  • Buffers: shared hit=9128
3. 0.868 20.278 ↓ 31.7 1,777 1

Nested Loop Left Join (cost=5.02..56.66 rows=56 width=1,572) (actual time=0.043..20.278 rows=1,777 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, product_1.state_reason
  • Buffers: shared hit=5574
4. 0.030 0.030 ↓ 15.0 15 1

Seq Scan on public.tag (cost=0.00..1.55 rows=1 width=104) (actual time=0.006..0.030 rows=15 loops=1)

  • Output: tag.id, tag.tag_type, tag.code, tag.name
  • Filter: ((tag.tag_type)::text = 'Category'::text)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=1
5. 2.142 19.380 ↓ 2.0 118 15

Nested Loop (cost=5.02..54.51 rows=60 width=1,476) (actual time=0.035..1.292 rows=118 loops=15)

  • 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, product_1.state_reason
  • Inner Unique: true
  • Buffers: shared hit=5573
6. 0.945 1.245 ↓ 2.0 118 15

Bitmap Heap Scan on public.product_tag product_tag_1 (cost=4.75..19.50 rows=60 width=16) (actual time=0.027..0.083 rows=118 loops=15)

  • Output: product_tag_1.product_id, product_tag_1.tag_id
  • Recheck Cond: (tag.id = product_tag_1.tag_id)
  • Heap Blocks: exact=200
  • Buffers: shared hit=238
7. 0.300 0.300 ↓ 2.0 118 15

Bitmap Index Scan on ix_product_tag_tag_id (cost=0.00..4.73 rows=60 width=0) (actual time=0.020..0.020 rows=118 loops=15)

  • Index Cond: (tag.id = product_tag_1.tag_id)
  • Buffers: shared hit=38
8. 15.993 15.993 ↑ 1.0 1 1,777

Index Scan using product_pkey on public.product product_1 (cost=0.28..0.58 rows=1 width=1,468) (actual time=0.009..0.009 rows=1 loops=1,777)

  • 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, product_1.state_reason
  • Index Cond: (product_1.id = product_tag_1.product_id)
  • Buffers: shared hit=5335
9. 15.993 15.993 ↑ 1.0 1 1,777

Index Scan using brand_pkey on public.brand brand_1 (cost=0.15..0.21 rows=1 width=99) (actual time=0.009..0.009 rows=1 loops=1,777)

  • Output: brand_1.id, brand_1.created_at, brand_1.code, brand_1.name, brand_1.enabled, brand_1.statement_reference, brand_1.category, brand_1.blocked_verticals, brand_1.locale, brand_1.updated_at, brand_1.timezone, brand_1.mfa_challenge_frequency, brand_1.order_created_notification_email_address
  • Index Cond: (brand_1.id = product_1.brand_id)
  • Buffers: shared hit=3554
10. 0.011 0.020 ↓ 1.1 42 1

Hash (cost=1.37..1.37 rows=37 width=73) (actual time=0.020..0.020 rows=42 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
11. 0.009 0.009 ↓ 1.1 42 1

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

  • Output: data_processor_1.id, data_processor_1.code, data_processor_1.name, data_processor_1.enabled
  • Buffers: shared hit=1