Every enterprise with more than a handful of AWS accounts eventually has the same reckoning. Someone in finance asks which team owns the spend in AWS account 846241037459. Someone in security wants to know whether the resources in a particular VPC are production or development. Someone in operations needs to route an incident to the right application owner at 2am. And in every case, the answer depends on tags…. tags tags tags, never ending tags – tags that probably do not exist, may or may not be accurate, and almost certainly aren’t consistent across business divisions.

This is the tagging problem, and most organisations try to solve it the wrong way. They write a tagging policy. They distribute it in a wiki that nobody bookmarks. They ask teams to please tag their resources correctly. And then they act surprised when compliance sits at sub 20% and the data is useless for any meaningful reporting or incident management.

The Provider > Consumer model solves this differently. Instead of asking consumers to tag their resources, the Provider delivers mandatory tags as part of the account vending pipeline (before the consumer ever touches the environment). The tags arrive with the account. The resources inherit them. The consumer can’t remove them, can’t modify them, and in most cases doesn’t even realise they provided the data that populated them!

This isn’t a tagging strategy. It’s a tagging system. And the distinction matters more than most platform teams appreciate.

Why Voluntary Tagging Always Fails at Scale

Let’s be honest about this. Voluntary tagging policies have a 100% failure rate in large enterprises. Not because people are negligent, but because the model is fundamentally broken.

A developer provisioning an EC2 instance is thinking about their application. They’re thinking about instance types, security groups, and whether their deployment fettling works. They are not thinking about which cost centre code maps to their division’s financial reporting hierarchy. They don’t know that finance needs a specific format for the Cost Code tag (they certainly don’t know that the value needs to match an entry in SAP that they’ve never seen and don’t have access to).

So what happens? They leave the tag blank. Or they guess. Or TBD/D goes in. Or they put in something that looks right but doesn’t match the source of truth. And now you have a resource in production with a cost code that doesn’t resolve to anything in the financial system, which means it falls into an “unallocated” bucket, which means someone in FinOps spends three hours every month trying to reconcile it.

Multiply this across thousands of resources, dozens of teams, and many business divisions, and you have a tagging estate that’s technically present but functionally useless. You’ve achieved the appearance of governance without any of the substance.

The root cause isn’t laziness. It’s that you’ve placed the burden of organisational metadata on the people least equipped to provide it.

A developer shouldn’t need to understand the enterprise financial hierarchy to deploy a Lambda function. That’s not their job. It’s yours (as a Provider).

Mandatory Tags Are a Platform Concern, Not a Consumer Concern

This is the shift in thinking that makes the Provider > Consumer model work for tagging. Mandatory tags, the tags that drive financial reporting, security ownership, compliance classification, and business criticallity, are platform-level concerns. They exist to serve the organisation, not the individual workload. And because they serve the organisation, the organisation (through the Provider) should own them end to end.

In the Provider > Consumer model, mandatory tags are opinionated.

The platform team decides which tags exist, what their keys are called, what format the values must take, and where the source of truth lives. These aren’t suggestions. They’re not configurable. They’re the platform’s DNA, and they flow through every account, every VPC, and every resource that gets provisioned and this works because there is workload segregation at the account level.

The Provider owns the tag taxonomy. The Provider owns the enforcement mechanism. The Provider owns the pipeline that applies them. The consumer benefits from the data they generate, through cost reports, ownership dashboards, and incident routing (without ever having to maintain the tags themselves).

This is what opinionated platform design looks like when applied to metadata. You don’t ask consumers what tags they want. You tell them what tags they’ll get. And you make those tags so deeply embedded in the provisioning process that removing them would require dismantling the pipeline itself.

Pre-Account Vending: Where the Data Actually Comes From

Here’s where it gets elegant. The data that populates mandatory tags doesn’t come from a tagging form. It comes from the account request process, typically a well-designed ServiceNow form that the consumer fills out when they request a new AWS account pattern.

Think about what a consumer provides when they request an account. They specify which business division they belong to. They identify the application or workload the account is for. They nominate a technical owner and a security contact. They provide (or their request is enriched with) a cost centre code from the enterprise financial system. They indicate whether this is a production or non-production workload.

Every single one of those data points maps directly to a mandatory tag. Business Division becomes the Business Division tag. The application name maps to Application Name tag. The cost centre maps to Cost Code. The technical contact maps to Technical Owner. The consumer isn’t filling out a tagging form. They’re filling out an account request form. But the platform team has designed that form so that every field either directly populates a tag or feeds a lookup that resolves to a tag value.

The consumer doesn’t know they’re providing mandatory tagging data. They think they’re answering reasonable questions about their workload. And they are, but those questions just happen to be the exact inputs the tagging pipeline needs.

This is deliberate. The best tagging strategies are invisible to the people being tagged. If your consumers are aware they’re doing tagging, you’ve already introduced friction. And friction is where compliance goes to die.

The Vending Pipeline: Tags as Infrastructure

Once the account request is approved and the required data is captured, the Provider’s account vending pipeline takes over. This is where tags stop being metadata and start being infrastructure.

