explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EsOB

Settings
# exclusive inclusive rows x rows loops node
1. 11,349.261 16,134.401 ↓ 2.2 27,732 1

Nested Loop Left Join (cost=17,622.76..1,503,552.45 rows=12,502 width=79) (actual time=576.643..16,134.401 rows=27,732 loops=1)

  • Join Filter: (((pedigree_acl.ident)::text = (boards_all_forks_aireon_billing_matview.reg)::text) OR (((pedigree_acl.ident)::text ~~ '%.*'::text) AND (substr((pedigree_acl.ident)::text, 1, 3) = (boards_all_forks_aireon_billing_matview.ident)::text)))
  • Rows Removed by Join Filter: 98,484,622
2. 32.105 32.105 ↑ 1.0 12,033 1

Seq Scan on pedigree_acl (cost=0.00..5,887.07 rows=12,502 width=73) (actual time=0.853..32.105 rows=12,033 loops=1)

  • Filter: ((enabled IS TRUE) AND (deleted IS NULL) AND (((pedigree_mask)::text = 'aireon'::text) OR ((pedigree_mask)::text = '*'::text)))
  • Rows Removed by Filter: 169,919
3. 4,178.861 4,753.035 ↓ 1.6 8,186 12,033

Materialize (cost=17,622.76..17,754.28 rows=5,261 width=10) (actual time=0.048..0.395 rows=8,186 loops=12,033)

4. 40.096 574.174 ↓ 1.6 8,186 1

HashAggregate (cost=17,622.76..17,675.37 rows=5,261 width=10) (actual time=572.788..574.174 rows=8,186 loops=1)

  • Group Key: boards_all_forks_aireon_billing_matview.ident, boards_all_forks_aireon_billing_matview.reg
5. 344.360 534.078 ↓ 35.4 189,258 1

Bitmap Heap Scan on boards_all_forks_aireon_billing_matview (cost=12,336.56..17,596.01 rows=5,350 width=10) (actual time=194.076..534.078 rows=189,258 loops=1)

  • Recheck Cond: (date_trunc('month'::text, (date)::timestamp without time zone) = date_trunc('month'::text, (now() - '1 mon'::interval)))
  • Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
  • Rows Removed by Filter: 1,008,640
  • Heap Blocks: exact=8,026
6. 135.966 135.966 ↓ 167.9 1,197,898 1

Bitmap Index Scan on boards_all_forks_aireon_billing_matview_date_idx (cost=0.00..75.93 rows=7,133 width=0) (actual time=135.966..135.966 rows=1,197,898 loops=1)

  • Index Cond: (date_trunc('month'::text, (date)::timestamp without time zone) = date_trunc('month'::text, (now() - '1 mon'::interval)))
7.          

SubPlan (for Bitmap Heap Scan)

8. 29.179 29.179 ↑ 1.0 12,501 1

Seq Scan on pedigree_acl pedigree_acl_1 (cost=0.00..5,887.07 rows=12,544 width=6) (actual time=0.388..29.179 rows=12,501 loops=1)

  • Filter: ((enabled IS TRUE) AND (((pedigree_mask)::text = 'aireon'::text) OR ((pedigree_mask)::text = '*'::text)))
  • Rows Removed by Filter: 169,451
9. 24.573 24.573 ↓ 4.7 117 1

Seq Scan on pedigree_acl pedigree_acl_2 (cost=0.00..6,340.81 rows=25 width=32) (actual time=8.080..24.573 rows=117 loops=1)

  • Filter: ((enabled IS TRUE) AND ((ident)::text ~~ '%.*'::text) AND (((pedigree_mask)::text = 'aireon'::text) OR ((pedigree_mask)::text = '*'::text)))
  • Rows Removed by Filter: 181,835
Planning time : 0.852 ms
Execution time : 16,135.938 ms