pg_lion benchmarks

Times and relative speeds from a synthetic benchmark. Absolute times depend on the machine; the ratios carry over better. “5× faster” means the other index took five times as long as lion. Times are milliseconds of query execution.

Setup. The extension at 59f2bac (so pg_lion.recheck_group_tid_cost at 1; the default of 2 picks the same plan for the one query it affects), PostgreSQL 18.6 (packaged, -O2), an 8-core Ryzen 7 5700X3D under WSL2 with 23 GB RAM, focused profile. Lion preloaded, so its indexes use lion’s own WAL format. Shared buffers 2 GB, which holds the tables and every index, work_mem 64 MB, parallel query and JIT off (index builds may still use parallel workers), warm cache. Each query is the median of seven timed runs after two warmups, measured as EXPLAIN ANALYZE execution time. Every result is checked against a sequential scan, and all 394 checks passed. The default planner chooses the plan.

Data. The scalar table has 5M rows (1M-row results are in the full report and show the same pattern; where lion and B-tree are close, the winner can differ between the two sizes) and one single-column index per column: B-tree, GIN (btree_gin) or lion. Columns hold 2, 20, 200, 20,000 and 1M distinct values, plus a skewed column (90% one value) and a column that is 10% NULL. The documents table has 200k rows with a text[] and a tsvector, indexed by GIN or lion (store_positions = true).

Full report: REPORT.md · HTML · CSV.

Counts (5M rows, vacuumed)

Query Lion ms B-tree ms GIN ms Lion vs B-tree Lion vs GIN
Count a value in 50% of rows 0.21 168 515 787× faster 2,419× faster
Count the hot value of a skewed column (90%) 0.23 312 486 1,368× faster 2,130× faster
Count a value in 0.5% of rows 0.13 1.67 22.6 13× faster 174× faster
Count a value in 0.005% of rows 0.025 0.035 0.27 1.4× faster 11× faster
Count a value of a 1M-value column 0.017 0.022 0.036 1.3× faster 2.1× faster
Two equality filters ANDed 0.43 19.8 205 46× faster 477× faster
Three selective equality filters ANDed 0.073 1.77 3.24 24× faster 44× faster
Two IN lists ANDed 2.42 44.1 92.5 18× faster 38× faster
Two columns ORed 0.94 111 124 119× faster 132× faster
IN list of 10 values 0.10 0.19 2.82 1.8× faster 27× faster
IN list of 1,000 values 9.04 17.6 119 2.0× faster 13× faster
IS NULL (10% of rows) 0.24 32.3 333 133× faster 1,371× faster
Range over 100 of 20,000 values 0.88 1.70 288 1.9× faster 328× faster

Lion’s lead grows with the number of rows a count matches. On the most selective values, a few to a few hundred rows, all three finish in under a millisecond and B-tree is within a few hundredths of a millisecond of lion.

GROUP BY (5M rows, vacuumed)

Query Lion ms B-tree ms GIN ms Lion vs B-tree Lion vs GIN
Count per group, 2 groups 0.45 439 724 985× faster 1,624× faster
Count per group, 200 groups 21.6 443 843 21× faster 39× faster
Count per group, 20 groups, with a filter 13.8 21.0 23.9 1.5× faster 1.7× faster

Unvacuumed tables (5M rows)

The same counts after updating 1% of rows, scattered, without a VACUUM. Lion has to check the affected pages in the heap, so its lead shrinks. It doesn’t disappear.

Query Lion ms B-tree ms GIN ms Lion vs B-tree Lion vs GIN
Count a value in 50% of rows 86.2 372 525 4.3× faster 6.1× faster
Count a value in 0.5% of rows 3.91 7.45 24.1 1.9× faster 6.2× faster
Two equality filters ANDed 3.39 17.4 212 5.1× faster 62× faster
Count per group, 200 groups 334 854 876 2.6× faster 2.6× faster

Fetching rows (5M rows, vacuumed)

Queries that read the matching rows (sum(id), sum(length(payload))), so every index has to visit the heap. Lion is level with B-tree on the single key of the 1M-value column and up to 1.8× faster on the rest, and faster than GIN in every case.

Query Lion ms B-tree ms GIN ms Lion vs B-tree Lion vs GIN
A value of a 1M-value column 0.031 0.031 0.042 same 1.4× faster
A value in 0.005% of rows 0.21 0.27 0.29 1.2× faster 1.4× faster
A value in 0.5% of rows 10.9 19.7 24.9 1.8× faster 2.3× faster
A value in 5% of rows 83.7 117 133 1.4× faster 1.6× faster
Two filters ANDed 11.9 13.8 30.8 1.2× faster 2.6× faster
Three filters ANDed 12.3 15.1 31.7 1.2× faster 2.6× faster
Two IN lists ANDed 37.1 42.5 93.8 1.1× faster 2.5× faster