The vending pipeline, whether it’s built on AWS Control Tower Account Factory, Terraform, or a custom solution, provisions the account with mandatory tags already applied. The tags are written at the account level as part of the same automation that creates the account, configures the security baseline, deploys the VPC, and wires up connectivity.

There’s no separate tagging step. There’s no “apply tags after provisioning” task that someone might forget or defer. The tags are part of the account’s identity from the moment it exists. By the time the consumer gets access to their new environment, the mandatory tags are already there, applied, validated, and from their perspective, immutable.

At the AWS account level, these tags propagate through AWS Organisations. Tag policies enforce the expected format and values. Service Control Policies can prevent the creation of resources that don’t carry the required tags. And because the Provider controls the Terraform modules that consumers use to provision resources within their accounts, those modules can inject mandatory tags automatically, the consumer’s resource inherits the account’s mandatory tags without any action on their part.

This is the inheritance model, and it works because of a design decision the Provider made long before any tagging conversation happened, accounts are segregated by environment and by workload. One account per workload per environment. This means the mandatory tags that describe ownership, cost allocation, and classification are consistent for every resource in the account because every resource in the account belongs to the same workload in the same environment. There’s no ambiguity. There’s no “this EC2 instance belongs to Team A but that RDS instance belongs to Team B.” The account boundary is the ownership boundary, and the tags reflect that.

Why Mandatory Tags Must Be Immutable to Consumers

This is where most tagging implementations go sideways.

If consumers can modify mandatory tags, they will. Not maliciously just inevitably. Someone will “fix” a cost code they think is wrong. Someone will update a technical owner field when a team member leaves, using a personal email instead of a distribution list. Someone will delete a tag they don’t recognise because it looks like clutter.

Every one of these well-intentioned changes breaks something downstream. The cost code “fix” means the resource no longer maps to a valid entry in the financial system. The email change means the incident routing automation sends alerts to a personal inbox that nobody checks after hours. The deleted tag means a compliance report now has a gap that triggers an audit finding.

Mandatory tags are not the consumer’s concern to maintain because they are not the consumer’s data. They are organisational data that happens to be attached to the consumer’s resources. The Provider maintains them. The Provider updates them. The Provider ensures they stay consistent with their sources of truth.

In practice, this means the tags are applied and managed through the Provider’s pipeline, through Terraform state, through AWS Organisations tag policies, through account-level automation that runs on a schedule to detect and remediate drift. If a tag gets changed, the pipeline puts it back. If a tag gets deleted, the pipeline restores it. The consumer’s Terraform code doesn’t manage these tags. The consumer’s IAM permissions don’t grant write access to them at the organisational level.

This isn’t about trust issues. It’s about system design. You don’t let application code modify the VPC’s route table. You don’t let developers reconfigure the transit gateway. And you don’t let consumers edit the tags that drive your organisation’s financial reporting, security ownership, and compliance posture. These are platform concerns, managed by the platform, enforced by the platform.

What Happens When a Consumer Needs a Tag Changed?

Tags like CostCode or TechnicalOwner do change over time. Teams reorganise. Cost centres get restructured. People move to different roles. The fact that mandatory tags are immutable to consumers doesn’t mean they’re permanent, instead it means changes flow through the right channels.

When a consumer needs a mandatory tag updated, they raise a request. This might be a ServiceNow ticket, or a conversation with the Platform team. The Provider validates the new value against the source of truth (does this cost code actually exist in SAP? Is this email address a valid distribution list in the directory?), then updates the tag through the pipeline, and the change propagates to the account and its resources.

The process is intentionally deliberate (not slow, deliberate). Mandatory tags affect reporting, billing allocation, security ownership, and compliance classification across the organisation. A change to the ‘BusinessDivision’ tag on an account ripples through every cost report, every ownership dashboard, and every compliance audit that references that account. That change should be validated, approved, and applied through automation, not made ad hoc in the AWS console by someone who needed to update a spreadsheet.

In practice, mandatory tag change requests are rare. Cost codes don’t change monthly. business division structures don’t shift weekly. Technical ownership transfers happen, but not frequently enough to create operational burden. The channel exists for when it’s needed, but the design assumes stability, because mandatory tags represent organisational facts that are, by definition, slow-moving.

Consumer Tags: Where the Provider Steps Back

Not all tags are mandatory. Consumers obviously have their own tagging needs, operational tags, automation tags, application-specific metadata that the platform team has no opinion about. And that’s exactly right. The Provider should have no opinion about them.

A consumer might want to tag resources with deployment version numbers, feature flags, team-specific identifiers, or automation triggers. These tags serve the consumer’s operational needs, not the organisation’s reporting needs. The Provider doesn’t define them, doesn’t enforce them, and doesn’t manage them.

What the Provider can do, and should do, is provide guidance. A set of recommended operational tags, published as suggestions rather than mandates, helps consumers who aren’t sure what to tag or how. Tags like CostCentre (for project-level cost tracking below the mandatory CostCode), ManagedBy (Terraform, CloudFormation, manual), or BackupPolicy (daily, weekly, none) are operationally useful and don’t need to be mandated to be valuable.

