explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2C4K : Optimization for: Optimization for: plan #4Dgx; plan #fkSa

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7.019 31.136 ↑ 26.0 1 1

Gather (cost=21,645.78..21,679.34 rows=26 width=166) (actual time=31.117..31.136 rows=1 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Single Copy: true
2. 0.034 24.117 ↑ 26.0 1 1

Hash Join (cost=20,645.78..20,676.74 rows=26 width=166) (actual time=24.116..24.117 rows=1 loops=1)

  • Hash Cond: (pwe.lf_category_id = lcd.lf_category_id)
3. 0.047 24.041 ↑ 31.0 1 1

GroupAggregate (cost=20,644.20..20,674.47 rows=31 width=92) (actual time=24.041..24.041 rows=1 loops=1)

  • Group Key: pwe.lf_category_id, tag_attrs.value, (count(1))
4. 0.051 23.994 ↑ 60.7 28 1

Sort (cost=20,644.20..20,648.45 rows=1,699 width=48) (actual time=23.990..23.994 rows=28 loops=1)

  • Sort Key: pwe.lf_category_id, tag_attrs.value, (count(1))
5. 0.007 23.943 ↑ 60.7 28 1

Nested Loop (cost=10,320.24..20,553.04 rows=1,699 width=48) (actual time=13.317..23.943 rows=28 loops=1)

6. 0.009 12.336 ↑ 1.0 1 1

Aggregate (cost=10,270.14..10,270.15 rows=1 width=8) (actual time=12.335..12.336 rows=1 loops=1)

7. 0.305 12.327 ↑ 60.7 28 1

Nested Loop (cost=50.10..10,265.89 rows=1,699 width=0) (actual time=1.366..12.327 rows=28 loops=1)

8. 9.849 12.021 ↑ 9.3 183 1

Nested Loop (cost=50.09..8,337.52 rows=1,699 width=32) (actual time=0.922..12.021 rows=183 loops=1)

9. 1.919 2.167 ↑ 1.0 1,692 1

Bitmap Heap Scan on mv_product_with_elements pwe_1 (cost=50.09..6,409.15 rows=1,699 width=270) (actual time=0.450..2.167 rows=1,692 loops=1)

  • Recheck Cond: ((host_id = ANY ('{11}'::integer[])) AND (lf_gender_id = ANY ('{12}'::integer[])) AND (lf_category_id = ANY ('{23}'::integer[])))
10. 0.248 0.248 ↑ 1.0 1,692 1

Bitmap Index Scan on mv_product_with_elements_host_category_gender_idx (cost=0.00..49.66 rows=1,699 width=0) (actual time=0.248..0.248 rows=1,692 loops=1)

  • Index Cond: ((host_id = ANY ('{11}'::integer[])) AND (lf_gender_id = ANY ('{12}'::integer[])) AND (lf_category_id = ANY ('{23}'::integer[])))
11. 0.005 0.005 ↓ 0.0 0 1,692

Function Scan on jsonb_each_text tags_1 (cost=0.00..1.13 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1,692)

  • Filter: (key = ANY ('{"fabric finishing"}'::text[]))
  • Rows Removed by Filter: 8
12. 0.001 0.001 ↓ 0.0 0 183

Function Scan on jsonb_array_elements_text tag_attrs_1 (cost=0.01..1.13 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=183)

  • Filter: (value = ANY ('{seam}'::text[]))
  • Rows Removed by Filter: 1
13. 0.291 11.600 ↑ 60.7 28 1

Nested Loop (cost=50.10..10,265.89 rows=1,699 width=40) (actual time=0.980..11.600 rows=28 loops=1)

14. 9.647 11.308 ↑ 9.3 183 1

Nested Loop (cost=50.09..8,337.52 rows=1,699 width=40) (actual time=0.662..11.308 rows=183 loops=1)

15. 1.409 1.656 ↑ 1.0 1,692 1

Bitmap Heap Scan on mv_product_with_elements pwe (cost=50.09..6,409.15 rows=1,699 width=278) (actual time=0.451..1.656 rows=1,692 loops=1)

  • Recheck Cond: ((host_id = ANY ('{11}'::integer[])) AND (lf_gender_id = ANY ('{12}'::integer[])) AND (lf_category_id = ANY ('{23}'::integer[])))
16. 0.247 0.247 ↑ 1.0 1,692 1

Bitmap Index Scan on mv_product_with_elements_host_category_gender_idx (cost=0.00..49.66 rows=1,699 width=0) (actual time=0.247..0.247 rows=1,692 loops=1)

  • Index Cond: ((host_id = ANY ('{11}'::integer[])) AND (lf_gender_id = ANY ('{12}'::integer[])) AND (lf_category_id = ANY ('{23}'::integer[])))
17. 0.005 0.005 ↓ 0.0 0 1,692

Function Scan on jsonb_each_text tags (cost=0.00..1.13 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1,692)

  • Filter: (key = ANY ('{"fabric finishing"}'::text[]))
  • Rows Removed by Filter: 8
18. 0.001 0.001 ↓ 0.0 0 183

Function Scan on jsonb_array_elements_text tag_attrs (cost=0.01..1.13 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=183)

  • Filter: (value = ANY ('{seam}'::text[]))
  • Rows Removed by Filter: 1
19. 0.010 0.042 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=86) (actual time=0.042..0.042 rows=26 loops=1)

20. 0.032 0.032 ↑ 1.0 26 1

Seq Scan on mv_lf_category_dim lcd (cost=0.00..1.26 rows=26 width=86) (actual time=0.032..0.032 rows=26 loops=1)