explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fcJK

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 93,520.034 ↓ 2.6 514 1

Append (cost=127,543.11..1,623,145.56 rows=201 width=72) (actual time=0.001..93,520.034 rows=514 loops=1)

2.          

CTE filtered_products

3. 98.424 108.246 ↓ 2.6 37,025 1

Bitmap Heap Scan on products (cost=7,767.75..127,543.11 rows=14,501 width=62) (actual time=13.705..108.246 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=23553
4. 9.822 9.822 ↑ 1.4 55,495 1

Bitmap Index Scan on get_novelties_idx (cost=0.00..7,764.12 rows=75,149 width=0) (actual time=9.822..9.822 rows=55,495 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.100 93,519.974 ↓ 2.6 513 1

Subquery Scan on *SELECT* 2 (cost=1,470,058.94..1,495,602.43 rows=200 width=72) (actual time=93,395.815..93,519.974 rows=513 loops=1)

7. 8.374 93,519.874 ↓ 2.6 513 1

GroupAggregate (cost=1,470,058.94..1,495,600.43 rows=200 width=88) (actual time=93,395.813..93,519.874 rows=513 loops=1)

  • Group Key: categories._id
8. 58.806 93,511.500 ↑ 3.6 55,840 1

Unique (cost=1,470,058.94..1,492,597.43 rows=200,000 width=48) (actual time=93,395.770..93,511.500 rows=55,840 loops=1)

9. 4,734.419 93,452.694 ↑ 7.3 411,911 1

Sort (cost=1,470,058.94..1,477,571.77 rows=3,005,133 width=48) (actual time=93,395.760..93,452.694 rows=411,911 loops=1)

  • Sort Key: categories._id, filtered_products.__group
  • Sort Method: external sort Disk: 29024kB
10. 24,975.343 88,718.275 ↑ 7.3 411,911 1

Merge Right Join (cost=6,937.72..1,054,276.19 rows=3,005,133 width=48) (actual time=92.997..88,718.275 rows=411,911 loops=1)

  • Merge Cond: ("additionalSubCategories"."parentCategoryId" = categories._id)
  • Filter: ((product_categories."categoryId" = categories._id) OR (product_categories."categoryId" = "subCategories"._id) OR (product_categories."categoryId" = "additionalSubCategories"."childCategoryId") OR (product_categories."categoryId" = "_subCategories"._id))
  • Rows Removed by Filter: 121873463
11. 2.210 4.778 ↑ 15.2 53 1

Nested Loop Left Join (cost=0.14..950.33 rows=804 width=48) (actual time=0.755..4.778 rows=53 loops=1)

  • Join Filter: ("_subCategories"."parentCategoryId" = "additionalSubCategories"."childCategoryId")
  • Rows Removed by Join Filter: 24418
12. 0.406 0.406 ↑ 1.0 46 1

Index Only Scan using additional_child_categories_uniq_key on additional_child_categories "additionalSubCategories" (cost=0.14..8.83 rows=46 width=32) (actual time=0.020..0.406 rows=46 loops=1)

  • Heap Fetches: 48
13. 1.624 2.162 ↑ 1.9 531 46

Materialize (cost=0.00..254.00 rows=1,000 width=32) (actual time=0.001..0.047 rows=531 loops=46)

14. 0.538 0.538 ↑ 1.9 531 1

Seq Scan on categories "_subCategories" (cost=0.00..249.00 rows=1,000 width=32) (actual time=0.016..0.538 rows=531 loops=1)

15. 45,278.861 63,738.154 ↓ 3.9 122,285,374 1

Materialize (cost=6,937.58..475,126.43 rows=31,119,344 width=80) (actual time=88.951..63,738.154 rows=122,285,374 loops=1)

16. 11,483.615 18,459.293 ↓ 2.1 66,102,117 1

Nested Loop (cost=6,937.58..397,328.07 rows=31,119,344 width=80) (actual time=88.949..18,459.293 rows=66,102,117 loops=1)

17. 1.264 3.369 ↑ 1.1 933 1

Merge Left Join (cost=597.66..614.72 rows=1,000 width=32) (actual time=0.851..3.369 rows=933 loops=1)

  • Merge Cond: (categories._id = "subCategories"."parentCategoryId")
18. 0.857 1.055 ↑ 1.9 531 1

Sort (cost=298.83..301.33 rows=1,000 width=16) (actual time=0.388..1.055 rows=531 loops=1)

  • Sort Key: categories._id
  • Sort Method: quicksort Memory: 49kB
19. 0.198 0.198 ↑ 1.9 531 1

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

20. 0.764 1.050 ↑ 2.3 428 1

Sort (cost=298.83..301.33 rows=1,000 width=32) (actual time=0.460..1.050 rows=428 loops=1)

  • Sort Key: "subCategories"."parentCategoryId
  • Sort Method: quicksort Memory: 63kB
21. 0.286 0.286 ↑ 1.9 531 1

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

22. 6,731.634 6,972.309 ↓ 2.3 70,849 933

Materialize (cost=6,339.92..7,803.65 rows=31,119 width=48) (actual time=0.095..7.473 rows=70,849 loops=933)

23. 37.763 240.675 ↓ 2.3 70,849 1

Hash Join (cost=6,339.92..7,648.05 rows=31,119 width=48) (actual time=88.092..240.675 rows=70,849 loops=1)

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

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

25. 40.139 73.676 ↓ 1.0 184,383 1

Hash (cost=4,040.52..4,040.52 rows=183,952 width=32) (actual time=73.676..73.676 rows=184,383 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13572kB
26. 33.537 33.537 ↓ 1.0 184,383 1

Seq Scan on product_categories (cost=0.00..4,040.52 rows=183,952 width=32) (actual time=0.015..33.537 rows=184,383 loops=1)

Planning time : 1.907 ms
Execution time : 94,554.304 ms