explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AYew

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

Nested Loop Left Join (cost=17,622.76..1,503,552.45 rows=12,502 width=79) (actual time=597.948..16,042.235 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. 33.435 33.435 ↑ 1.0 12,033 1

Seq Scan on pedigree_acl (cost=0.00..5,887.07 rows=12,502 width=73) (actual time=0.602..33.435 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,929
3. 4,169.442 4,765.068 ↓ 1.6 8,186 12,033

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

4. 41.609 595.626 ↓ 1.6 8,186 1

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

  • Group Key: boards_all_forks_aireon_billing_matview.ident, boards_all_forks_aireon_billing_matview.reg
5. 365.761 554.017 ↓ 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=192.379..554.017 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. 133.627 133.627 ↓ 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=133.627..133.627 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. 30.156 30.156 ↑ 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.384..30.156 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,461
9. 24.473 24.473 ↓ 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.071..24.473 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,845
Planning time : 0.427 ms
Execution time : 16,043.640 ms