The Provider can also provide tag validation tooling that consumers can optionally adopt. Linting rules in the CI/CD pipeline that warn (but don’t block) when recommended tags are missing. Dashboard views that show tag coverage across a consumer’s account. These are nudges, not guardrails, and the distinction really matters.

Mandatory tags get guardrails. Consumer tags get guidance. Conflating the two undermines both.

The ServiceNow Form: Designed for Data, Not for Tagging

This is where the consumer experience either succeeds or fails.

A poorly designed form asks the consumer to provide tagging data. It has fields labelled “Business Division Tag,” “Cost Centre Tag,” and “Application Name Tag.” The consumer sees these and thinks “I don’t know what half of these mean” or “I’ll come back to this later” (they won’t). The form creates friction, and friction creates gaps and gaps create delays in environment vending.

Behind the form, the platform team has mapped every field to a tag key and configured lookups against the sources of truth. The business division dropdown pulls from a source of truth. The cost centre field validates against SAP. The application name resolves against the enterprise architecture tool. The consumer selects from a dropdown and the pipeline writes the value to the account. The consumer types a cost centre and the pipeline validates it exists before writing.

This form is the interface between the consumer’s knowledge and the Provider’s tagging system. When it’s designed well, the consumer knows what information is required well up front and fills it out in five minutes, the pipeline has everything it needs, and mandatory tags arrive fully populated and validated before the account is even created. The consumer never typed a tag key. They never formatted a tag value. They answered questions about their workload, and the platform did the rest.

Resource Inheritance: Why Account Segregation Makes This Work

The inheritance model (where resources automatically carry the mandatory tags of their parent account) only works if accounts are properly segregated. And this is a design decision the Provider must make early, because retrofitting it later is pain – real pain.

The principle is straightforward. One account per workload per environment. The production instance of Application X lives in one account. The development instance lives in another. The staging instance lives in a third. Each account has mandatory tags that describe that specific combination of workload and environment.

When a consumer provisions an ECS/EC2 instance, an RDS database, or an S3 bucket in that account, the resource inherits the account’s mandatory tags. The Business Division tag is correct because the account belongs to that division. The Application Name tag is correct because the account hosts that application. The Environment tag is correct because the account is dedicated to that environment. The Cost Code is correct because the cost centre covers all resources in that workload’s environment.

There’s no need for the consumer to tag each resource individually with organisational metadata. The account is the tagging boundary. Everything inside it inherits. This eliminates the most common source of tagging inconsistency in large estates: resources within the same account tagged differently because different team members provisioned them at different times with different understandings of what the values should be.

Account segregation also eliminates the shared account problem, you know which one I mean.. The scenario where a single account hosts resources from multiple applications owned by different teams with different cost centres. In a shared account, tagging has to happen at the resource level because the account-level tags can’t represent multiple owners. This is fragile, error-prone, and impossible to enforce consistently. Proper account segregation makes it unnecessary.

The Tags That Drive Everything

Let’s be specific about what mandatory tags actually enable, because the payoff is what justifies the investment in the pipeline.

Financial reporting and cost allocation. When every account carries a validated Business Division, and Cost Code tag, cost allocation becomes automatic. AWS Cost Explorer can slice spend by any of these dimensions without manual intervention. FinOps teams can produce division-level cost reports that match the financial system because the tag values are sourced from the financial system. There’s no reconciliation step. There’s no “unallocated” bucket growing larger every month. The tags are the reporting layer, and because they’re mandatory and immutable, the reports are trustworthy. This paves the way for successful integration of enterprise level FinOps tooling that can deliver incredible insights and value.

Security and compliance ownership. The Security Owner and Technical Owner tags establish clear accountability for every account and every resource within it. When a vulnerability is discovered, the security team doesn’t need to dig through wikis or Slack/Teams channels to find who owns the affected resource. The tag tells them. When a compliance audit requires evidence of ownership for production workloads, the tag provides it. Automated incident routing can use these tags to page the right person at 2am without human triage.

Operational efficiency. Tags like Application Name, Application ID, and Environment enable automation that would be impossible without consistent metadata. Automated backup policies keyed to environment type. Monitoring thresholds that differ between production and development. Cost anomaly detection scoped to individual applications rather than entire accounts. Each of these automations depends on tags being present, accurate, and consistent (exactly what mandatory tagging through the Provider pipeline guarantees).

Discovery and migration planning. When an organisation needs to assess its cloud estate (for migration, for rationalisation, for M&A due diligence), mandatory tags provide the map. Every account can be categorised by business division, application, environment, and ownership without manual investigation. A query against AWS Organisations returns a complete inventory, tagged and classified, ready for analysis. Without mandatory tags, this same exercise takes weeks of interviews, spreadsheet reconciliation, and educated guessing.

Governance That Doesn’t Feel Like Governance

This is the part that matters most, and it’s the part that most tagging strategies miss the mark on.

Good governance is invisible to those being governed. If your consumers feel the weight of your tagging policy, if they’re spending time looking up tag values, formatting keys correctly, remembering which tags are mandatory, then your governance is creating friction, and friction erodes compliance over time. People start cutting corners. They copy tags from another resource without checking if they’re correct. They leave optional-but-important fields blank because the form is already too long.