Ordered queries with LIMIT (5M rows, vacuumed)

Query Lion ms B-tree ms Lion vs B-tree
WHERE a = .. AND b = .. ORDER BY c LIMIT 10 (0.25% match) 1.31 4.99 3.8× faster
The same, ORDER BY c DESC 1.17 3.43 2.9× faster
WHERE b = .. ORDER BY c LIMIT 10 (50% match) 0.040 0.041 same
WHERE c >= .. ORDER BY c LIMIT 100 on one column 0.073 0.020 3.6× slower

Lion walks the ORDER BY column’s index in order and skips rows its filter rules out (LionOrdered). A B-tree’s index-only scan on the one column wins the last case: keep a B-tree for plain “first N by this column”.

Arrays and full-text search (200k documents, vacuumed)

Query Lion ms GIN ms Lion vs GIN
Count tags @> '{t1}' (5% of rows) 0.031 4.35 140× faster
Count tags @> '{t1,t17}' 0.073 1.05 14× faster
Count tags && '{t1,t17,t123}' 0.11 7.20 67× faster
Count tsv @@ 'common' (nearly every row) 0.039 31.8 816× faster
Count tsv @@ 'w1 & w17' 0.075 1.14 15× faster
Count tsv @@ '(w1 | w2) & (w17 | w18)' 0.18 3.86 22× faster
Count a rare word 0.019 0.038 2.0× faster
Count a phrase, common <-> w1 2.52 7.60 3.0× faster
Count a weight, w1:D 0.44 5.40 12× faster
Count a NOT, common & !w1 9.54 39.0 4.1× faster
Count a prefix, rare12:* (111 words) 0.63 1.72 2.7× faster
Fetch the rows matching w1 2.62 4.83 1.8× faster

Lion answers a prefix as the OR of the indexed words that start with it, up to 1000 words. A prefix that matches more words is rechecked row by row; this benchmark does not measure that case.

Ranked search: top 10 by score

Top 10 rows for an OR of words, over 500k synthetic documents of 20–200 words from a Zipf-like 50,000-word vocabulary (script, results). GIN finds every match, scores it with ts_rank and sorts. Lion’s LionBm25 scan scores from the index and skips rows that can’t reach the top 10. Each run is on a new connection.

Query Matches Lion BM25 ms GIN + ts_rank ms Lion vs GIN
One common word and two rare words 492,031 11.9 622 52× faster
Two mid-frequency words 32,934 6.88 97.6 14× faster
Three common words 499,985 52.2 686 13× faster
Two rare words 584 3.74 3.25 1.2× slower
Two rare words ANDed 18 0.57 0.56 same

When the query’s words together hold under about 0.75% of the rows, as in the last two queries, sorting the matches beats the ranked walk, so lion does not offer it (pg_lion.bm25_walk_min_share, less when the tables and index outgrow effective_cache_size), and both sides are a scan and a sort.

Nearest neighbours: lionvec against HNSW

The ten nearest of a query by cosine distance, ORDER BY embedding <=> $1 LIMIT 10, over 975,000 OpenAI ada-002 embeddings of DBpedia entities (1536 dimensions; Qdrant’s dbpedia-openai-1M-1536-angular set and the first 100 of its test queries), optionally under WHERE u < s, a uniform random column with a lion index that keeps a share s of the rows (script, results). lionvec built with partitions = 1974 (2 sqrt(n)) and spill = off (the defaults are 8 sqrt(n) partitions with each row in a second partition as well, spill = on: on 115,000 text-embedding-3-large rows those read 1.8 times fewer code pages for 0.95 recall, in the same time, and 1.25 times fewer for 0.98, taking 1.3 times longer, and make the index about 18% larger; results), against pgvector 0.8.6’s HNSW on embedding::halfvec(1536) (m 16, ef_construction 64). PostgreSQL 16, 4 vCPUs, 16 GB RAM, shared_buffers 5 GB, and pgvector’s vector distance functions at COST 100, as lionvec’s setup asks. Recall is the share of the true ten returned, against an exact numpy search. Both indexes are approximate.

Warm (everything in memory), median of 100 queries, 95th percentile and recall. lionvec at its defaults (margin 3, patience 30), at margins 2 and 4, and with the pause off:

Filter lionvec (defaults) margin 2 margin 4 no pause HNSW ef_search 40 HNSW ef_search 100
none 7.9 ms (16), 0.979 6.6 (15), 0.964 9.1 (18), 0.983 14 (68), 0.984 4.7 (6.5), 0.900 8.1 (11), 0.954
10% 13.5 (29), 0.978 15 (29), 0.974 15 (28), 0.979 60 (123), 0.996 8.7 (14), 0.931 8.0 (15), 0.939
1% 24 (40), 0.981 27 (41), 0.981 27 (38), 0.981 109 (136), 1.000 37 (62), 0.947 41 (66), 0.950
0.1% 9.9 (11), 1.000 13 (18), 0.999

