explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6WGU

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 631.622 ↓ 2.6 514 1

Append (cost=127,543.11..279,447.42 rows=201 width=72) (actual time=0.001..631.622 rows=514 loops=1)

2.          

CTE filtered_products

3. 100.598 109.658 ↓ 2.6 37,025 1

Bitmap Heap Scan on products (cost=7,767.75..127,543.11 rows=14,501 width=62) (actual time=12.999..109.658 rows=37,025 loops=1)

  • Recheck Cond: (visible AND "visibleInLists")
  • Filter: ("existsInSupplierPrice" AND ((quantity > 5) OR "onDemand" OR ("beingDeliveredCount" > 0)))
  • Rows Removed by Filter: 18246
  • Heap Blocks: exact=23546
4. 9.060 9.060 ↑ 1.4 55,463 1

Bitmap Index Scan on get_novelties_idx (cost=0.00..7,764.12 rows=75,149 width=0) (actual time=9.060..9.060 rows=55,463 loops=1)

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=72) (actual time=0.001..0.001 rows=1 loops=1)

6. 0.061 631.578 ↓ 2.6 513 1

Subquery Scan on *SELECT* 2 (cost=151,899.30..151,904.30 rows=200 width=72) (actual time=631.292..631.578 rows=513 loops=1)

7. 11.700 631.517 ↓ 2.6 513 1

HashAggregate (cost=151,899.30..151,902.30 rows=200 width=88) (actual time=631.291..631.517 rows=513 loops=1)

  • Group Key: categories._id
8. 77.022 619.817 ↑ 1.2 53,334 1

HashAggregate (cost=150,344.10..150,966.18 rows=62,208 width=48) (actual time=606.971..619.817 rows=53,334 loops=1)

  • Group Key: categories._id, filtered_products.__group
9. 81.195 542.795 ↓ 2.1 131,781 1

Hash Join (cost=144,973.36..150,033.06 rows=62,208 width=48) (actual time=341.064..542.795 rows=131,781 loops=1)

  • Hash Cond: (filtered_products._id = product_categories."productId")
10. 133.898 133.898 ↓ 2.6 37,025 1

CTE Scan on filtered_products (cost=0.00..290.02 rows=14,501 width=48) (actual time=13.004..133.898 rows=37,025 loops=1)

11. 111.180 327.702 ↑ 1.1 330,311 1

Hash (cost=137,862.86..137,862.86 rows=367,720 width=32) (actual time=327.702..327.702 rows=330,311 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 12345kB
12. 56.188 216.522 ↑ 1.1 330,311 1

Nested Loop (cost=368.22..137,862.86 rows=367,720 width=32) (actual time=0.526..216.522 rows=330,311 loops=1)

13. 0.798 1.724 ↑ 1.1 933 1

Hash Right Join (cost=261.50..513.14 rows=1,000 width=32) (actual time=0.472..1.724 rows=933 loops=1)

  • Hash Cond: ("subCategories"."parentCategoryId" = categories._id)
14. 0.470 0.470 ↑ 1.9 531 1

Seq Scan on categories "subCategories" (cost=0.00..249.00 rows=1,000 width=32) (actual time=0.004..0.470 rows=531 loops=1)

15. 0.098 0.456 ↑ 1.9 531 1

Hash (cost=249.00..249.00 rows=1,000 width=16) (actual time=0.456..0.456 rows=531 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
16. 0.358 0.358 ↑ 1.9 531 1

Seq Scan on categories (cost=0.00..249.00 rows=1,000 width=16) (actual time=0.020..0.358 rows=531 loops=1)

17. 128.754 158.610 ↑ 2.1 354 933

Bitmap Heap Scan on product_categories (cost=106.72..129.77 rows=758 width=32) (actual time=0.042..0.170 rows=354 loops=933)

  • Recheck Cond: (("categoryId" = categories._id) OR ("categoryId" = "subCategories"._id))
  • Heap Blocks: exact=68186
18. 0.933 29.856 ↓ 0.0 0 933

BitmapOr (cost=106.72..106.72 rows=759 width=0) (actual time=0.032..0.032 rows=0 loops=933)

19. 17.727 17.727 ↑ 1.9 201 933

Bitmap Index Scan on product_categories_category_id_idx (cost=0.00..7.37 rows=379 width=0) (actual time=0.019..0.019 rows=201 loops=933)

  • Index Cond: ("categoryId" = categories._id)
20. 11.196 11.196 ↑ 2.4 155 933

Bitmap Index Scan on product_categories_category_id_idx (cost=0.00..7.37 rows=379 width=0) (actual time=0.012..0.012 rows=155 loops=933)

  • Index Cond: ("categoryId" = "subCategories"._id)
Planning time : 0.884 ms
Execution time : 633.871 ms