explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QfY

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.025 496.062 ↓ 1.3 94 1

CTE Scan on adv_parent (cost=12,924.41..12,930.91 rows=75 width=12) (actual time=495.416..496.062 rows=94 loops=1)

  • Filter: ((hashed SubPlan 4) OR (hashed SubPlan 5))
  • Rows Removed by Filter: 6
2.          

CTE adv_parent

3. 4.526 4.526 ↑ 1.0 100 1

Index Scan using pk_tbladcsummary_advertiser on tbladcsummary_advertiser sa (cost=0.56..408.75 rows=100 width=12) (actual time=0.061..4.526 rows=100 loops=1)

  • Index Cond: (advertiser_id = ANY ('{16355,28938,46520,57226,67771,72663,76831,77092,79681,89462,102198,103216,123280,135680,139744,188656,210198,219474,233249,245754,277886,294606,306291,309094,365211,372990,401033,404061,430286,433265,459640,469528,473209,494993,524042,529125,535542,542056,570092,570999,611244,628140,648846,653678,658102,666374,678159,682260,682761,701850,723985,734921,755516,758393,768113,779084,781559,792051,816421,839895,844131,856999,868194,876072,881203,896734,898265,914641,918013,920690,935826,990455,1002016,1031493,1038850,1052686,1062767,1075133,1080966,1086054,1098601,1110118,1123174,1130362,1141403,1145402,1147087,1153462,1153746,1180879,1206626,1207034,1226824,1240786,1241262,1242341,1262871,1289026,1299124,1313615}'::integer[]))
  • Filter: (NOT is_deleted)
4.          

CTE verified_parent

5. 0.000 488.213 ↓ 7.0 13,209 1

Nested Loop (cost=7.25..11,930.41 rows=1,891 width=4) (actual time=4.622..488.213 rows=13,209 loops=1)

6. 4.049 244.951 ↓ 6.0 27,256 1

Nested Loop (cost=6.81..9,579.13 rows=4,518 width=8) (actual time=4.577..244.951 rows=27,256 loops=1)

7. 0.101 4.602 ↑ 1.0 100 1

HashAggregate (cost=6.25..9.25 rows=100 width=4) (actual time=4.540..4.602 rows=100 loops=1)

  • Group Key: adv_parent_1.parent_company_id
8. 4.501 4.501 ↑ 1.0 100 1

CTE Scan on adv_parent adv_parent_1 (cost=0.00..6.00 rows=100 width=4) (actual time=0.000..4.501 rows=100 loops=1)

9. 236.300 236.300 ↓ 6.1 273 100

Index Scan using ix_tbladcsummary_advertiser_parent_company_id on tbladcsummary_advertiser sa_1 (cost=0.56..94.35 rows=45 width=8) (actual time=0.031..2.363 rows=273 loops=100)

  • Index Cond: (parent_company_id = adv_parent_1.parent_company_id)
  • Filter: (NOT is_deleted)
10. 245.304 245.304 ↓ 0.0 0 27,256

Index Scan using ix_tblcompany_claims_account_id on tblcompany_claims cc (cost=0.43..0.49 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=27,256)

  • Index Cond: (account_id = sa_1.account_id)
  • Filter: ((NOT is_deleted) AND ((status)::text = 'VERIFIED'::text))
  • Rows Removed by Filter: 0
11.          

CTE verified_account

12. 0.079 0.544 ↑ 1.3 92 1

Nested Loop (cost=6.68..459.75 rows=117 width=8) (actual time=0.052..0.544 rows=92 loops=1)

13. 0.052 0.065 ↑ 1.0 100 1

HashAggregate (cost=6.25..9.25 rows=100 width=4) (actual time=0.041..0.065 rows=100 loops=1)

  • Group Key: adv_parent_2.account_id
14. 0.013 0.013 ↑ 1.0 100 1

CTE Scan on adv_parent adv_parent_2 (cost=0.00..6.00 rows=100 width=4) (actual time=0.001..0.013 rows=100 loops=1)

15. 0.400 0.400 ↑ 1.0 1 100

Index Scan using ix_tblcompany_claims_account_id on tblcompany_claims cc_1 (cost=0.43..4.47 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (account_id = adv_parent_2.account_id)
  • Filter: ((NOT is_deleted) AND ((status)::text = 'VERIFIED'::text))
  • Rows Removed by Filter: 0
16.          

SubPlan (forCTE Scan)

17. 492.468 492.468 ↓ 7.0 13,209 1

CTE Scan on verified_parent (cost=0.00..113.46 rows=1,891 width=4) (actual time=4.623..492.468 rows=13,209 loops=1)

18. 0.569 0.569 ↑ 1.3 92 1

CTE Scan on verified_account (cost=0.00..7.02 rows=117 width=8) (actual time=0.053..0.569 rows=92 loops=1)