HNSW under a filter uses its iterative scan; without it, it returns 3.9 of the ten rows at 10% and 0.4 at 1%. At 10% the planner chose lionvec’s plain scan with a Filter, at 1% LionOrdered, and at 0.1% a sort of the 953 matching rows, for either index (the plain scan with a Filter is a plan only under pg_lion.vector_postfilter = allow, DESIGN.md §44.6). The pause stops a scan once 30 times sqrt(n) / 2 rows, here 30 partitions' worth, have brought nothing nearer; without it a query whose nearest rows lie far from every centroid reads hundreds of partitions (a mean of 254 of the 1,974 unfiltered, 77 with it), and the 95th percentile was five times the median, where with it it is twice.

Cold (PostgreSQL restarted and the OS page cache dropped before each query), median of 5:

Filter lionvec HNSW ef_search 40 (iterative under a filter)
none 85 ms 118 ms
1% 196 ms 1,254 ms
0.1% 210 ms 251 ms (both: the planner sorts the filter’s rows)

Memory-capped (a stream of queries, PostgreSQL and its page cache held to 1 or 2 GB by a memory cgroup with shared_buffers at 128 MB, the cache filled first), median of 100 and the pages each query read from disk:

Memory Filter lionvec HNSW ef_search 40 (iterative under a filter)
1 GB none 19 ms, 100 pages 51 ms, 767 pages
1 GB 1% 28 ms, 0 pages 691 ms, 9,229 pages
2 GB none 13 ms, 41 pages 41 ms, 672 pages
2 GB 1% 30 ms, 0 pages 465 ms, 7,853 pages

lionvec’s working set is the centroids, the one-bit codes of the partitions queries probe, and the few rows they refine and fetch; under the 1% filter it never filled the cgroup. HNSW reads 650 to 800 random pages a query without a filter and 8,000 to 9,000 under one. This machine’s disk answers a random read in well under a tenth of a millisecond; on slower storage the page counts are what to compare.

Size and build. lionvec is 1.7 GB, of which an unfiltered query reads about 9 MB of one-bit codes; HNSW is 3.8 GB. lionvec builds in 19 minutes with the table cached, nearly all of it k-means and the assignment of every row to its nearest of the 1,974 centroids.

Times vary between runs by up to a fifth.

Size, build and writes (5M rows, all seven indexes)

B-tree GIN Lion
Index size 256 MiB 157 MiB 118 MiB
Build time 11.7 s 8.4 s 5.4 s
Insert 50k rows 1.1 s, 120 MiB WAL 0.4 s, 61 MiB WAL 1.5 s, 104 MiB WAL
Update an indexed column in 50k rows 1.5 s, 139 MiB WAL 0.6 s, 78 MiB WAL 1.7 s, 125 MiB WAL
Delete 50k rows 0.7 s 0.6 s 0.7 s
VACUUM 1.4 s 1.8 s 1.5 s

Lion is 54% smaller than B-tree and 25% smaller than GIN here, and builds in under half B-tree’s time. Inserts are about 1.4× slower than B-tree but write about 13% less WAL; an indexed update is about 1.2× slower and writes about 10% less WAL. GIN’s writes look cheap because it defers work to its pending list.

On the documents table, lion’s tsvector index with positions is 2.3× the size of GIN’s (10.0 vs 4.3 MiB). Lion’s text[] index is the same size as GIN’s (2.1 MiB).

Reproducing

Install pg_lion into a release PostgreSQL build, then:

python3 bench/comprehensive/run.py --prefix /path/to/postgresql --preload --repeats 7 --warmups 2 --output bench/results/my-run
python3 bench/comprehensive/report.py bench/results/my-run      # needs matplotlib
python3 bench/comprehensive/audit.py bench/results/my-run
python3 bench/ranking/run.py --dsn 'dbname=rank' --docs 500000 --repeats 9 # needs pg_lion in that database

For the nearest-neighbour benchmark, load the vectors into items(id int, u float8, embedding vector(1536)) with u uniform in [0, 1), build pgvector’s HNSW index on (embedding::halfvec(1536)) halfvec_cosine_ops, and save the queries and their exact similarities to every row as Q.npy and S.npy (see the script’s header). Then:

python3 bench/vector/knn.py setup     # the lionvec and lion indexes
python3 bench/vector/knn.py warm 100  # needs pg_prewarm
python3 bench/vector/knn.py cold 5    # restarts the cluster; run as root to drop the page cache
# as root, with KNN_CGROUP a memory cgroup directory it may use:
python3 bench/vector/knn.py capped 1024 100   # PostgreSQL held to 1 GB of memory

The suite runs its own temporary cluster and must run as a non-root user. It takes about 10 minutes, and the ranking script about 3. See bench/comprehensive/README.md for options.