explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OvbG6

Settings
# exclusive inclusive rows x rows loops node
1. 437.497 25,780.584 ↑ 28.0 4,438 1

Bitmap Heap Scan on "Nomenclatures" (cost=85,862.16..267,892,525.19 rows=124,362 width=374) (actual time=2,631.144..25,780.584 rows=4,438 loops=1)

  • Recheck Cond: (client = '6486d26c-c43b-4da8-a182-bd937683fcd1'::uuid)
  • Rows Removed by Index Recheck: 849307
  • Filter: (used AND ((hashed SubPlan 3) OR (hashed SubPlan 4)))
  • Rows Removed by Filter: 155702
  • Heap Blocks: exact=34369 lossy=55310
2.          

CTE nomenclaturesWithCategories

3. 0.182 2,581.676 ↑ 1.7 484 1

Nested Loop (cost=39,090.40..80,430.92 rows=821 width=32) (actual time=2,083.415..2,581.676 rows=484 loops=1)

4. 0.019 0.019 ↑ 1.0 1 1

Index Scan using "Clients_pkey1" on "Clients" (cost=0.28..8.30 rows=1 width=265) (actual time=0.016..0.019 rows=1 loops=1)

  • Index Cond: (id = '6486d26c-c43b-4da8-a182-bd937683fcd1'::uuid)
5. 511.299 2,581.475 ↑ 21.2 484 1

Bitmap Heap Scan on "Nomenclatures" "Nomenclatures_1" (cost=39,090.11..80,320.02 rows=10,260 width=64) (actual time=2,083.386..2,581.475 rows=484 loops=1)

  • Recheck Cond: ((client = '6486d26c-c43b-4da8-a182-bd937683fcd1'::uuid) AND (endpoint = CASE WHEN (COALESCE(("Clients".data ->> 'separateNomenclature'::text), 'false'::text) = 'true'::text) THEN '53bf2f12-5ba8-44be-b12b-a49565e63548'::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END))
  • Rows Removed by Index Recheck: 849307
  • Filter: (used AND ((((data ->> 'monthSales'::text))::json ->> '53bf2f12-5ba8-44be-b12b-a49565e63548'::text) IS NOT NULL) AND ((((data ->> 'monthSales'::text))::json ->> '53bf2f12-5ba8-44be-b12b-a49565e63548'::text) <> '0'::text))
  • Rows Removed by Filter: 159656
  • Heap Blocks: exact=34369 lossy=55310
6. 10.335 2,070.176 ↓ 0.0 0 1

BitmapAnd (cost=39,090.11..39,090.11 rows=10,834 width=0) (actual time=2,070.176..2,070.176 rows=0 loops=1)

7. 33.700 33.700 ↑ 1.1 160,140 1

Bitmap Index Scan on "Nomenclatures_client_index" (cost=0.00..5,360.69 rows=173,351 width=0) (actual time=33.700..33.700 rows=160,140 loops=1)

  • Index Cond: (client = '6486d26c-c43b-4da8-a182-bd937683fcd1'::uuid)
8. 2,026.141 2,026.141 ↓ 14.4 15,626,192 1

Bitmap Index Scan on "Nomenclatures_endpoint_index" (cost=0.00..33,685.56 rows=1,087,066 width=0) (actual time=2,026.141..2,026.141 rows=15,626,192 loops=1)

  • Index Cond: (endpoint = CASE WHEN (COALESCE(("Clients".data ->> 'separateNomenclature'::text), 'false'::text) = 'true'::text) THEN '53bf2f12-5ba8-44be-b12b-a49565e63548'::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
9. 33.694 33.694 ↑ 1.1 160,140 1

Bitmap Index Scan on "Nomenclatures_client_index" (cost=0.00..5,360.69 rows=173,351 width=0) (actual time=33.694..33.694 rows=160,140 loops=1)

  • Index Cond: (client = '6486d26c-c43b-4da8-a182-bd937683fcd1'::uuid)
10.          

SubPlan (forBitmap Heap Scan)

11. 4.438 22,726.998 ↓ 0.0 0 4,438

Limit (cost=2,149.25..2,149.25 rows=1 width=8) (actual time=5.121..5.121 rows=0 loops=4,438)

12. 22.190 22,722.560 ↓ 0.0 0 4,438

Sort (cost=2,149.25..2,149.25 rows=1 width=8) (actual time=5.120..5.120 rows=0 loops=4,438)

  • Sort Key: cp.date DESC
  • Sort Method: quicksort Memory: 25kB
13. 13.314 22,700.370 ↓ 0.0 0 4,438

Nested Loop (cost=411.97..2,149.24 rows=1 width=8) (actual time=5.115..5.115 rows=0 loops=4,438)

14. 22,687.056 22,687.056 ↓ 0.0 0 4,438

Seq Scan on "CampaignNomenclature" cn (cost=0.00..901.24 rows=3 width=16) (actual time=5.112..5.112 rows=0 loops=4,438)

  • Filter: (nomenclature = "Nomenclatures".id)
  • Rows Removed by Filter: 45379
15. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "CampaignProgress" cp (cost=411.97..415.99 rows=1 width=27) (never executed)

  • Recheck Cond: (("SKU" = cn."SKU") AND (endpoint = '53bf2f12-5ba8-44be-b12b-a49565e63548'::uuid))
16. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=411.97..411.97 rows=1 width=0) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "CampaignProgress_SKU_idx" (cost=0.00..11.50 rows=392 width=0) (never executed)

  • Index Cond: ("SKU" = cn."SKU")
18. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "CampaignProgress_endpoint_campaign_SKU_idx" (cost=0.00..397.78 rows=9,763 width=0) (never executed)

  • Index Cond: (endpoint = '53bf2f12-5ba8-44be-b12b-a49565e63548'::uuid)
19. 2,582.194 2,582.194 ↑ 1.7 484 1

CTE Scan on "nomenclaturesWithCategories" (cost=0.00..16.42 rows=821 width=16) (actual time=2,083.420..2,582.194 rows=484 loops=1)

20. 0.163 0.201 ↓ 2.0 394 1

HashAggregate (cost=18.47..20.47 rows=200 width=16) (actual time=0.159..0.201 rows=394 loops=1)

  • Group Key: "nomenclaturesWithCategories_1".category
21. 0.038 0.038 ↑ 1.7 484 1

CTE Scan on "nomenclaturesWithCategories" "nomenclaturesWithCategories_1" (cost=0.00..16.42 rows=821 width=16) (actual time=0.001..0.038 rows=484 loops=1)