explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BvEM

Settings
# exclusive inclusive rows x rows loops node
1. 17.743 515.006 ↓ 34.2 5,332 1

Subquery Scan on t (cost=678,406.65..679,023.59 rows=156 width=195) (actual time=324.530..515.006 rows=5,332 loops=1)

  • Filter: ((t.date = '2020-03-01'::date) OR (t.date IS NULL))
  • Rows Removed by Filter: 246,935
2.          

CTE mysql_companies

3. 0.059 0.059 ↑ 1.0 432 1

Values Scan on ""*VALUES*"" (cost=0.00..5.40 rows=432 width=4) (actual time=0.001..0.059 rows=432 loops=1)

4. 140.064 497.263 ↓ 14.4 252,267 1

WindowAgg (cost=678,401.25..678,796.60 rows=17,571 width=131) (actual time=324.523..497.263 rows=252,267 loops=1)

5. 133.474 357.199 ↓ 14.4 252,267 1

Sort (cost=678,401.25..678,445.18 rows=17,571 width=35) (actual time=324.509..357.199 rows=252,267 loops=1)

  • Sort Key: metric_table.company_id, metric_table.date DESC
  • Sort Method: external merge Disk: 10,752kB
6. 54.173 223.725 ↓ 14.4 252,267 1

Hash Join (cost=15,227.61..677,162.42 rows=17,571 width=35) (actual time=68.768..223.725 rows=252,267 loops=1)

  • Hash Cond: (metric_table.company_id = mysql_companies.id)
7. 102.254 169.359 ↑ 1.1 433,219 1

Bitmap Heap Scan on traffic_similarweb_all_visit metric_table (cost=15,213.57..650,940.00 rows=462,803 width=31) (actual time=68.522..169.359 rows=433,219 loops=1)

  • Recheck Cond: (((organization_id = 5) AND ((rollup)::text = 'month'::text) AND (is_primary_entity = 1)) OR ((organization_id IS NULL) AND ((rollup)::text = 'month'::text)))
  • Filter: (is_primary_entity = 1)
  • Heap Blocks: exact=8,457
8. 0.002 67.105 ↓ 0.0 0 1

BitmapOr (cost=15,213.57..15,213.57 rows=462,803 width=0) (actual time=67.105..67.105 rows=0 loops=1)

9. 67.091 67.091 ↑ 1.1 433,219 1

Bitmap Index Scan on idx_traffic_all_visit_orpcd (cost=0.00..14,977.60 rows=462,803 width=0) (actual time=67.091..67.091 rows=433,219 loops=1)

  • Index Cond: ((organization_id = 5) AND ((rollup)::text = 'month'::text) AND (is_primary_entity = 1))
10. 0.012 0.012 ↓ 0.0 0 1

Bitmap Index Scan on idx_traffic_all_visit_orcd (cost=0.00..4.57 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: ((organization_id IS NULL) AND ((rollup)::text = 'month'::text))
11. 0.040 0.193 ↑ 1.0 432 1

Hash (cost=8.64..8.64 rows=432 width=4) (actual time=0.193..0.193 rows=432 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
12. 0.153 0.153 ↑ 1.0 432 1

CTE Scan on mysql_companies (cost=0.00..8.64 rows=432 width=4) (actual time=0.002..0.153 rows=432 loops=1)

Planning time : 0.468 ms
Execution time : 517.077 ms