explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Dgx

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.055 33.554 ↑ 115.0 1 1

Hash Join (cost=20,775.17..20,811.43 rows=115 width=166) (actual time=33.538..33.554 rows=1 loops=1)

  • Hash Cond: (lcd.lf_category_id = q1.lf_category_id)
2. 3.046 3.046 ↑ 24.0 31 1

Foreign Scan on lf_category_dim lcd (cost=100.00..132.32 rows=744 width=86) (actual time=3.044..3.046 rows=31 loops=1)

3. 0.023 30.453 ↑ 31.0 1 1

Hash (cost=20,674.78..20,674.78 rows=31 width=84) (actual time=30.453..30.453 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
4. 0.000 30.430 ↑ 31.0 1 1

Subquery Scan on q1 (cost=20,644.20..20,674.78 rows=31 width=84) (actual time=30.429..30.430 rows=1 loops=1)

5. 0.055 30.430 ↑ 31.0 1 1

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

  • Group Key: pwe.lf_category_id, tag_attrs.value, (count(1))
6. 0.031 30.375 ↑ 60.7 28 1

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

  • Sort Key: pwe.lf_category_id, tag_attrs.value, (count(1))
  • Sort Method: quicksort Memory: 27kB
7. 0.014 30.344 ↑ 60.7 28 1

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

8. 0.008 15.163 ↑ 1.0 1 1

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

9. 0.067 15.155 ↑ 60.7 28 1

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

10. 1.711 14.722 ↑ 9.3 183 1

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

11. 2.594 2.859 ↑ 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.465..2.859 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[])))
  • Heap Blocks: exact=1681
12. 0.265 0.265 ↑ 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.265..0.265 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[])))
13. 10.152 10.152 ↓ 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.006..0.006 rows=0 loops=1,692)

  • Filter: (key = ANY ('{"fabric finishing"}'::text[]))
  • Rows Removed by Filter: 8
14. 0.366 0.366 ↓ 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.002..0.002 rows=0 loops=183)

  • Filter: (value = ANY ('{seam}'::text[]))
  • Rows Removed by Filter: 1
15. 0.038 15.167 ↑ 60.7 28 1

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

16. 1.705 14.763 ↑ 9.3 183 1

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

17. 2.566 2.906 ↑ 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.739..2.906 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[])))
  • Heap Blocks: exact=1681
18. 0.340 0.340 ↑ 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.340..0.340 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[])))
19. 10.152 10.152 ↓ 0.0 0 1,692

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

  • Filter: (key = ANY ('{"fabric finishing"}'::text[]))
  • Rows Removed by Filter: 8
20. 0.366 0.366 ↓ 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.002..0.002 rows=0 loops=183)

  • Filter: (value = ANY ('{seam}'::text[]))
  • Rows Removed by Filter: 1