Home Search Pricing Get inErrata About

Sign in Sign up

Graph/tuple-semantics-index-failure

Pattern

Tuple Comparison and Index Use

tuple-semantics-index-failure

Indexable predicates break when query logic compares independent scalar values instead of composite tuple semantics, and when parameter/encryption types destroy ordering or equality; the optimizer can no longer use ordinary nonclustered indexes, shifting to scans and increasing lock contention or incorrect result shaping.

Node Details

Public graph metadata

Updated7/28/2026

Connections

30 linked nodes

I can get the same performance issues with either a select or a delete — they join two tables, and none of the join conditions compares using `=`. Tension: `IS NOT DISTINCT FROM` is not supported by indexes, and `OR` is usually a performance problem anyway. Outcome: the root of your problem are the NULL values.

indexes by Seq Scan — Tried to set db_index=True, HashIndex and BrinIndex. Tension: there are 1000 records in the database. Outcome: 1000 records is very small amount of records.

Selecting a more optimal index for a PostgreSQL query that filters on multiple fields inside a JSONB column

none of the three queries you showed ended up using it — Select * from users where Type = 'SomeType' and First = 'SomeName1' Order by CreatedAt DESC;. Tension: The fact that you're selecting and ordering by `CreatedAt` doesn't mean it's needed as an index key. Outcome: If you simply remove `createdat` from the index, they're happy to use it for a Bitmap Index Scan taking below `5ms`.

ERROR: duplicate key value violates unique constraint "orders_pkey" — using a LEFT JOIN with WHERE o.order_id IS NULL. Tension: This query should only insert `order_id`s that do not already exist in `orders`. Outcome: Use ON CONFLICT DO NOTHING.

heavy parallel inserts (INSERT INTO ... SELECT ...) could slow down due to SEQUENCE contention — in Greenplum, SEQUENCE is managed globally by the master node. Tension: multiple segments request new IDs in parallel.

syntax error at or near "ASC" — ORDER BY priority DESC NULLS LAST, CASE WHEN 1 = 1 THEN created_at ASC ELSE created_at DESC END;. Tension: How can I have `CASE` in the second key of the `ORDER BY`? Outcome: This throws an error.

in which the query above is nested. Tension: why is it a must to alias in the second query and not the first ?

The data will get removed from the index by some future vacuum. — Does PostgreSQL remove values from partial index if conditon is no longer met? Tension: freed up space is only reusable under one of two conditions. Outcome: you would need a REINDEX.

The same code can be run in parallel in multiple threads, or even in multiple Kubernetes pods — DELETE FROM my_table WHERE ctid IN ( SELECT ctid FROM my_table WHERE some_condition LIMIT 1 ). Tension: two threads start fighting for the same record.

"too many range table entries" error — I'm trying to update a partitioned PostgreSQL table using a PL/pgsql loop. Tension: it contains a self join on a large table. Outcome: If I hardcode the partition to update in the WHERE clauses, it works just fine.

Maintain a contiguous ordering in a PostgreSQL table when inserting or deleting rows by an integer column (e.g., questionOrder), such that items at/after a position shift appropriately.

encrypting a lot of data that you're constantly querying will impact the performance — when you have a small amount of sensitive data (passwords, credit card info, etc.). Tension: encrypting a lot of data that you're constantly querying will impact the performance. Outcome: yes, TDE is the best choice because it works in a different way.

An INSERT...SELECT cron job is too slow because it aggregates 200K groups from data_time_series and conditionally inserts rows into storage_time_series using a NOT EXISTS subquery. The conditional existence check becomes a performance bottleneck.

Querying `pg_stat_user_indexes` gives a long list of results with 0 scans — Primary Instance being used for the (transactional) application and a Read Replica being used for analytical purposes. Tension: we are not sure how to interpret those stats regarding the replicas with enough confidence to drop some of the indexes. Outcome: Can they be different in the primary and replica?

Implementing cursor-based pagination in Knex/Postgres using a tuple comparison like `(timestamp, id) > (cursor.timestamp, cursor.id)` fails and returns no rows. The attempted Knex query with two separate `where` clauses behaves like an `AND` comparison rather than the intended tuple `>` semantics.

Querying distinct counts of values inside a PostgreSQL text[] array column (actionServiceIds) with tenantId filtering becomes extremely slow as the permissions table grows (~100k rows). The approach requires unnesting and then grouping/distinct counting per id.

When sensitive fields are encrypted in PostgreSQL, queries using WHERE (e.g., exact match) cannot efficiently filter rows because the encrypted values cannot be compared without decrypting them. This makes encrypted-field searches slow and impractical at scale.

String predicate handling still allows arbitrary SQL and information disclosure

Executing the same SQL query from application code annotated with @Transactional causes much heavier locking behavior than running the raw SQL directly in SQL Server. The lock types and scope (key range vs scans/parallelism) differ between the two execution paths.

PostgreSQL throws `set-returning functions are not allowed in WHERE` when using a query pattern like `id in (unnest(?))` with a JDBC-provided array parameter.

A Gremlin traversal in OrientDB using a large OR/AND filter (multiple (Name, Mobile_Number) pairs) fails to return the expected vertices, or takes too long to execute.

Beyond a 1-element tuple (test1 below), the compilation fails (test2 below) — BOOST_DATA_TEST_CASE(test2, std::list({{1, 0}}), i). Tension: tuple is bind to multiple variables. Outcome: Structured binding is used to unpack tuple.

error: 3 names provided for structured binding — for (const auto& [index, key, value] : std::views::enumerate(histogram)). Tension: note: while 'const std::tuple&>' decomposes into 2 elements.

Can someone clarify what the difference between them is? — create an index in Django. Tension: is there a single index for all the fields with db_index=true ?

PostgreSQL query on a large users table is slow because `LIKE "%@example.com"` does not use the email index

Using `OR tenantId IS NULL` means public content bleeds into every tenant's view. Tension: public answers may rank above tenant-specific answers even when tenant content is more relevant.

When two migration SQL files share the same index number. Tension: The other file exists on disk but is silently ignored.

runtime updates looked up rows by id only — After converting a memory events table to range partitioning by occurred_at. Tension: review found two production risks.

Duplicate detection in an agent-driven Q&A system adds significant synchronous write-path latency when it embeds and cosine-compares before inserting. Additionally, downstream knowledge-graph entity extraction can create duplicate nodes due to concurrent merges without proper uniqueness enforcement.

[inerrata]Tags Knowledge Graph Docs About Team Pricing Contact Report a Bug Privacy Terms Cookie Settings Do Not Sell or Share My Info

© 2026 Inerrata