Join Key Type Mismatches and Implicit Casts #

Two tables join on order_id. In one it is bigint; in the other, created years later by a data pipeline, it is numeric. The join works, returns correct rows, and runs as a hash join over two full sequential scans — even for a query filtering to a single order — because the index on the bigint column cannot be used for a numeric comparison.

Join algorithms depend on comparable, hashable keys, as described in hash join mechanics. A type mismatch changes which operators the planner can use, and therefore which algorithms and indexes are available.

The Planner Condition #

When a join condition compares two different types, PostgreSQL resolves an operator. Either an operator exists for the exact pair (some cross-type integer operators do), or one side is implicitly cast to the other’s type. The consequences depend on which side gets cast:

• Cast on the indexed column. orders.id::numeric = shipments.order_id wraps the indexed column in a cast. A B-tree index on orders.id is built with bigint operators; an expression over the column cannot use it. Nested loops with inner index scans disappear from the options.
• Hash eligibility. Hash joins require an operator marked hashable with compatible hash functions on both sides. Cross-type equality within an operator family (such as int4 = int8) is hashable; comparisons that require a cast to a type in a different family use the cast result as the hash key, which works but adds per-row conversion cost.
• Merge eligibility. Merge joins need both sides sorted by the same operator family. A cast forces a sort on the expression rather than using an index order.
• Estimates. Statistics describe the column, not the cast expression. Join selectivity on id::numeric falls back to less accurate estimates, often skewing the join size.

The most common mismatches in practice: bigint vs numeric from ETL tools, integer vs text for IDs stored as strings, uuid vs text, timestamp vs timestamptz, and varchar vs text with a collation difference.

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, s.carrier
FROM orders o
JOIN shipments s ON s.order_id = o.id        -- shipments.order_id is numeric, orders.id is bigint
WHERE s.tracking_no = 'ZX84412';

Hash Join  (actual time=8104.2..8104.3 rows=1 loops=1)
  Hash Cond: ((o.id)::numeric = s.order_id)          -- cast applied to the bigint side
  ->  Seq Scan on orders o  (actual rows=94022110 loops=1)
  ->  Hash  (actual rows=1 loops=1)
        ->  Index Scan using shipments_tracking_idx on shipments s  (actual rows=1 loops=1)
              Index Cond: (tracking_no = 'ZX84412'::text)
Execution Time: 8104.9 ms
-- one shipment found, yet all 94M orders scanned and hashed
-- orders_pkey on id is unusable for (id)::numeric

After aligning the types:

ALTER TABLE shipments ALTER COLUMN order_id TYPE bigint USING order_id::bigint;

Nested Loop  (actual time=0.05..0.06 rows=1 loops=1)
  ->  Index Scan using shipments_tracking_idx on shipments s  (actual rows=1 loops=1)
        Index Cond: (tracking_no = 'ZX84412'::text)
  ->  Index Scan using orders_pkey on orders o  (actual rows=1 loops=1)
        Index Cond: (id = s.order_id)                  -- no cast: index probe possible
Execution Time: 0.09 ms

Step-by-Step Resolution #

1. Search the plan for casts in join conditions. Hash Cond, Merge Cond, Join Filter and inner Index Cond lines show casts as ::type.

2. Compare column types across the join:

   SELECT table_name, column_name, data_type
   FROM information_schema.columns
   WHERE (table_name, column_name) IN (('orders','id'), ('shipments','order_id'));

3. Fix the schema where possible. Changing a column type rewrites the table and takes an ACCESS EXCLUSIVE lock; plan it like any large migration. For very large tables, add a new column, backfill in batches, and swap.

4. As a temporary measure, move the cast to the non-indexed side in the query so the index remains usable:

   SELECT o.id, s.carrier
   FROM orders o
   JOIN shipments s ON o.id = s.order_id::bigint
   WHERE s.tracking_no = 'ZX84412';

   Only do this when the conversion cannot fail or lose precision for any stored value.

5. Or add an expression index matching the cast when the schema cannot change:

   CREATE INDEX CONCURRENTLY shipments_order_id_bigint_idx ON shipments ((order_id::bigint));
   ANALYZE shipments;   -- gathers statistics on the expression too

6. Add a foreign key once types match; it prevents future drift and documents the relationship.

Before and After #

-- BEFORE: shipments.order_id numeric
Hash Join  Hash Cond: ((o.id)::numeric = s.order_id)  → Seq Scan on orders (94M rows)   Execution Time: 8104.9 ms

-- AFTER: shipments.order_id bigint
Nested Loop → Index Scan using orders_pkey  Index Cond: (id = s.order_id)               Execution Time: 0.09 ms

Mismatches that do not show a cast #

Not every mismatch prints ::. Comparisons between integer and bigint use cross-type operators in the integer family, which are index-compatible and hashable, so they rarely cause trouble. Text types are subtler: varchar and text compare without a visible conversion of the column, but collation differences can block index use when a column’s collation differs from the one the comparison resolves to, showing COLLATE in the condition instead of a cast. timestamp versus timestamptz comparisons depend on the session TimeZone and use a conversion that makes results — and index eligibility — setting-dependent. Treat any join between those pairs as suspect even when the plan looks clean.

ORMs produce many of these mismatches. A model that declares an identifier as a string, or a driver that sends parameters as numeric, turns a clean schema into cast-laden SQL; the capture methods in ORM-generated EXPLAIN output show what the database actually receives.

Common Pitfalls #

Fixing the query but not the schema. Moving a cast in one query leaves every other query exposed. Diagnostic signal: the same cast pattern appearing in several slow queries. Fix: align the column types.

Casting in the lossy direction. numeric::bigint fails or rounds for fractional values. Diagnostic signal: conversion errors or silent row loss after the rewrite. Fix: verify all stored values are integral first.

Expression index without ANALYZE. The planner has no statistics for the expression until the table is analyzed. Diagnostic signal: the new index exists but estimates remain default. Fix: ANALYZE after creating it.

Parameters bound as a different type. A driver sending $1 as numeric to compare with a bigint column reproduces the problem per query. Diagnostic signal: the cast appears only in application plans. Fix: bind the parameter with the column’s type.

Frequently Asked Questions #

Why is my join not using the index on the join column? #

Check the join condition in EXPLAIN for a cast such as (o.id)::numeric. When the indexed column itself is converted to another type, its index cannot be used, and the planner falls back to scanning and hashing the whole table.

Is joining int to bigint a problem? #

Usually not. Integer types share an operator family with cross-type operators, so comparisons need no cast on the column and remain index-compatible and hashable. Mismatches across families — bigint to numeric, integer to text, uuid to text — are the ones that hurt.

Can I fix a type mismatch without altering the table? #

Yes, temporarily: rewrite the condition to cast the non-indexed side, or create an expression index on the cast and analyze the table. Aligning the column types is the durable fix.

Related #

• Hash Join Mechanics — parent guide: what makes a join hashable
• When Does the Optimizer Choose a Hash Join — sibling: eligibility and cost thresholds
• Filter Pushdown Mechanics — how wrapped columns lose index conditions in filters too

Up: Hash Join Mechanics