PostgreSQL derives equalities through equivalence classes but never
inequalities.  For the indexer's transaction pages

    FROM txn t JOIN block_header h ON t.round = h.round
    WHERE t.round >= $1 ORDER BY t.round, t.intra LIMIT n

nothing tells the planner that h.round >= $1, so a plan that drives the
join from block_header scans that index from its first key and probes txn
for every round below $1.  With an accurate n_distinct on round that plan
is the cheapest LIMIT plan on paper and the slowest one in practice
(6-12 s instead of 1-3 ms on a 3.7B-row table).

ineq_propagation.c adds, before planning, every  column OP value  condition
(OP one of <, <=, >=, >) to all columns joined to it with = of the same
type and operator family in the WHERE clause or an inner join.  Only
conditions that hold for every output row are used and produced (nothing
at or below the nullable side of an outer join), so results never change.
The value side must be safe to evaluate twice; uncorrelated sub-selects
become a second init plan.  GUC pg_algorand.propagate_inequalities.

Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BAkGJyPG5VRajhBwFRPiQ2

Sections 1-6 are the original brief (problem, verified facts, design,
milestones, acceptance). Section 7 records how the 2.1 implementation
departed from the brief (planner hook instead of a support function) and
section 8 the first production deployment with measurements and the
plan-cache finding fixed in 4faf1f5. Host names, paths and other
environment specifics are left out.

Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BAkGJyPG5VRajhBwFRPiQ2

With server-side prepared statements the plan cache builds a generic plan
after five executions. For substring(x,1,N) = $1 with N smaller than the
index prefix K, no value is available at plan time, so nothing can be
derived and the plan falls back to an unindexed scan. Under LIMIT with
default selectivity that scan is estimated cheap (cost ~12k on mainnet vs
the real crawl over billions of rows), so plancache adopts it and the
prefix index is never used again for that statement.

The hook now adds a large penalty to the total cost of any plan built with
an unbound external Param on a N < K prefix clause, which is exactly the
generic-plan case. plancache compares that against the average custom plan
cost and keeps replanning with the actual value, where the range on the
prefix index is derived as before. N >= K statements are unaffected.

Observed on the ush mainnet indexer: EXPLAIN EXECUTE x7 for N=3 switched
from the index range scan to a txn_pkey filter on the sixth execution.

Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BAkGJyPG5VRajhBwFRPiQ2

The indexer's note search is
  substring(decode(txn->'txn'->>'note','base64') from 1 for N) = $3
with N a per-request literal (7 in ~84% of cases, 3 and 6 next, tail up
to 34). PostgreSQL only matches an index when the indexed expression is a
direct operand of the operator, so no support function or opclass can
help here and the query never used an index for any N but the one baked
into an expression index.

note_prefix.c installs a planner_hook that rewrites such clauses against
any existing substring(x,1,K) btree expression index on the table:
  N > K   -> P = substring(B,1,K), original kept as recheck filter
  N = K   -> P = B (substr(), FROM 0 FOR K+1, reversed operands)
  N < K   -> P = B if len(B) < N, else B <= P < successor(B)
             (no upper bound when B is all 0xFF)
For N <= K the derived form is an exact three-valued equivalent and
replaces the clause, which keeps the row estimates honest. Works for
literals and for bound parameters in custom plans; generic plans get the
N >= K derivation only. Derived clauses are built from the query's own
expression so outer-join nulling rels are preserved. Runtime switch:
SET pg_algorand.prefix_rewrite = off. Needs the library preloaded
(shared_preload_libraries or session_preload_libraries); does nothing
otherwise.

Also adds the regression suite the Makefile already referenced (basic
function tests plus 30 plan/equivalence cases for the rewrite), the
2.0->2.1 upgrade script, and README docs for the index and the
CREATE STATISTICS object the planner needs alongside a partial index.

Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BAkGJyPG5VRajhBwFRPiQ2

The module previously linked a cgo c-archive (go-algorand-sdk) whose Go
runtime booted at library load time in every backend and parallel worker
that touched any extension function - spawning threads and installing
signal handlers inside PostgreSQL processes, a likely cause of stuck
parallel index (re)builds on expression indexes using AddressBin2Txt.

The LogicSig escrow address is just sha512/256("Program" || bytecode),
so getLookupLSIG + lsig.Address() are reimplemented in ~100 lines of C
(nfd_lsig.c). C symbols and SQL signatures are unchanged. Verified
bit-for-bit against go-algorand-sdk v2.11.0 output for 8 test vectors
including multi-byte uvarint lengths, negative and max app ids, and the
binary decode->encode roundtrip. Library shrinks from ~4 MB to 88 KB
and links only against libc.

Also:
- extension version 2.0 with pg_algorand--2.0.sql and a 1.0 -> 2.0
  migration marking the NFD functions PARALLEL SAFE (now true)
- fix GetNFDSigRevAddressBinLSIG using VARDATA() on a PG_GETARG_*_PP
  argument (wrong for short-header varlenas)
- deduplicate the base32 address encoder (shared by AddressBin2Txt,
  TxnBin2Txt and the NFD functions)
- remove functions.go, go.mod, go.sum and the Go build step

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01LMWXZPMWguXT1jDcGw4S4Z

Ships the tested upgrade script so clusters carried through pg_upgrade at
0.2 can reach 1.0 with ALTER EXTENSION pg_algorand UPDATE, without the
drop/recreate dance. CREATE OR REPLACE upgrades the two functions with
dependents in place; the three GetNFD* functions are dropped and
recreated because 1.0 renamed their parameters. Verified on betanet,
testnet x2 and mainnet upgrades (identical to a fresh 1.0 install).

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_019ACfCvu2UPpd6UcoK8cpW3