Mandatory tagging through the Provider-Consumer model removes all of this. The governance is in the pipeline, not in the consumer’s workflow. The consumer’s experience is > request an account, answer some questions about your workload, get a fully provisioned environment, start building. The tags are there. They’re correct. They’ll stay correct. And the consumer never had to think about them.

This is what it means to build for consumability in the context of tagging. You’re not building a tagging policy. You’re building a tagging system that operates as infrastructure, fully automated, validated, enforced, and invisible to the people it serves. The reporting works because the data is clean. The data is clean because the pipeline ensures it. The pipeline ensures it because the Provider owns it end to end.

Tags aren’t a practice. They’re plumbing. And like all good plumbing, the best measure of success is that nobody thinks about it until something goes wrong, which, if you’ve built it right, it won’t (famous last words 😉)

Mandatory tags don’t ask for compliance. They deliver it. Build the pipeline, own the taxonomy, and let your consumers focus on what they’re actually here to do, build and operate!

The query plan is only as good as the statistics behind it. When those statistics are wrong, the planner makes confident decisions based on a false reality.

We run a field technician dispatch system on PostgreSQL 14. The core query — find technicians matching specific dispatch criteria near a job site — ran in 3ms at 10am and 42ms at 2pm the same day. Same query text. Same bind parameters. The only thing that changed was the number of technicians available — as the fleet clocked in and dispatched through the day, the underlying data distribution shifted just enough to flip the query plan.

This post is the story of how we traced a 14x latency regression to a fundamental assumption baked into every cost-based query optimizer, and why our existing composite index didn’t help. Along the way, we’ll dig into pg_statistic, selectivity estimation, BitmapAnd mechanics, and the specific ways correlated boolean columns break the planner’s world model.

But first — if you’ve never looked at how PostgreSQL decides whether to use your index, let’s build up from first principles.

How PostgreSQL Chooses a Query Plan

PostgreSQL doesn’t just “use the index.” It evaluates multiple execution strategies and picks the one with the lowest estimated cost. This is the cost-based optimizer.

The optimizer doesn’t see your data. It sees a statistical summary stored in pg_statistic (exposed via the pg_stats view). When ANALYZE runs — manually or via autovacuum — PostgreSQL samples rows and builds per-column statistics: most common values (MCVs), histograms, distinct counts, and null fractions.

For a single predicate like WHERE is_available = true, the planner looks up the MCV frequency for is_available. If true appears at 0.50, the estimated rows on a 15,000-row table is 7,500.

For multiple predicates, it applies the independence assumption:

P(A AND B AND C) = P(A) × P(B) × P(C)

This is correct if and only if the columns are statistically independent. The PostgreSQL docs are explicit:

“The planner normally assumes that multiple conditions are independent of each other, an assumption that does not hold when column values are correlated.”
— Chapter 14.2: Statistics Used by the Planner

When the assumption holds, estimates are accurate. When it doesn’t, they can be off by orders of magnitude. Our query hit the second case.

The Setup: Our Table and Query

How we ended up with six booleans

The technicians table tracks every field technician in the system — roughly 15,000 rows. It didn’t start with six boolean columns. It started with two: is_active and is_available. Then we needed to track whether a technician was currently on a job, so is_dispatched arrived. A compliance incident led to is_blocked. Express delivery became a feature, so is_express_enabled. An ops request for soft-disabling technicians without removing them added is_suspended. Each boolean made sense in isolation, each was a small migration, and each shipped independently over about eighteen months.

CREATE TABLE technicians (
    id              BIGSERIAL PRIMARY KEY,
    zone_id         INTEGER NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT false,
    is_available    BOOLEAN NOT NULL DEFAULT false,
    is_dispatched   BOOLEAN NOT NULL DEFAULT false,
    is_blocked      BOOLEAN NOT NULL DEFAULT false,
    is_express_enabled BOOLEAN NOT NULL DEFAULT false,
    is_suspended    BOOLEAN NOT NULL DEFAULT false,
    last_job_location GEOMETRY(Point, 4326),
    -- ... other columns
);

The technician_locations table stores real-time GPS positions (one row per technician, updated every few seconds).

Key indexes

-- Composite boolean index WITH zone
CREATE INDEX idx_technician_selection_v2 ON technicians
    (zone_id, is_active, is_available, is_dispatched, is_blocked, is_express_enabled, is_suspended);

-- Composite boolean index WITHOUT zone
CREATE INDEX idx_technician_selection ON technicians
    (is_active, is_available, is_dispatched, is_blocked, is_express_enabled, is_suspended);

-- Spatial index on last job location
CREATE INDEX idx_technician_last_job_geom ON technicians USING gist (last_job_location);

-- Spatial index on live GPS location
CREATE INDEX idx_technician_location_geom ON technician_locations USING gist (current_location);

The allocation query

The query finds technicians matching specific dispatch criteria within a geographic radius:

