A match operator in the target list crosses every JIT threshold
What happens?
Putting a match operator in the target list adds the per_tuple_cost hint to the total plan cost. That crosses jit_above_cost and both jit_*_above_cost thresholds, so the query gets JIT-compiled with inlining and optimization.
On a 200-row table, adding (title) ||| 'alpha' to the SELECT list of an otherwise identical query moves the plan from cost 10.07 to 600000010.08, and execution from 0.075 ms to 38.4 ms. With jit = off the same plan runs in 0.118 ms. The qual is still absorbed into the custom scan either way; the Tantivy Query line is identical in both.
We hit this projecting column ||| query to find out which field matched. One of our application queries projects several of those plus snippets, and JIT-compiles 585 functions for 7546 ms of JIT work.
ParadeDB prints WARNING: the table is being sequentially scanned for this query for the target-list query, but not for the qual-only one.
Turning JIT off (#2406) works, and is what we do. That issue was closed pointing at #4109 and #2997, which both cover JOIN plans. This still happens on 0.25.9.
To Reproduce
Everything is at its default setting: jit on, jit_above_cost 100000, jit_inline_above_cost and jit_optimize_above_cost 500000, paradedb.per_tuple_cost 1e+08.
CREATE EXTENSION IF NOT EXISTS pg_search;
DROP TABLE IF EXISTS jit_repro;
CREATE TABLE jit_repro (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
title text NOT NULL,
content text NOT NULL
);
INSERT INTO jit_repro (title, content)
SELECT 'title-' || g, 'alpha bravo charlie ' || g FROM generate_series(1, 200) g;
CREATE INDEX jit_repro_idx ON jit_repro USING paradedb (id, title, content) WITH (key_field = 'id');
ANALYZE jit_repro;
-- A: match operator only as a qual
EXPLAIN (ANALYZE, TIMING OFF) SELECT id FROM jit_repro WHERE content @@@ 'alpha' LIMIT 5;
-- B: same query, one match operator added to the target list
EXPLAIN (ANALYZE, TIMING OFF) SELECT id, (title) ||| 'alpha' FROM jit_repro WHERE content @@@ 'alpha' LIMIT 5;
-- C: B again, with JIT off
BEGIN;
SET LOCAL jit = off;
EXPLAIN (ANALYZE, TIMING OFF) SELECT id, (title) ||| 'alpha' FROM jit_repro WHERE content @@@ 'alpha' LIMIT 5;
COMMIT;A:
Limit (cost=10.00..10.07 rows=5 width=16) (actual rows=5.00 loops=1)
Buffers: shared hit=23
-> Custom Scan (ParadeDB Base Scan) on jit_repro (cost=10.00..10.07 rows=5 width=16) (actual rows=5.00 loops=1)
Table: jit_repro
Index: jit_repro_idx
Segment Count: 1
Heap Fetches: 5
Exec Method: TopKScanExecState
Scores: false
TopK Limit: 5
Queries: 1
Tantivy Query: {"with_index":{"query":{"parse_with_field":{"field":"content","query_string":"alpha","lenient":null,"conjunction_mode":null}}}}
Buffers: shared hit=23
Planning:
Buffers: shared hit=127
Planning Time: 0.370 ms
Execution Time: 0.075 msB:
WARNING: the table is being sequentially scanned for this query, so performance may be slow
if you are not sure why, please file an issue: https://github.com/paradedb/paradedb/issues/new/choose
Limit (cost=100000010.00..600000010.08 rows=5 width=17) (actual rows=5.00 loops=1)
Buffers: shared hit=42
-> Custom Scan (ParadeDB Base Scan) on jit_repro (cost=100000010.00..600000010.08 rows=5 width=17) (actual rows=5.00 loops=1)
Table: jit_repro
Index: jit_repro_idx
Segment Count: 1
Heap Fetches: 5
Exec Method: TopKScanExecState
Scores: false
TopK Limit: 5
Queries: 1
Tantivy Query: {"with_index":{"query":{"parse_with_field":{"field":"content","query_string":"alpha","lenient":null,"conjunction_mode":null}}}}
Buffers: shared hit=42
Planning:
Buffers: shared hit=48
Planning Time: 0.210 ms
JIT:
Functions: 5
Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 38.448 msC is the same plan as B without the JIT section, Execution Time: 0.118 ms. Alternating B and C on one session gives 32-39 ms against 0.18-0.20 ms.
OS:
Container: Debian 13 (PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1)), aarch64. Host: macOS 27.0, arm64, Podman
ParadeDB Version (e.g. output of SELECT * FROM paradedb.version_info();):
0.25.9 | release
Are you using ParadeDB Docker, Helm, or the extension(s) standalone?
ParadeDB Docker Image
Full Name:
Patrick Hein
Affiliation:
none
Did you include all relevant data sets for reproducing the issue?
N/A - The reproduction does not require a data set
Did you include the code required to reproduce the issue?
- Yes, I have
Did you include all relevant configurations (e.g., CPU architecture, PostgreSQL version, Linux distribution) to reproduce the issue?
- Yes, I have
Source: paradedb/paradedb