explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pfJc : Optimization for: plan #QfY

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.357 181.003 ↓ 1.3 94 1

CTE Scan on adv_parent (cost=12,394.56..12,401.06 rows=75 width=12) (actual time=180.937..181.003 rows=94 loops=1)

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

CTE adv_parent

3. 1.077 1.077 ↑ 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.017..1.077 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_account

5. 0.061 1.645 ↑ 1.3 92 1

Nested Loop (cost=0.43..456.50 rows=117 width=8) (actual time=0.101..1.645 rows=92 loops=1)

6. 1.084 1.084 ↑ 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..1.084 rows=100 loops=1)

7. 0.500 0.500 ↑ 1.0 1 100

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

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

CTE verified_parent

9. 0.000 175.326 ↓ 13.9 13,117 1

Nested Loop (cost=8.31..11,462.87 rows=946 width=4) (actual time=1.734..175.326 rows=13,117 loops=1)

10. 2.979 67.012 ↓ 12.0 27,175 1

Nested Loop (cost=7.88..10,252.38 rows=2,259 width=8) (actual time=1.726..67.012 rows=27,175 loops=1)

11. 0.033 0.033 ↑ 1.0 100 1

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

12. 62.334 64.000 ↓ 11.8 272 100

Index Scan using ix_tbladcsummary_advertiser_parent_company_id on tbladcsummary_advertiser sa_1 (cost=7.88..101.77 rows=23 width=8) (actual time=0.027..0.640 rows=272 loops=100)

  • Index Cond: (parent_company_id = adv_parent_2.parent_company_id)
  • Filter: ((NOT is_deleted) AND (NOT (hashed SubPlan 3)))
  • Rows Removed by Filter: 1
13.          

SubPlan (forIndex Scan)

14. 1.666 1.666 ↑ 1.3 92 1

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

15. 108.700 108.700 ↓ 0.0 0 27,175

Index Scan using ix_tblcompany_claims_account_id on tblcompany_claims cc_1 (cost=0.43..0.51 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=27,175)

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

SubPlan (forCTE Scan)

17. 178.635 178.635 ↓ 13.9 13,117 1

CTE Scan on verified_parent (cost=0.00..56.76 rows=946 width=4) (actual time=1.734..178.635 rows=13,117 loops=1)

18. 0.011 0.011 ↑ 1.3 92 1

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