SELECT t.*, tl.*, ts.*
FROM technicians t
    INNER JOIN technician_locations tl ON t.id = tl.technician_id
    LEFT JOIN technician_stats ts ON t.id = ts.technician_id
    LEFT JOIN daily_utilization du ON t.id = du.technician_id
    -- ... additional left joins for tagging, blocklists, queue state
WHERE t.is_active = true
  AND t.is_available = true
  AND t.is_dispatched = true
  AND t.is_blocked = false
  AND t.is_express_enabled = true
  AND t.is_suspended = false
  AND ST_DWithin(tl.current_location, ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography, 4000)
  AND tl.reported_at > now() - interval '20 minutes';

Six boolean conditions on technicians, plus a spatial predicate on technician_locations. Looks straightforward. The problem is entirely in how the planner estimates the boolean combination.

The Symptom: Same Query, Two Plans

We noticed the issue in our Grafana dashboards. The allocation query’s P50 latency was steady around 4ms, but P99 kept spiking to 40–45ms within the same day. The pattern correlated with fleet activity — as more technicians clocked in and changed dispatch states through the day, the underlying data distribution shifted. The planner’s statistics drifted with it, and the plan flipped.

When we pulled EXPLAIN ANALYZE hours apart on the same day, we saw two completely different plans.

The fast plan (~3ms): BitmapAnd

BitmapAnd  (cost=146.22..146.22 rows=4 width=0) (actual time=1.83..1.83 rows=0 loops=1)
  -> Bitmap Index Scan on idx_technician_selection
       (cost=0.00..5.22 rows=38 width=0) (actual time=0.45..0.45 rows=8720 loops=1)
       Index Cond: (is_active = true) AND (is_available = true) AND (is_dispatched = true)
                   AND (is_blocked = false) AND (is_express_enabled = true) AND (is_suspended = false)
  -> Bitmap Index Scan on idx_technician_last_job_geom
       (cost=0.00..140.75 rows=1628 width=0) (actual time=1.20..1.20 rows=8419 loops=1)
       Index Cond: (last_job_location && <bounding box>)

Bitmap Heap Scan on technicians t
    Heap Blocks: exact=191
    -> BitmapAnd (above)
    Filter: ST_DWithin(last_job_location, ..., 4000)
    Rows Removed by Filter: 92
    -> actual rows=1

Execution Time: 3.155 ms

The planner used BitmapAnd — it scanned both the boolean index and the spatial index on technicians, intersected the two bitmaps in memory, and only fetched 191 heap pages. One row survived all filters. Fast.

The slow plan (~42ms): Boolean index only

Bitmap Heap Scan on technicians t
    (cost=5.47..1205.33 rows=38 width=824) (actual time=0.62..8.14 rows=4626 loops=1)
  -> Bitmap Index Scan on idx_technician_selection
       (cost=0.00..5.22 rows=38 width=0) (actual time=0.48..0.48 rows=4626 loops=1)
       Index Cond: (is_active = true) AND (is_available = true) AND (is_dispatched = true)
                   AND (is_blocked = false) AND (is_express_enabled = true) AND (is_suspended = false)

  Nested Loop (actual loops=4558)
    -> Index Scan on technician_locations tl
         Index Cond: (technician_id = t.id)
         Filter: ST_DWithin(current_location, ..., 4000)
         loops=4558

Execution Time: 42.008 ms

The planner used only the boolean index. No spatial index. It fetched 4,626 rows from technicians, then nested-loop joined into technician_locations 4,558 times, applying ST_DWithin as a CPU filter on each loop. 14x slower.

Side by side

A few things jump out. The estimated row count is 38 in both plans. That’s the planner’s selectivity estimate for the boolean combination. But the actual count is thousands of rows. The estimate is wrong by two orders of magnitude.

The difference between the plans isn’t that one has better estimates — both are equally wrong. The difference is what the planner decided to do with that bad estimate.

The Root Cause: 229x Underestimate

The selectivity math

The planner looks up each boolean column’s frequency independently from pg_statistic and multiplies them together:

is_active = true:          ~70%  → 0.70
is_available = true:       ~50%  → 0.50
is_dispatched = true:      ~25%  → 0.25
is_blocked = false:        ~95%  → 0.95
is_express_enabled = true: ~40%  → 0.40
is_suspended = false:      ~95%  → 0.95

Combined (assuming independence):
  0.70 × 0.50 × 0.25 × 0.95 × 0.40 × 0.95 = 0.0025

  15,000 rows × 0.0025 = 38 rows

The planner arrives at 38. The actual number is 8,720. That’s a 229x underestimate.

Why the multiplication is wrong

These columns are not independent. They encode a finite state machine — the operational lifecycle of a field technician. The business logic enforces hard constraints between them:

In practice, 5 of the 6 booleans encode roughly 6 valid states (the sixth, is_express_enabled, is an independent capability flag):

That’s 6 valid states out of 2^5 = 32 theoretical combinations. The planner treats all 32 as equally possible and weights them accordingly. It doesn’t know that is_blocked = true AND is_active = true can never occur in practice.

Actual selectivity of the boolean combination: ~58% (8,720 / 15,000)
Planner’s estimate: 0.25% (38 / 15,000)
Error factor: 229x

The PostgreSQL docs even demonstrate this exact failure mode:

