explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l6xE

Settings
# exclusive inclusive rows x rows loops node
1. 25.896 184,116.709 ↑ 1.0 1 1

GroupAggregate (cost=2,967,317.28..2,967,319.79 rows=1 width=32) (actual time=184,116.709..184,116.709 rows=1 loops=1)

  • Group Key: total_accounts.count
2.          

CTE list_accounts

3. 115.638 12,101.738 ↓ 1.2 94,985 1

HashAggregate (cost=243,903.87..244,667.35 rows=76,348 width=42) (actual time=12,069.159..12,101.738 rows=94,985 loops=1)

  • Group Key: ddb.abm_id
4. 155.441 11,986.100 ↓ 1.2 94,985 1

Nested Loop (cost=1.69..243,713.00 rows=76,348 width=42) (actual time=2.067..11,986.100 rows=94,985 loops=1)

5. 90.404 2,332.159 ↓ 1.2 94,985 1

Nested Loop (cost=1.13..196,838.92 rows=76,348 width=18) (actual time=2.038..2,332.159 rows=94,985 loops=1)

6. 57.100 57.100 ↓ 1.2 94,985 1

Index Scan using index_list_domains_on_list_id_and_hidden_and_dynamic_hidden_new on list_domains ld (cost=0.57..81,458.50 rows=76,348 width=15) (actual time=2.008..57.100 rows=94,985 loops=1)

  • Index Cond: ((list_id = 181858) AND (hidden = false))
  • Filter: (NOT hidden)
7. 2,184.655 2,184.655 ↑ 1.0 1 94,985

Index Only Scan using domaindb_domain_aliases_tmp_given_domain_domaindb_domain_idx_ol on domaindb_domain_aliases ddba (cost=0.56..1.50 rows=1 width=36) (actual time=0.023..0.023 rows=1 loops=94,985)

  • Index Cond: (given_domain = ld.given_domain)
  • Heap Fetches: 0
8. 9,498.500 9,498.500 ↑ 1.0 1 94,985

Index Scan using domaindb_tmp_domain_idx_old on domaindb ddb (cost=0.56..0.60 rows=1 width=42) (actual time=0.100..0.100 rows=1 loops=94,985)

  • Index Cond: ((domain)::text = (ddba.domaindb_domain)::text)
9.          

CTE total_accounts

10. 13.905 12,166.319 ↑ 1.0 1 1

Aggregate (cost=1,717.83..1,717.84 rows=1 width=8) (actual time=12,166.319..12,166.319 rows=1 loops=1)

11. 12,152.414 12,152.414 ↓ 1.2 94,985 1

CTE Scan on list_accounts (cost=0.00..1,526.96 rows=76,348 width=0) (actual time=12,069.162..12,152.414 rows=94,985 loops=1)

12.          

CTE account_placement_country_breakdown

13. 1,888.523 171,708.970 ↑ 22.3 79,643 1

GroupAggregate (cost=2,587,684.50..2,672,064.40 rows=1,776,419 width=1,053) (actual time=165,325.024..171,708.970 rows=79,643 loops=1)

  • Group Key: la.account_name, la.domain, la.abm_id, dfa.placement_type, dfa.geo_country
14. 13,847.957 169,820.447 ↓ 1.6 2,869,363 1

Sort (cost=2,587,684.50..2,592,125.55 rows=1,776,419 width=1,061) (actual time=165,324.923..169,820.447 rows=2,869,363 loops=1)

  • Sort Key: la.account_name, la.domain, la.abm_id, dfa.placement_type, dfa.geo_country
  • Sort Method: external merge Disk: 236392kB
15. 1,556.394 155,972.490 ↓ 1.6 2,869,363 1

Nested Loop (cost=0.56..1,919,628.85 rows=1,776,419 width=1,061) (actual time=0.171..155,972.490 rows=2,869,363 loops=1)

16. 65.471 65.471 ↓ 1.2 94,985 1

CTE Scan on list_accounts la (cost=0.00..1,526.96 rows=76,348 width=1,036) (actual time=0.001..65.471 rows=94,985 loops=1)

17. 154,350.625 154,350.625 ↓ 1.3 30 94,985

Index Scan using idx_dsp_delivery_forecast_aggregates_abm_id on dsp_delivery_forecast_aggregates dfa (cost=0.56..24.89 rows=23 width=29) (actual time=0.525..1.625 rows=30 loops=94,985)

  • Index Cond: (abm_id = la.abm_id)
  • Filter: (((placement_type)::text = ANY ('{banner}'::text[])) AND ((geo_country)::text = ANY ('{can,usa}'::text[])) AND (((placement_type)::text <> 'banner'::text) OR ((banner_dimensions)::text = ANY ('{300x50,320x50,160x600,970x250,728x90,300x250,300x600}'::text[]))) AND (((geo_country)::text <> 'usa'::text) OR ((geo_region)::text = ANY ('{usa/ca,usa/ks,usa/tx,usa/ga,usa/pa,usa/wa,usa/nj,usa/ny,usa/ct}'::text[]))))
  • Rows Removed by Filter: 158
18.          

CTE account_breakdown

19. 78.298 171,849.284 ↓ 330.9 66,174 1

HashAggregate (cost=48,851.52..48,854.02 rows=200 width=13) (actual time=171,824.327..171,849.284 rows=66,174 loops=1)

  • Group Key: account_placement_country_breakdown.abm_id
20. 171,770.986 171,770.986 ↑ 22.3 79,643 1

CTE Scan on account_placement_country_breakdown (cost=0.00..35,528.38 rows=1,776,419 width=12) (actual time=165,325.029..171,770.986 rows=79,643 loops=1)

21. 27.811 184,090.813 ↓ 330.9 66,174 1

Sort (cost=13.66..14.16 rows=200 width=21) (actual time=184,081.033..184,090.813 rows=66,174 loops=1)

  • Sort Key: total_accounts.count
  • Sort Method: quicksort Memory: 8242kB
22. 18.978 184,063.002 ↓ 330.9 66,174 1

Nested Loop (cost=0.00..6.02 rows=200 width=21) (actual time=183,990.653..184,063.002 rows=66,174 loops=1)

23. 12,166.321 12,166.321 ↑ 1.0 1 1

CTE Scan on total_accounts (cost=0.00..0.02 rows=1 width=8) (actual time=12,166.320..12,166.321 rows=1 loops=1)

24. 171,877.703 171,877.703 ↓ 330.9 66,174 1

CTE Scan on account_breakdown ab (cost=0.00..4.00 rows=200 width=13) (actual time=171,824.329..171,877.703 rows=66,174 loops=1)