explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N6S5

Settings
# exclusive inclusive rows x rows loops node
1. 1,531.383 62,603.093 ↓ 0.0 0 1

Nested Loop (cost=1,098,143.98..9,174,906.93 rows=321,950 width=463) (actual time=62,603.093..62,603.093 rows=0 loops=1)

  • Join Filter: ("Extent2"."CustomerAccountId" = "Extent1"."CustomerAccountId")
2. 2,916.482 11,582.046 ↓ 7.6 3,093,104 1

Hash Join (cost=1,098,143.42..1,409,871.41 rows=409,063 width=48) (actual time=7,381.043..11,582.046 rows=3,093,104 loops=1)

  • Hash Cond: ("Extent2"."CustomerAccountId" = "Extent3"."CustomerAccountId")
3. 1,506.748 1,506.748 ↓ 1.1 3,195,797 1

Seq Scan on "Account" "Extent2" (cost=0.00..246,628.71 rows=3,024,639 width=8) (actual time=218.366..1,506.748 rows=3,195,797 loops=1)

  • Filter: (("TenantId" = 24871) AND ("CustomerSetId" = 1))
  • Rows Removed by Filter: 2726433
4. 810.186 7,158.816 ↓ 2.4 3,093,104 1

Hash (cost=1,071,584.66..1,071,584.66 rows=1,307,501 width=40) (actual time=7,158.816..7,158.816 rows=3,093,104 loops=1)

  • Buckets: 1048576 (originally 1048576) Batches: 8 (originally 2) Memory Usage: 57345kB
5. 605.937 6,348.630 ↓ 2.4 3,093,104 1

Unique (cost=1,049,464.83..1,071,584.66 rows=1,307,501 width=40) (actual time=5,111.937..6,348.630 rows=3,093,104 loops=1)

6. 2,237.200 5,742.693 ↓ 1.4 3,093,104 1

Sort (cost=1,049,464.83..1,054,994.79 rows=2,211,983 width=40) (actual time=5,111.935..5,742.693 rows=3,093,104 loops=1)

  • Sort Key: ""Extent3"".""CustomerAccountId"", ""Extent3"".""TenantId"", ((""Extent3"".""UserId"")::text)
  • Sort Method: external merge Disk: 253904kB
7. 3,505.493 3,505.493 ↓ 1.4 3,093,104 1

Seq Scan on "UserAttribute" "Extent3" (cost=0.00..755,869.01 rows=2,211,983 width=40) (actual time=447.077..3,505.493 rows=3,093,104 loops=1)

  • Filter: (("TenantId" = 24871) AND (("FullyQualifiedName")::text = 'tenant~preferred-store'::text))
  • Rows Removed by Filter: 14669692
8. 12,372.416 49,489.664 ↓ 0.0 0 3,093,104

Index Scan using "PK_User" on "User" "Extent1" (cost=0.56..18.97 rows=1 width=463) (actual time=0.016..0.016 rows=0 loops=3,093,104)

  • Index Cond: (("TenantId" = 24871) AND ("CustomerAccountId" = "Extent3"."CustomerAccountId") AND (("UserId")::text = ("Extent3"."UserId")::text))
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 1
9.          

SubPlan (for Index Scan)

10. 0.000 37,117.248 ↓ 0.0 0 3,093,104

Merge Semi Join (cost=1.12..17.19 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=3,093,104)

  • Merge Cond: ("Extent4"."UserAttributeId" = "Extent5"."UserAttributeId")
11. 12,372.416 12,372.416 ↑ 1.0 1 3,093,104

Index Only Scan using "PK_UserAttribute" on "UserAttribute" "Extent4" (cost=0.56..8.58 rows=1 width=44) (actual time=0.004..0.004 rows=1 loops=3,093,104)

  • Index Cond: (("TenantId" = "Extent1"."TenantId") AND ("CustomerAccountId" = "Extent1"."CustomerAccountId") AND ("UserId" = ("Extent1"."UserId")::text))
  • Heap Fetches: 3093104
12. 24,744.832 24,744.832 ↓ 0.0 0 3,093,104

Index Scan using "PK_UserAttributeValue" on "UserAttributeValue" "Extent5" (cost=0.56..8.59 rows=1 width=44) (actual time=0.008..0.008 rows=0 loops=3,093,104)

  • Index Cond: (("TenantId" = "Extent1"."TenantId") AND ("CustomerAccountId" = "Extent1"."CustomerAccountId") AND (("UserId")::text = ("Extent1"."UserId")::text))
  • Filter: (("NumberValue" = '13712'::numeric) OR ("StringValue" = 'tenant~preferred-store'::citext))
  • Rows Removed by Filter: 4
Planning time : 1.785 ms
Execution time : 62,653.022 ms