explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EXzK

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 713.313 ↓ 2.6 514 1

Append (cost=127,543.11..621,223.52 rows=201 width=72) (actual time=0.002..713.313 rows=514 loops=1)

2.          

CTE filtered_products

3. 100.693 109.654 ↓ 2.6 37,025 1

Bitmap Heap Scan on products (cost=7,767.75..127,543.11 rows=14,501 width=158) (actual time=12.881..109.654 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=23567
4. 8.961 8.961 ↑ 1.4 55,490 1

Bitmap Index Scan on get_novelties_idx (cost=0.00..7,764.12 rows=75,149 width=0) (actual time=8.961..8.961 rows=55,490 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.063 713.270 ↓ 2.6 513 1

Subquery Scan on *SELECT* 2 (cost=493,675.40..493,680.40 rows=200 width=72) (actual time=712.986..713.270 rows=513 loops=1)

7. 11.634 713.207 ↓ 2.6 513 1

HashAggregate (cost=493,675.40..493,678.40 rows=200 width=88) (actual time=712.984..713.207 rows=513 loops=1)

  • Group Key: categories._id
8. 73.124 701.573 ↑ 3.7 53,334 1

HashAggregate (cost=488,675.40..490,675.40 rows=200,000 width=48) (actual time=687.664..701.573 rows=53,334 loops=1)

  • Group Key: categories._id, filtered_products.__group
9.          

CTE full_categories

10. 5.392 136.018 ↑ 1.9 531 1

Seq Scan on categories categories_1 (cost=0.00..251,751.50 rows=1,000 width=48) (actual time=0.492..136.018 rows=531 loops=1)

11.          

SubPlan (for Seq Scan)

12. 130.626 130.626 ↑ 32.0 1 531

Seq Scan on categories z (cost=0.00..251.50 rows=32 width=16) (actual time=0.235..0.246 rows=1 loops=531)

  • Filter: ("parentCategoryId" = categories_1._id)
  • Rows Removed by Filter: 530
13. 101.712 628.449 ↑ 4.9 130,580 1

Hash Join (cost=187,485.96..233,745.32 rows=635,715 width=48) (actual time=404.567..628.449 rows=130,580 loops=1)

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

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

15. 121.892 390.242 ↑ 11.5 328,132 1

Hash (cost=114,824.14..114,824.14 rows=3,757,826 width=32) (actual time=390.242..390.242 rows=328,132 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 5372kB
16. 43.125 268.350 ↑ 11.5 328,132 1

Nested Loop (cost=261.92..114,824.14 rows=3,757,826 width=32) (actual time=0.829..268.350 rows=328,132 loops=1)

17. 0.833 138.141 ↑ 1.9 531 1

Hash Join (cost=261.50..284.14 rows=1,000 width=48) (actual time=0.801..138.141 rows=531 loops=1)

  • Hash Cond: (full_categories._id = categories._id)
18. 137.012 137.012 ↑ 1.9 531 1

CTE Scan on full_categories (cost=0.00..20.00 rows=1,000 width=48) (actual time=0.497..137.012 rows=531 loops=1)

19. 0.097 0.296 ↑ 1.9 531 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
20. 0.199 0.199 ↑ 1.9 531 1

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

21. 87.084 87.084 ↑ 6.1 618 531

Index Only Scan using product_categories_pkey on product_categories (cost=0.42..76.96 rows=3,758 width=32) (actual time=0.009..0.164 rows=618 loops=531)

  • Index Cond: ("categoryId" = ANY (full_categories.full_ids))
  • Heap Fetches: 47380
Planning time : 0.843 ms
Execution time : 715.738 ms