explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AJE : work_mem='512mb'

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 55,893.045 ↓ 10.0 10 1

Limit (cost=3,570.08..3,580.33 rows=1 width=4,112) (actual time=55,892.944..55,893.045 rows=10 loops=1)

2. 0.054 55,893.042 ↓ 10.0 10 1

Nested Loop (cost=3,570.08..3,580.33 rows=1 width=4,112) (actual time=55,892.943..55,893.042 rows=10 loops=1)

  • Join Filter: (catalogite0_.id = catalogite1_.id)
  • Rows Removed by Join Filter: 200
3. 237.235 55,892.888 ↓ 10.0 10 1

HashAggregate (cost=3,570.08..3,570.09 rows=1 width=16) (actual time=55,892.887..55,892.888 rows=10 loops=1)

  • Group Key: catalogite1_.id
4. 39,577.510 55,655.653 ↓ 1,028.8 1,352,880 1

Merge Join (cost=1,775.15..3,566.80 rows=1,315 width=16) (actual time=66.525..55,655.653 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: 317081610
5. 2.934 6.021 ↓ 20.0 5,643 1

Sort (cost=344.47..345.17 rows=282 width=69) (actual time=4.019..6.021 rows=5,643 loops=1)

  • Sort Key: service2_.id
  • Sort Method: quicksort Memory: 458kB
6. 0.937 3.087 ↓ 20.0 5,643 1

Hash Left Join (cost=98.51..332.99 rows=282 width=69) (actual time=0.108..3.087 rows=5,643 loops=1)

  • Hash Cond: (entitledse6_.entitlement_id = entitlemen7_.id)
7. 1.330 2.089 ↓ 20.0 5,643 1

Hash Right Join (cost=10.31..243.72 rows=282 width=32) (actual time=0.038..2.089 rows=5,643 loops=1)

  • Hash Cond: (entitledse6_.service_id = service2_.id)
8. 0.740 0.740 ↑ 1.0 5,643 1

Seq Scan on cat_entitlement_services entitledse6_ (cost=0.00..209.43 rows=5,643 width=32) (actual time=0.007..0.740 rows=5,643 loops=1)

9. 0.005 0.019 ↑ 1.0 1 1

Hash (cost=10.30..10.30 rows=1 width=16) (actual time=0.019..0.019 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on cat_service service2_ (cost=0.00..10.30 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: (((tenant_id)::text = 'intel-1'::text) AND ((status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 1
11. 0.004 0.061 ↑ 1.0 12 1

Hash (cost=88.05..88.05 rows=12 width=53) (actual time=0.061..0.061 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.020 0.057 ↑ 1.0 12 1

Bitmap Heap Scan on cat_entitlement entitlemen7_ (cost=47.48..88.05 rows=12 width=53) (actual time=0.041..0.057 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
13. 0.037 0.037 ↑ 1.0 12 1

Bitmap Index Scan on cat_entitlement_pkey (cost=0.00..47.48 rows=12 width=0) (actual time=0.037..0.037 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[]))
14. 16,026.465 16,072.122 ↓ 56,429.0 318,428,848 1

Sort (cost=1,430.68..1,444.79 rows=5,643 width=123) (actual time=62.384..16,072.122 rows=318,428,848 loops=1)

  • Sort Key: catalogite1_.service_id
  • Sort Method: quicksort Memory: 5953kB
15. 11.684 45.657 ↓ 10.0 56,430 1

Hash Left Join (cost=316.76..1,079.06 rows=5,643 width=123) (actual time=0.763..45.657 rows=56,430 loops=1)

  • Hash Cond: (entitledca4_.entitlement_id = entitlemen5_.id)
16. 9.396 33.934 ↓ 10.0 56,430 1

Nested Loop Left Join (cost=228.56..969.58 rows=5,643 width=86) (actual time=0.718..33.934 rows=56,430 loops=1)

17. 0.024 0.078 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.42..16.46 rows=1 width=69) (actual time=0.017..0.078 rows=10 loops=1)

18. 0.044 0.044 ↓ 10.0 10 1

Index Scan using cat_catalogitem_is_requestable_status_tenant_id_idx on cat_catalogitem catalogite1_ (cost=0.14..8.16 rows=1 width=130) (actual time=0.013..0.044 rows=10 loops=1)

  • Index Cond: ((is_requestable = true) AND ((status)::text = 'PUBLISHED'::text) AND ((tenant_id)::text = 'intel-1'::text))
  • Filter: is_requestable
19. 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
20. 17.180 24.460 ↑ 1.0 5,643 10

Bitmap Heap Scan on cat_entitlement_catalogitems entitledca4_ (cost=228.15..896.69 rows=5,643 width=33) (actual time=0.803..2.446 rows=5,643 loops=10)

  • Recheck Cond: (catalogite1_.id = catalogitem_id)
  • Heap Blocks: exact=5980
21. 7.280 7.280 ↑ 1.0 5,643 10

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

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

Hash (cost=88.05..88.05 rows=12 width=53) (actual time=0.039..0.039 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.013 0.032 ↑ 1.0 12 1

Bitmap Heap Scan on cat_entitlement entitlemen5_ (cost=47.48..88.05 rows=12 width=53) (actual time=0.022..0.032 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
24. 0.019 0.019 ↑ 1.0 12 1

Bitmap Index Scan on cat_entitlement_pkey (cost=0.00..47.48 rows=12 width=0) (actual time=0.019..0.019 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[]))
25. 0.100 0.100 ↓ 2.1 21 10

Seq Scan on cat_catalogitem catalogite0_ (cost=0.00..10.10 rows=10 width=4,112) (actual time=0.002..0.010 rows=21 loops=10)

Planning time : 1.345 ms
Execution time : 55,893.946 ms