“The planner estimates the selectivity for each condition individually… Then it assumes that the conditions are independent, and so it multiplies their selectivities, producing a final selectivity estimate of just 0.01%. This is a significant underestimate, as the actual number of rows matching the conditions (100) is two orders of magnitude higher.”
— Multivariate Statistics Examples

Why the Plan Flips: ANALYZE Sampling Non-Determinism

The 229x underestimate explains why the slow plan is slow. But why does the query sometimes get the fast plan?

Because the estimate isn’t always exactly 38 — it drifts. ANALYZE takes a random sample, not a census:

“The statistics are only approximate, and will change slightly each time ANALYZE is run, even if the actual table contents did not change. This might result in small changes in the planner’s estimated costs. In rare situations, this non-determinism will cause the planner’s choices of query plans to change after ANALYZE is run.”
— ANALYZE command reference

Our technicians table is high-churn — technicians toggle is_dispatched hundreds of times per day across the fleet. As the number of available technicians changes through the day, each ANALYZE sample captures a different snapshot. The boolean estimate drifts — and the plan is sensitive to that drift.

The tipping point

The planner’s decision to include the spatial index in a BitmapAnd has a cost threshold. When the boolean estimate is “moderate” (say, 30–50 rows), the planner decides it’s worth adding a second index scan to intersect. When the estimate drops below a threshold (say, 8–15 rows), the planner decides the boolean index alone is good enough and drops the spatial index.

When the boolean estimate is ~38:

Planner: "Boolean index returns ~38 rows. That's moderate.
          Adding the spatial index scan (est. 1,628 rows) and
          intersecting bitmaps will reduce heap fetches to ~4.
          The extra index scan is worth it."

Plan: BitmapAnd(boolean_index + spatial_index)
Result: 191 heap pages, 1 row survives → 3ms

When the boolean estimate drops to ~10:

Planner: "Boolean index returns only ~10 rows. That's already tiny.
          A second index scan costs more than just filtering 10 rows
          by distance. Not worth the overhead."

Plan: Bitmap Scan on boolean index only, ST_DWithin as CPU filter
Result: 8,720 rows scanned, spatial filter on every row → 42ms

The perverse incentive

Here’s the cruel part: the MORE wrong the boolean estimate is (in the “too low” direction), the MORE confident the planner becomes that BitmapAnd is unnecessary — and the WORSE the actual performance gets. The planner drops the spatial index precisely when it would help the most.

Autovacuum is the trigger

Autovacuum triggers ANALYZE when the number of changed tuples exceeds autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × table_size. With default settings, that’s 50 + 0.10 × 15,000 = 1,550 tuple changes. On a table with thousands of state transitions per minute during peak hours, this threshold is crossed constantly. ANALYZE can fire multiple times within a few minutes, each time producing a slightly different boolean estimate that may or may not cross the BitmapAnd decision boundary.

BitmapAnd: How It Works and Why It’s Fragile

BitmapAnd scans multiple indexes on the same table, builds a bitmap of matching heap page locations for each, and intersects them. Only pages in the intersection get fetched. In our fast plan, it intersects boolean-matching pages with spatial-matching pages, reducing thousands of candidates down to 191 heap pages.

Two things make it fragile for our case:

Same-table only. BitmapAnd can’t combine indexes across different tables. Our spatial filter is on technician_locations, our boolean filter is on technicians — that’s a join, not a merge. BitmapAnd only helps when both indexes live on the same table (like the last_job_location GiST index and the boolean index, both on technicians).

Cost-sensitive inclusion. Each additional bitmap index scan has a startup cost. When the boolean estimate is low enough, the planner decides the second index scan isn’t worth it:

“Because each additional index scan adds extra time, the planner will sometimes choose to use a simple index scan even though additional indexes are available that could have been used as well.”
— Chapter 11.5: Combining Multiple Indexes

The planner concludes: “I only expect 10 rows from the boolean index — filtering them by distance is cheaper than running a second index scan.” The math is correct given the premise. The premise is just 229x wrong.

Why Our Composite Index Didn’t Help

We already had a composite index on all six boolean columns. The natural assumption was: “PostgreSQL has an index on exactly this combination — surely it knows how many rows match?”

It doesn’t. A composite index helps PostgreSQL find rows efficiently (access path), but it does NOT help PostgreSQL estimate how many rows exist before scanning (cardinality estimation). These are two separate systems.

“No entry is made for an ordinary non-expression index column, however, since it would be redundant with the entry for the underlying table column.”
— pg_statistic catalog

ANALYZE samples heap pages and builds per-column MCVs independently. The B-tree’s internal knowledge of which key combinations exist is never extracted into planner statistics. Our composite index found the right rows perfectly every time — the problem was that the planner’s estimate of how many rows it would find was 229x too low, causing it to choose a bad join strategy around the index.

Only two things can store combination frequencies:

1. CREATE STATISTICS (mcv) — explicit opt-in, stores multi-column MCV lists
2. A single enum/status column — collapses the combination into one value

The Anti-Pattern: Dependent Booleans in OLTP

This isn’t just a query tuning story. It’s a schema design lesson.

