explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q6sf

Settings
# exclusive inclusive rows x rows loops node
1. 2.995 7,709.643 ↓ 2.3 419 1

Sort (cost=6,585,006,519.24..6,585,006,519.69 rows=179 width=1,038) (actual time=7,709.606..7,709.643 rows=419 loops=1)

  • Sort Key: look.created_at DESC, look_product_associations_1."position
  • Sort Method: quicksort Memory: 256kB
2. 31.375 7,706.648 ↓ 2.3 419 1

Nested Loop Left Join (cost=1.44..6,585,006,512.55 rows=179 width=1,038) (actual time=254.022..7,706.648 rows=419 loops=1)

  • Join Filter: (product_variant_1.id = product_variant_pre_allocation_1.variant_id)
  • Rows Removed by Join Filter: 242028
3. 1,009.807 7,655.641 ↓ 2.3 409 1

Nested Loop Left Join (cost=1.44..6,585,004,903.23 rows=179 width=990) (actual time=253.524..7,655.641 rows=409 loops=1)

  • Join Filter: (product_1.id = product_variant_1.product_id)
  • Rows Removed by Join Filter: 4420631
4. 0.181 5,671.482 ↑ 1.1 48 1

Nested Loop Left Join (cost=1.44..6,584,841,750.58 rows=55 width=880) (actual time=138.917..5,671.482 rows=48 loops=1)

5. 0.035 5,669.509 ↑ 1.0 16 1

Limit (cost=0.86..6,584,841,544.55 rows=16 width=36) (actual time=138.834..5,669.509 rows=16 loops=1)

6. 0.861 5,669.474 ↑ 2.0 16 1

Nested Loop Anti Join (cost=0.86..13,169,683,088.25 rows=32 width=36) (actual time=138.832..5,669.474 rows=16 loops=1)

7. 1.217 1.217 ↑ 13.2 938 1

Index Scan Backward using ix_look_created_at on look (cost=0.29..500.90 rows=12,339 width=36) (actual time=0.026..1.217 rows=938 loops=1)

  • Filter: (validation_state = 'VALIDATED'::look_validation_state)
8. 2.834 5,667.396 ↑ 3.0 1 938

Nested Loop (cost=0.58..2,318,178.06 rows=3 width=8) (actual time=6.042..6.042 rows=1 loops=938)

9. 4.690 4.690 ↑ 3.0 1 938

Index Only Scan using idx_19040_primary on look_product_associations (cost=0.29..0.48 rows=3 width=16) (actual time=0.004..0.005 rows=1 loops=938)

  • Index Cond: (look_id = look.id)
  • Heap Fetches: 1102
10. 11.394 5,659.872 ↑ 1.0 1 1,102

Index Scan using idx_19051_primary on product (cost=0.29..772,707.39 rows=1 width=8) (actual time=5.136..5.136 rows=1 loops=1,102)

  • Index Cond: (id = look_product_associations.product_id)
  • Filter: ((json_array_length(images) = 0) OR hidden OR ((gender)::text <> ALL ('{Boy,Mixed}'::text[])) OR ((season)::text <> ALL ('{All,Hivers}'::text[])) OR (NOT (SubPlan 2)))
  • Rows Removed by Filter: 0
11.          

SubPlan (for Index Scan)

12. 5,642.745 5,648.478 ↑ 1.0 1 253

Seq Scan on product_variant (cost=0.00..772,706.94 rows=1 width=0) (actual time=22.326..22.326 rows=1 loops=253)

  • Filter: ((product.id = product_id) AND (((first_size)::text = '8 ans'::text) OR (((product.meta_category)::text = 'TOP'::text) AND (((first_size)::text = '6 ans'::text) OR ((second_size)::text = '6 ans'::text))) OR (((product.meta_category)::text = 'TOP'::text) AND (((first_size)::text = '6 ans'::text) OR ((second_size)::text = '6 ans'::text))) OR (((product.meta_category)::text = 'TOP'::text) AND (((first_size)::text = '6 ans'::text) OR ((second_size)::text = '6 ans'::text) OR ((first_size)::text = '6 ans'::text) OR ((second_size)::text = '6 ans'::text) OR ((first_size)::text = '6 ans'::text) OR ((second_size)::text = '6 ans'::text)))) AND (((pvx_stocks)::numeric - COALESCE((SubPlan 1), '0'::numeric)) >= '1'::numeric))
  • Rows Removed by Filter: 87838
13.          

SubPlan (for Seq Scan)

14. 2.457 5.733 ↑ 1.0 1 273

Aggregate (cost=8.30..8.31 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=273)

15. 3.276 3.276 ↑ 1.0 1 273

Index Scan using test_product_variant_pre_allocation_variant_id_idx on product_variant_pre_allocation (cost=0.28..8.29 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=273)

  • Index Cond: (variant_id = product_variant.id)
  • Filter: (request_id <> 223265)
16. 0.512 1.792 ↑ 1.0 3 16

Nested Loop (cost=0.58..12.84 rows=3 width=852) (actual time=0.034..0.112 rows=3 loops=16)

17. 0.560 0.560 ↑ 1.0 3 16

Index Scan using idx_19040_primary on look_product_associations look_product_associations_1 (cost=0.29..11.51 rows=3 width=24) (actual time=0.014..0.035 rows=3 loops=16)

  • Index Cond: (look.id = look_id)
18. 0.720 0.720 ↑ 1.0 1 48

Index Scan using idx_19051_primary on product product_1 (cost=0.29..0.44 rows=1 width=836) (actual time=0.015..0.015 rows=1 loops=48)

  • Index Cond: (id = look_product_associations_1.product_id)
19. 934.953 974.352 ↑ 1.0 92,105 48

Materialize (cost=0.00..4,734.34 rows=92,156 width=110) (actual time=0.012..20.299 rows=92,105 loops=48)

20. 39.399 39.399 ↑ 1.0 92,105 1

Seq Scan on product_variant product_variant_1 (cost=0.00..2,743.56 rows=92,156 width=110) (actual time=0.011..39.399 rows=92,105 loops=1)

21. 19.446 19.632 ↑ 1.0 592 409

Materialize (cost=0.00..15.91 rows=594 width=48) (actual time=0.000..0.048 rows=592 loops=409)

22. 0.186 0.186 ↑ 1.0 592 1

Seq Scan on product_variant_pre_allocation product_variant_pre_allocation_1 (cost=0.00..12.94 rows=594 width=48) (actual time=0.023..0.186 rows=592 loops=1)