Why does my SQL query return fewer rows than expected without an error? (LIMIT, NOT EXISTS, VOLATILE function)

Three SQL queries run fine, raise no error and still return a wrong or expensive result: a LIMIT applied before the filter, a NOT EXISTS inside an INSERT ... SELECT, and a VOLATILE function in the WHERE clause. All three showed up in a production system I maintain, and all three pass a simple test because the test data is small. Below are the SQL I ran on Postgres 16 and the fix for each.

1. Listing with LIMIT and filtering afterwards drops the tail

According to the documentation, the evaluation order of a SELECT is: WHERE removes rows, then comes the sort, and LIMIT applies last. That holds inside a single query. When you ask for "the latest 1,000" in one query and filter in application code or in an outer query, the filter runs after the cut.

create table pedidos(id serial primary key, cliente int, status text, criado timestamptz default now());
insert into pedidos(cliente, status)
select 1 + (g % 3), case when g % 50 = 0 then 'pago' else 'novo' end
from generate_series(1, 5000) g;

-- filters after cutting
select count(*) from (
  select * from pedidos order by criado desc, id desc limit 1000
) t where status = 'pago';   -- 20

-- filters in the query
select count(*) from pedidos where status = 'pago';   -- 100

In my run, the first query returned 20 and the second returned 100. Four fifths of the paid orders vanished and nothing warned me. In production I saw the same shape in a routine that asked the data source for the latest records of everything, with a fixed limit, and filtered by type afterwards: part of the records of one type never showed up. It also happens when the limit is applied by an API or by the ORM.

Fix: put the filter in the query itself (where status = 'pago'), and if the result is still larger than the limit, paginate to the end. The documentation warns that LIMIT without a unique ORDER BY returns an unpredictable subset, so sort by a column with a tiebreaker, such as order by criado, id.

2. NOT EXISTS in an INSERT SELECT creates duplicates within the same statement

The idea looks safe: insert one notice per conversation, only if no notice for that conversation exists yet.

create table eventos(id serial primary key, conversa int, texto text);
create table avisos(conversa int, texto text);
insert into eventos(conversa, texto) values (10, 'a'), (10, 'b'), (11, 'c');

insert into avisos(conversa, texto)
select conversa, texto from eventos e
where not exists (select 1 from avisos a where a.conversa = e.conversa);

select count(*) from avisos;   -- 3

The result was 3 rows, two of them for conversation 10. The Postgres documentation says that under Read Committed, a SELECT sees a snapshot of the database taken when the query starts. The NOT EXISTS is evaluated against that snapshot, which does not include the rows the statement itself is inserting; that is my reading of the behavior, and it is what the test showed. So the check only holds between executions, never within one. That matters when the same batch contains two rows with the same key, a common case when the same customer sends two messages in a row.

Fix 1: reduce the batch to one row per key before testing.

truncate avisos;
insert into avisos(conversa, texto)
select distinct on (conversa) conversa, texto from eventos e
where not exists (select 1 from avisos a where a.conversa = e.conversa)
order by conversa, id;
-- 2 rows; running it again inserts 0

DISTINCT ON keeps the first row of each conversation, and the documentation notes that "first" is only predictable with ORDER BY, which is why there is an order by conversa, id.

Fix 2: let the database block the duplicate with a unique index and ON CONFLICT DO NOTHING.

truncate avisos;
create unique index on avisos(conversa);
insert into avisos(conversa, texto)
select conversa, texto from eventos order by id
on conflict (conversa) do nothing;
-- 2 rows: (10, 'a') and (11, 'c')

This second form also protects against two simultaneous executions, which NOT EXISTS does not cover at all.

3. A VOLATILE function in WHERE runs once per row

A function that returns a value and records the date of the last read has to write to the database, so it is VOLATILE. Used directly in the WHERE clause, it becomes a per-row cost.

create table leituras(nome text primary key, cliente int, ultima timestamptz);
insert into leituras values ('abc', 1, null);
create table lancamentos(id serial primary key, cliente int);
insert into lancamentos(cliente) select 1 + (g % 1000) from generate_series(1, 10000) g;
create index on lancamentos(cliente);
analyze lancamentos;

create function cliente_atual(p_nome text) returns int language sql volatile as
$$ update leituras set ultima = now() where nome = p_nome returning cliente $$;

explain select count(*) from lancamentos where cliente = cliente_atual('abc');

The plan came out as a Seq Scan with Filter: (cliente = cliente_atual('abc'::text)), and the index was not used. The documentation explains why: a query with a volatile function re-evaluates it at every row where the value is needed, while an index scan condition evaluates the comparison value only once, so a VOLATILE function is not valid there. In my test, with 10,000 rows, the query took about 2 seconds and made 10,000 updates to the leituras table (I checked with the n_tup_upd statistic). In production, the same trap multiplied the writes by the number of rows read.

Fix: wrap the call in a scalar subquery.

select count(*) from lancamentos
where cliente = (select cliente_atual('abc'));

That plan shows an InitPlan and a Bitmap Index Scan with Index Cond: (cliente = $0): the function runs once and the index is back. In the same test, it took under 10 milliseconds and made a single update. In plpgsql, the equivalent is to store the value in a variable before the query. If the function only reads and never writes, declare it STABLE, which the documentation says is safe in an index scan condition.

How to catch all three before production

Always compare the total on a screen or report with a count taken directly in the database, run EXPLAIN on every query that uses a function, and test inserts with a batch that deliberately repeats a key. A plausible result passes every test that only checks whether the query ran.

If your totals do not add up or screens got slow for no obvious reason, this is the kind of investigation I do for dashboards and data.