When you model a state machine as independent boolean columns, you’re making an implicit promise to the database: “these columns are independent dimensions.” Every cost-based optimizer — PostgreSQL, MySQL, Oracle, SQL Server — takes you at your word. The planner multiplies their selectivities because that’s mathematically correct for independent variables.

The problem is that the promise is false. is_available and is_dispatched aren’t independent dimensions — they’re states in a lifecycle. One implies the other. The planner can’t know this from the schema alone.

This pattern tends to emerge organically. You start with is_active. A feature ships, you add is_available. A compliance requirement adds is_blocked. Each column is a small, low-risk migration. Nobody notices that the columns are accumulating mutual dependencies until the planner starts making bad decisions — and even then, the symptom (intermittent latency spikes) doesn’t obviously point at schema design.

The fix is to model state as state. If your boolean columns have business-logic dependencies between them — if certain combinations can never occur — they should be a single enum or status column. One column, one MCV lookup, no multiplication error. WHERE status = 'idle' gives the planner an exact frequency. WHERE is_active = true AND is_available = true AND is_dispatched = false AND ... gives it a guess.

A note on InnoDB

Interestingly, this specific failure mode wouldn’t manifest the same way on MySQL/InnoDB with the same composite index. InnoDB’s optimizer uses a technique called index dive — when estimating the cardinality of a range scan on a composite index, it actually samples the B-tree directly rather than multiplying per-column statistics. For an equality scan across all columns of a composite index (which is what our query does), InnoDB dives into the index, reads a sample of pages at the leaf level, and estimates row count from the actual index structure.

This means InnoDB would see that the combination (true, true, false, false, true, false) maps to ~8,700 leaf entries, not 38. The estimate would be roughly correct, and the optimizer wouldn’t make the same bad join order decision.

PostgreSQL doesn’t do index dives for cardinality estimation. It always goes back to pg_statistic and multiplies. The composite index is invisible to the estimation layer — it’s only visible to the execution layer. This is a deliberate design choice (keeping statistics separate from access paths), but it means PostgreSQL is more vulnerable to correlated-column estimation errors than InnoDB is, even when the right composite index already exists.

Wrapping Up

• Always compare estimated vs. actual rows in EXPLAIN ANALYZE. If they diverge by more than 10x, the planner is making decisions based on a false premise.

• Composite indexes don’t fix estimation in PostgreSQL. They help the executor find rows, but pg_statistic stores per-column statistics independently. The planner still multiplies. (InnoDB’s index dives would handle this better.)

• Model state as state, not as independent booleans. If your boolean columns have mutual dependencies — if certain combinations can never occur — they belong in a single enum column.

PostgreSQL’s cost-based optimizer is remarkably good. But it operates on a statistical model of your data, not the data itself. When your schema encodes assumptions that violate the model’s assumptions, the planner makes rational decisions from irrational premises. Understanding where the model breaks is the difference between a query that runs in 3ms and one that runs in 42ms.

Binary spam detection (spam or not spam) breaks down in active communities. A forwarded giveaway could be spam or a legitimate user sharing excitement. A message saying “write me” could be a scam CTA or an angry user. We rebuilt our Telegram moderation pipeline into three action tiers using AI for intent classification: tier 1 (ban) for clear spam with profit intent, tier 2 (mute + admin buttons) for ambiguous cases, and tier 3 (3-strike warnings) for links and forwards. The system also auto-detects chat language from linked channel posts using character-set heuristics (їєґ → Ukrainian, ыэъ → Russian). How do you handle the gray area between spam and legitimate messages in your moderation systems?

My VS Code debugger broke. Took 3 days to find the dumbest fix.

Was debugging a Node script last week. Breakpoints wouldn’t hit. Console.log worked fine so I kept using that like an animal for 3 days straight.

Turns out I had the wrong launch config the whole time.

I’m working on a scraper that pulls product data. It kept returning undefined for certain fields. Console.log showed me the response looked fine, but somewhere in the parsing it was breaking.

So I did this:

console.log('response:', response);
console.log('data:', data);
console.log('product:', product);
console.log('price:', price);  // undefined - cool cool cool

Twenty console.log statements later I still couldn’t see where it was breaking. The terminal output was a mess. Scrolling through walls of JSON trying to spot the issue.

I knew VS Code had a debugger. I’d used it before on other projects. But this time the breakpoints just… didn’t work. They’d show up as gray circles instead of red. Script would run straight through them.

Googled “VS Code breakpoints not working Node.js” and tried everything:

1. Restart VS Code (did nothing)
2. Delete node_modules and reinstall (waste of 5 minutes)
3. Update VS Code (already latest version)
4. Check if source maps were the issue (I wasn’t using TypeScript so no)

Still broken. Back to console.log hell.

Turns out I was running the script with node scraper.js in the terminal. Just typing it manually every time. The VS Code debugger needs you to launch through the debug panel OR have the right launch.json config.

I had a launch.json but it was set up for a different project. It was trying to run npm start which didn’t exist in this repo. So when I clicked the debug button, nothing happened. When I set breakpoints and ran node scraper.js manually in terminal, they got ignored because that’s not an attached debug session.

