explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lrio

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 19,733.820 ↓ 5.6 50 1

Limit (cost=0.28..1,657,411,274.23 rows=9 width=14) (actual time=5,781.807..19,733.820 rows=50 loops=1)

2. 4.475 19,733.772 ↓ 5.6 50 1

Index Scan using idx_bltr_sndbx_id on blc_translation bt1 (cost=0.28..1,657,411,274.23 rows=9 width=14) (actual time=5,781.806..19,733.772 rows=50 loops=1)

  • Index Cond: (sndbx_id IS NULL)
  • Filter: (((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)) AND (((admin_addition_status)::text <> 'NEW'::text) OR (admin_addition_status IS NULL)) AND (((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_tier IS NULL) OR (sndbx_tier = COALESCE((SubPlan 1), (SubPlan 2), sndbx_tier)))) OR ((sndbx_id IS NULL) AND (sndbx_tier = 999,999) AND ((SubPlan 3) = 0) AND ((SubPlan 4) = 0)) OR ((sndbx_id IS NULL) AND (sndbx_tier IS NULL) AND ((SubPlan 5) = 0))) AND ((SubPlan 11) > 0))
  • Rows Removed by Filter: 574
3.          

SubPlan (for Index Scan)

4. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

5. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_bltr_sndbx_tem_id on blc_translation bt2 (cost=0.28..2.30 rows=1 width=8) (never executed)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bt1.translation_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
6. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_bltr_sndbx_tem_id on blc_translation bt2_1 (cost=0.28..2.30 rows=1 width=8) (never executed)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bt1.sndbx_orig_item_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
8. 0.623 2.492 ↑ 1.0 1 623

Aggregate (cost=2.31..2.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=623)

9. 1.869 1.869 ↓ 0.0 0 623

Index Scan using idx_bltr_sndbx_tem_id on blc_translation bt2_2 (cost=0.28..2.30 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=623)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bt1.translation_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
  • Rows Removed by Filter: 0
10. 0.574 0.574 ↑ 1.0 1 574

Aggregate (cost=2.31..2.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=574)

11. 0.000 0.000 ↓ 0.0 0 574

Index Scan using idx_bltr_sndbx_tem_id on blc_translation bt2_3 (cost=0.28..2.30 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=574)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bt1.sndbx_orig_item_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
12. 0.623 1.869 ↑ 1.0 1 623

Aggregate (cost=2.31..2.32 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=623)

13. 1.246 1.246 ↓ 0.0 0 623

Index Scan using idx_bltr_sndbx_tem_id on blc_translation bt2_4 (cost=0.28..2.31 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=623)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bt1.translation_id))
  • Filter: (((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)) AND (((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_catalog_flag IS NULL) OR (sndbx_catalog_flag <> '-999999'::integer))) OR ((sndbx_id IS NULL) AND (sndbx_tier = 999,999) AND (sndbx_catalog_flag = '-228'::integer))))
  • Rows Removed by Filter: 0
14. 1.722 19,724.362 ↑ 1.0 1 574

Aggregate (cost=1,005,089.08..1,005,089.09 rows=1 width=8) (actual time=34.363..34.363 rows=1 loops=574)

15. 19,721.990 19,722.640 ↓ 0.0 0 574

Seq Scan on blc_product bp1 (cost=0.00..1,005,089.07 rows=3 width=8) (actual time=32.341..34.360 rows=0 loops=574)

  • Filter: (((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)) AND (((admin_addition_status)::text <> 'NEW'::text) OR (admin_addition_status IS NULL)) AND (bt1.catalog_disc <> catalog_disc) AND (((product_id)::character varying(255))::text = (bt1.entity_id)::text) AND (((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_tier IS NULL) OR (sndbx_tier = COALESCE((SubPlan 6), (SubPlan 7), sndbx_tier)))) OR ((sndbx_id IS NULL) AND (sndbx_tier = 999,999) AND ((SubPlan 8) = 0) AND ((SubPlan 9) = 0)) OR ((sndbx_id IS NULL) AND (sndbx_tier IS NULL) AND ((SubPlan 10) = 0))))
  • Rows Removed by Filter: 81,589
16.          

SubPlan (for Seq Scan)

17. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_blpr_sndbx_tem_id on blc_product bp2 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bp1.product_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
19. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_blpr_sndbx_tem_id on blc_product bp2_1 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bp1.sndbx_orig_item_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
21. 0.100 0.350 ↑ 1.0 1 50

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=50)

22. 0.250 0.250 ↓ 0.0 0 50

Index Scan using idx_blpr_sndbx_tem_id on blc_product bp2_2 (cost=0.42..2.44 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=50)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bp1.product_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
  • Rows Removed by Filter: 1
23. 0.100 0.100 ↑ 1.0 1 50

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=50)

24. 0.000 0.000 ↓ 0.0 0 50

Index Scan using idx_blpr_sndbx_tem_id on blc_product bp2_3 (cost=0.42..2.44 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=50)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bp1.sndbx_orig_item_id))
  • Filter: ((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)))
25. 0.050 0.200 ↑ 1.0 1 50

Aggregate (cost=2.45..2.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=50)

26. 0.150 0.150 ↓ 0.0 0 50

Index Scan using idx_blpr_sndbx_tem_id on blc_product bp2_4 (cost=0.42..2.45 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=50)

  • Index Cond: ((sndbx_orig_item_id IS NOT NULL) AND (sndbx_orig_item_id = bp1.product_id))
  • Filter: (((sndbx_archived_flag <> 'Y'::bpchar) OR (sndbx_archived_flag IS NULL)) AND (((sndbx_id = ANY ('{9603,9604}'::bigint[])) AND ((sndbx_catalog_flag IS NULL) OR (sndbx_catalog_flag <> '-999999'::integer))) OR ((sndbx_id IS NULL) AND (sndbx_tier = 999,999) AND (sndbx_catalog_flag = '-228'::integer))))
  • Rows Removed by Filter: 1
Planning time : 2.562 ms
Execution time : 19,734.229 ms