explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yTOW : work_mem='512mb'

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 231.865 55,505.701 ↓ 10.0 10 1

HashAggregate (cost=9,468.04..9,468.05 rows=1 width=16) (actual time=55,505.699..55,505.701 rows=10 loops=1)

  • Group Key: catalogite1_.id
2. 39,299.863 55,273.836 ↓ 215.9 1,352,880 1

Merge Join (cost=1,118.68..9,452.37 rows=6,267 width=16) (actual time=47.612..55,273.836 rows=1,352,880 loops=1)

  • Merge Cond: (service2_.id = catalogite1_.service_id)
  • Join Filter: (((entitlemen7_.id IS NOT NULL) AND ((catalogsub3_.id IS NULL) OR ((entitlemen7_.subtenant_id)::text = (catalogsub3_.id)::text)) AND ((entitlemen5_.id IS NULL) OR ((entitlemen5_.subtenant_id)::text <> (entitlemen7_.subtenant_id)::text))) OR ((entitlemen5_.id IS NOT NULL) AND ((catalogsub3_.id IS NULL) OR ((entitlemen5_.subtenant_id)::text = (catalogsub3_.id)::text)) AND (NOT entitledca4_.is_hidden)))
  • Rows Removed by Join Filter: 317,081,610
3. 8.978 25.565 ↓ 2.0 5,643 1

Nested Loop Left Join (cost=81.44..283.73 rows=2,822 width=69) (actual time=0.089..25.565 rows=5,643 loops=1)

  • Join Filter: (service2_.id = entitledse6_.service_id)
4. 0.023 0.023 ↑ 1.0 1 1