Created a proper launch.json:

{
  "version": "0.2.0",
  "configurations": [
    {
      "type": "node",
      "request": "launch",
      "name": "Debug Scraper",
      "program": "${workspaceFolder}/scraper.js",
      "skipFiles": ["<node_internals>/**"]
    }
  ]
}

Clicked the debug button. Breakpoints turned red. Hit F5. Stopped exactly where I wanted.

Took 30 seconds to see the issue. I was calling .text on an element that didn’t exist. The selector was wrong. One line fix.

Spent 3 days with console.log. Took 30 seconds with the debugger.

Usually when breakpoints don’t work it’s because you’re not launching through the debugger. Running node file.js in the terminal won’t attach to VS Code’s debugger session. You need to either click the debug button or set up launch.json to point to the right file. Also make sure the “type” field matches what you’re debugging. node for Node.js, chrome for browser stuff, python for Python scripts.

The debugger shows you variable values at each step, call stack, watches. You can step through line by line. Console.log shows you one thing at one point in time. Then you add another console.log. Then another. Then you’re scrolling through terminal spam trying to find the one value you care about.

I knew this. Still kept using console.log because it was easier than fixing a config file.

On March 24, 2026, threat actor TeamPCP published two compromised versions of LiteLLM to PyPI. If you work with Python AI tooling, this one is worth understanding in detail, because the attack technique will be reused.

What Happened

Versions 1.82.7 and 1.82.8 of LiteLLM contained malicious payloads after attackers obtained the maintainer’s PyPI credentials. The credential theft wasn’t a direct attack on LiteLLM. It was the third step in a cascade:

1. March 19: TeamPCP compromised Trivy, an open-source security scanner
2. March 21: Used the compromised Trivy action to steal credentials from Checkmarx’s CI pipeline
3. March 24: Used stolen credentials from LiteLLM’s CI/CD pipeline (which ran Trivy) to publish malicious packages

The malicious versions executed in two different ways. Version 1.82.7 embedded a base64-encoded payload in litellm/proxy/proxy_server.py; it fires when anything imports litellm.proxy. Version 1.82.8 was more aggressive: it added a litellm_init.pth file to site-packages, which runs on every Python interpreter startup regardless of whether LiteLLM is imported. That includes pip install, your IDE’s language server, and python -c "anything".

Once triggered, the payload harvested SSH keys, cloud credentials, Kubernetes secrets, database configs, and .env files. On machines running Kubernetes, it attempted lateral movement by deploying privileged pods to every node and installed a persistent systemd backdoor that polls an attacker-controlled endpoint for additional binaries.

Why This Is Harder to Catch Than It Looks

Standard supply chain defenses focus on hash verification and suspicious package names. This attack bypassed both because the malicious content was published using the maintainer’s actual credentials. The hash is correct. The package name is correct. There’s nothing to flag.

The .pth mechanism in version 1.82.8 is particularly worth understanding. It’s a legitimate Python feature: files ending in .pth in site-packages are processed on every interpreter startup. Any line that starts with import gets executed. This isn’t a vulnerability; it’s how Python works. Existing supply chain scanning tools mostly look at setup.py and __init__.py. They don’t catch malicious .pth files.

Who Was Affected

LiteLLM downloads 3.4 million times per day and is present in 36% of cloud environments as a transitive dependency. You might not have installed LiteLLM directly and still have been affected. Downstream packages that pull LiteLLM transitively include DSPy, MLflow, OpenHands, CrewAI, and Arize Phoenix.

The malicious versions were live for approximately three hours before PyPI quarantined them. Detection was accidental, not by automated tooling.

What to Do

Check first: pip show litellm | grep Version

If you see 1.82.7 or 1.82.8:

• Uninstall immediately and run pip cache purge (or rm -rf ~/.cache/uv if using uv) to prevent cached wheel re-use
• Rotate every credential accessible from that environment: API keys, SSH keys, cloud credentials, database passwords
• Check for persistence artifacts: ~/.config/sysmon/sysmon.py, a sysmon.service systemd unit, files in /tmp/pglog or /tmp/.pg_state
• If Kubernetes was present: inspect kube-system namespace for unauthorized pods, review cluster audit logs

The clean version is 1.82.6.

The Broader Signal

This is part of a coordinated campaign. Three days later, the Telnyx package was hit with the same technique. TeamPCP is running systematic attacks across Python packages in the AI/ML tooling space.

There’s also one detail buried in the security post-mortems that deserves separate attention: the attackers used an AI agent called “openclaw” as part of their operational pipeline. It’s the first confirmed case of an AI agent used operationally in a software supply chain attack. The full scope of what it automated isn’t publicly documented, but its presence in the campaign means some coordination steps that previously required manual effort are now automated.

For teams running Python AI tooling in production: pin your dependencies, monitor transitive package updates, and add .pth file detection to your supply chain scanning. The gap between what automated tooling catches and what’s actually exploitable just got a bit wider.

This story is from Edge Briefing: AI, a weekly newsletter curating the signal from AI noise. Subscribe for free to get it every Tuesday.