explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f3hn : Subquery

Settings
# exclusive inclusive rows x rows loops node
1. 268.480 83,802.414 ↓ 10.0 10 1

HashAggregate (cost=3,564.61..3,564.62 rows=1 width=16) (actual time=83,802.412..83,802.414 rows=10 loops=1)

  • Group Key: catalogite1_.id
2. 41,399.005 83,533.934 ↓ 1,031.2 1,352,880 1

Merge Join (cost=1,773.80..3,561.33 rows=1,312 width=16) (actual time=64.764..83,533.934 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. 3.392 6.510 ↓ 20.0 5,643 1

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

  • Sort Key: service2_.id
  • Sort Method: quicksort Memory: 458kB
4. 1.002 3.118 ↓ 20.0 5,643 1

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

  • Hash Cond: (entitledse6_.entitlement_id = entitlemen7_.id)
5. 1.369 2.041 ↓ 20.0 5,643 1

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

  • Hash Cond: (entitledse6_.service_id = service2_.id)
6. 0.662 0.662 ↑ 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.005..0.662 rows=5,643 loops=1)

7. 0.003 0.010 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.007 0.007 ↑ 1.0 1 1

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

  • Filter: (((tenant_id)::text = 'intel-1'::text) AND ((status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 1
9. 0.003 0.075 ↑ 1.0 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.020 0.072 ↑ 1.0 12 1

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

Bitmap Index Scan on cat_entitlement_pkey (cost=0.00..47.48 rows=12 width=0) (actual time=0.052..0.052 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[]))
12. 42,091.356 42,128.419 ↓ 56,559.3 318,428,848 1

Sort (cost=1,429.34..1,443.41 rows=5,630 width=123) (actual time=60.587..42,128.419 rows=318,428,848 loops=1)

  • Sort Key: catalogite1_.service_id
  • Sort Method: external sort Disk: 2,600kB
13. 9.090 37.063 ↓ 10.0 56,430 1

Hash Left Join (cost=316.66..1,078.62 rows=5,630 width=123) (actual time=0.751..37.063 rows=56,430 loops=1)

  • Hash Cond: (entitledca4_.entitlement_id = entitlemen5_.id)
14. 7.621 27.934 ↓ 10.0 56,430 1

Nested Loop Left Join (cost=228.46..969.19 rows=5,630 width=86) (actual time=0.707..27.934 rows=56,430 loops=1)

15. 0.027 0.063 ↓ 10.0 10 1

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

16. 0.036 0.036 ↓ 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.012..0.036 rows=10 loops=1)

  • Index Cond: ((is_requestable = true) AND ((status)::text = 'PUBLISHED'::text) AND ((tenant_id)::text = 'intel-1'::text))
  • Filter: is_requestable
17. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=10)

  • Index Cond: (id = (catalogite1_.subtenant_id)::text)
  • Heap Fetches: 0
18. 14.110 20.250 ↓ 1.0 5,643 10

Bitmap Heap Scan on cat_entitlement_catalogitems entitledca4_ (cost=228.05..896.42 rows=5,630 width=33) (actual time=0.675..2.025 rows=5,643 loops=10)

  • Recheck Cond: (catalogite1_.id = catalogitem_id)
  • Heap Blocks: exact=5,980
19. 6.140 6.140 ↓ 1.0 5,643 10

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

  • Index Cond: (catalogite1_.id = catalogitem_id)
20. 0.014 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: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.008 0.025 ↑ 1.0 12 1

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

Bitmap Index Scan on cat_entitlement_pkey (cost=0.00..47.48 rows=12 width=0) (actual time=0.017..0.017 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 : 0.989 ms
Execution time : 83,803.757 ms