Index Scan using cat_service_pkey on cat_service service2_ (cost=0.13..12.17 rows=1 width=16) (actual time=0.011..0.023 rows=1 loops=1)

  • Filter: (((tenant_id)::text = 'intel-1'::text) AND ((status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 1
5. 9.358 16.564 ↑ 1.0 5,643 1

Hash Left Join (cost=81.32..201.03 rows=5,643 width=69) (actual time=0.068..16.564 rows=5,643 loops=1)

  • Hash Cond: (entitledse6_.entitlement_id = entitlemen7_.id)
6. 7.152 7.152 ↑ 1.0 5,643 1

Seq Scan on cat_entitlement_services entitledse6_ (cost=0.00..98.43 rows=5,643 width=32) (actual time=0.009..7.152 rows=5,643 loops=1)

7. 0.003 0.054 ↑ 1.0 12 1

Hash (cost=81.17..81.17 rows=12 width=53) (actual time=0.054..0.054 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.025 0.051 ↑ 1.0 12 1

Bitmap Heap Scan on cat_entitlement entitlemen7_ (cost=43.48..81.17 rows=12 width=53) (actual time=0.033..0.051 rows=12 loops=1)

  • Recheck Cond: (id = ANY ('{505e03e5-7370-42c2-a26e-bdb2df593934,508da3b6-7147-4b16-971f-6e6476b8ef44,6c68fbd2-7cc4-4b7c-85c1-617b69578ab9,6c9e5ff0-a073-4923-a5ec-b47f5e4c120a,961bee54-e9d6-402c-a763-c3937b03402f,2f113c9a-9e2f-47d8-beda-df0e05faa167,471bca1e-a112-4842-bdfc-252b8848b862,482ba515-2197-4fdb-a74b-37d9a0795c4e,872038e4-766a-4b93-bf95-aa2735e7f942,fd6345fc-8799-42a0-83d5-0234e450e397,7378e830-5271-482f-b73b-7ce4232b000d,6aeafe3b-aac9-4c67-8895-aa77da7a7d6b}'::uuid[]))
  • Heap Blocks: exact=10
9. 0.026 0.026 ↑ 1.0 12 1

Bitmap Index Scan on cat_entitlement_pkey (cost=0.00..43.48 rows=12 width=0) (actual time=0.026..0.026 rows=12 loops=1)

  • Index Cond: (id = ANY ('{505e03e5-7370-42c2-a26e-bdb2df593934,508da3b6-7147-4b16-971f-6e6476b8ef44,6c68fbd2-7cc4-4b7c-85c1-617b69578ab9,6c9e5ff0-a073-4923-a5ec-b47f5e4c120a,961bee54-e9d6-402c-a763-c3937b03402f,2f113c9a-9e2f-47d8-beda-df0e05faa167,471bca1e-a112-4842-bdfc-252b8848b862,482ba515-2197-4fdb-a74b-37d9a0795c4e,872038e4-766a-4b93-bf95-aa2735e7f942,fd6345fc-8799-42a0-83d5-0234e450e397,7378e830-5271-482f-b73b-7ce4232b000d,6aeafe3b-aac9-4c67-8895-aa77da7a7d6b}'::uuid[]))
10. 15,914.277 15,948.408 ↓ 118,507.2 318,428,848 1

Sort (cost=1,037.24..1,043.96 rows=2,687 width=123) (actual time=47.421..15,948.408 rows=318,428,848 loops=1)

  • Sort Key: catalogite1_.service_id
  • Sort Method: quicksort Memory: 5,953kB
11. 9.135 34.131 ↓ 21.0 56,430 1

Hash Left Join (cost=265.75..884.19 rows=2,687 width=123) (actual time=0.630..34.131 rows=56,430 loops=1)

  • Hash Cond: (entitledca4_.entitlement_id = entitlemen5_.id)
12. 7.152 24.967 ↓ 21.0 56,430 1

Nested Loop Left Join (cost=184.43..792.74 rows=2,687 width=86) (actual time=0.595..24.967 rows=56,430 loops=1)

13. 0.019 0.065 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.28..10.62 rows=1 width=69) (actual time=0.011..0.065 rows=10 loops=1)

14. 0.036 0.036 ↓ 10.0 10 1

Seq Scan on cat_catalogitem catalogite1_ (cost=0.00..2.31 rows=1 width=130) (actual time=0.009..0.036 rows=10 loops=1)

  • Filter: (is_requestable AND ((status)::text = 'PUBLISHED'::text) AND ((tenant_id)::text = 'intel-1'::text))
  • Rows Removed by Filter: 11
15. 0.010 0.010 ↓ 0.0 0 10

Index Only Scan using cat_subtenant_pkey on cat_subtenant catalogsub3_ (cost=0.28..8.30 rows=1 width=37) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = (catalogite1_.subtenant_id)::text)
  • Heap Fetches: 0
16. 12.450 17.750 ↑ 1.0 5,643 10

Bitmap Heap Scan on cat_entitlement_catalogitems entitledca4_ (cost=184.15..725.69 rows=5,643 width=33) (actual time=0.575..1.775 rows=5,643 loops=10)

  • Recheck Cond: (catalogite1_.id = catalogitem_id)
  • Heap Blocks: exact=4,710
17. 5.300 5.300 ↑ 1.0 5,643 10

Bitmap Index Scan on cat_entitlement_catalogitems_catalogitem_id_entitlement_id_key (cost=0.00..182.74 rows=5,643 width=0) (actual time=0.530..0.530 rows=5,643 loops=10)

  • Index Cond: (catalogite1_.id = catalogitem_id)
18. 0.007 0.029 ↑ 1.0 12 1

Hash (cost=81.17..81.17 rows=12 width=53) (actual time=0.029..0.029 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.006 0.022 ↑ 1.0 12 1

Bitmap Heap Scan on cat_entitlement entitlemen5_ (cost=43.48..81.17 rows=12 width=53) (actual time=0.018..0.022 rows=12 loops=1)

  • Recheck Cond: (id = ANY ('{505e03e5-7370-42c2-a26e-bdb2df593934,508da3b6-7147-4b16-971f-6e6476b8ef44,6c68fbd2-7cc4-4b7c-85c1-617b69578ab9,6c9e5ff0-a073-4923-a5ec-b47f5e4c120a,961bee54-e9d6-402c-a763-c3937b03402f,2f113c9a-9e2f-47d8-beda-df0e05faa167,471bca1e-a112-4842-bdfc-252b8848b862,482ba515-2197-4fdb-a74b-37d9a0795c4e,872038e4-766a-4b93-bf95-aa2735e7f942,fd6345fc-8799-42a0-83d5-0234e450e397,7378e830-5271-482f-b73b-7ce4232b000d,6aeafe3b-aac9-4c67-8895-aa77da7a7d6b}'::uuid[]))
  • Heap Blocks: exact=10
20. 0.016 0.016 ↑ 1.0 12 1

Bitmap Index Scan on cat_entitlement_pkey (cost=0.00..43.48 rows=12 width=0) (actual time=0.016..0.016 rows=12 loops=1)

  • Index Cond: (id = ANY ('{505e03e5-7370-42c2-a26e-bdb2df593934,508da3b6-7147-4b16-971f-6e6476b8ef44,6c68fbd2-7cc4-4b7c-85c1-617b69578ab9,6c9e5ff0-a073-4923-a5ec-b47f5e4c120a,961bee54-e9d6-402c-a763-c3937b03402f,2f113c9a-9e2f-47d8-beda-df0e05faa167,471bca1e-a112-4842-bdfc-252b8848b862,482ba515-2197-4fdb-a74b-37d9a0795c4e,872038e4-766a-4b93-bf95-aa2735e7f942,fd6345fc-8799-42a0-83d5-0234e450e397,7378e830-5271-482f-b73b-7ce4232b000d,6aeafe3b-aac9-4c67-8895-aa77da7a7d6b}'::uuid[]))
Planning time : 1.026 ms
Execution time : 55,505.903 ms