pg.ddx.io  pgvector/pgvector GitHub issues and pull requests  
help / color / mirror / Atom feed
From: karlmoriaty (@karlmoriaty) <noreply+karlmoriaty@github.com>
To: pgvector/pgvector <noreply+pgvector-pgvector@github.com>
Subject: [pgvector/pgvector] PR #1060: Added search parameters to EXPLAIN settings
Date: Wed, 11 Nov 2026 07:47:43 +0000
Message-ID: <gh-pgvector-pgvector-1060@github.com> (raw)

Related to #1042 (the "add search parameters to EXPLAIN settings" item).

Motivation

Previously, although parameters such as hnsw.ef_search, ivfflat.probes, and the iterative scan settings played a decisive role in determining how a scan behaves, their effective values remained entirely invisible in the execution plan. Consequently, users were compelled to execute the SHOW command separately and guess which specific values applied to the query being explained, which inevitably caused considerable inconvenience.

Change

To fundamentally eliminate this issue, this update meticulously marks the index search GUCs with GUC_EXPLAIN, ensuring that these parameters will explicitly appear in EXPLAIN (SETTINGS). The detailed list is as follows:

hnsw.ef_search
hnsw.iterative_scan
hnsw.max_scan_tuples
hnsw.scan_mem_multiplier
ivfflat.probes
ivfflat.iterative_scan
ivfflat.max_probes

Furthermore, given that utils/guc.h has already been included in both src/hnsw.c and src/ivfflat.c, no additional includes are required, which significantly simplifies the implementation process.

Behavior

Once these modifications are successfully applied, users will be able to observe the exact settings simply by executing the following SQL commands:

SET hnsw.ef_search = 100;
EXPLAIN (COSTS OFF, SETTINGS) SELECT * FROM items ORDER BY embedding <-> '[1,2,3]' LIMIT 5;

inline patch 1: CHANGELOG.md, Makefile.win, src/hnsw.c and 3 more (+93/-8 lines) download inline-1.patchdiff --git a/CHANGELOG.md b/CHANGELOG.md index dfce94f52..db2cbc66b 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -2,6 +2,7 @@ - Added asynchronous I/O for indexes - Added vacuuming progress for indexes +- Added search parameters to `EXPLAIN` settings - Added `vector_dims` function for `sparsevec` - Improved performance of HNSW inserts with `sparsevec` - Improved `amvalidate` for indexes diff --git a/Makefile.win b/Makefile.win index 597108877..2813561d4 100644 --- a/Makefile.win +++ b/Makefile.win @@ -5,7 +5,7 @@ DATA_built = sql\$(EXTENSION)--$(EXTVERSION).sql OBJS = src\bitutils.obj src\bitvec.obj src\halfutils.obj src\halfvec.obj src\hnsw.obj src\hnswbuild.obj src\hnswinsert.obj src\hnswscan.obj src\hnswutils.obj src\hnswvacuum.obj src\ivfbuild.obj src\ivfflat.obj src\ivfinsert.obj src\ivfkmeans.obj src\ivfscan.obj src\ivfutils.obj src\ivfvacuum.obj src\sparsevec.obj src\vector.obj HEADERS = src\halfvec.h src\sparsevec.h src\vector.h -REGRESS = bit btree cast copy halfvec hnsw_bit hnsw_halfvec hnsw_sparsevec hnsw_vector ivfflat_bit ivfflat_halfvec ivfflat_vector sparsevec vector_type +REGRESS = bit btree cast copy halfvec hnsw_bit hnsw_halfvec hnsw_sparsevec hnsw_vector ivfflat_bit ivfflat_halfvec ivfflat_vector settings sparsevec vector_type REGRESS_OPTS = --inputdir=test --load-extension=$(EXTENSION) # For /arch flags diff --git a/src/hnsw.c b/src/hnsw.c index 32f431228..a3bdb91f1 100644 --- a/src/hnsw.c +++ b/src/hnsw.c @@ -101,21 +101,21 @@ HnswInit(void) DefineCustomIntVariable("hnsw.ef_search", "Sets the size of the dynamic candidate list for search", "Valid range is 1..1000.", &hnsw_ef_search, - HNSW_DEFAULT_EF_SEARCH, HNSW_MIN_EF_SEARCH, HNSW_MAX_EF_SEARCH, PGC_USERSET, 0, NULL, NULL, NULL); + HNSW_DEFAULT_EF_SEARCH, HNSW_MIN_EF_SEARCH, HNSW_MAX_EF_SEARCH, PGC_USERSET, GUC_EXPLAIN, NULL, NULL, NULL); DefineCustomEnumVariable("hnsw.iterative_scan", "Sets the mode for iterative scans", NULL, &hnsw_iterative_scan, - HNSW_ITERATIVE_SCAN_OFF, hnsw_iterative_scan_options, PGC_USERSET, 0, NULL, NULL, NULL); + HNSW_ITERATIVE_SCAN_OFF, hnsw_iterative_scan_options, PGC_USERSET, GUC_EXPLAIN, NULL, NULL, NULL); /* This is approximate and does not affect the initial scan */ DefineCustomIntVariable("hnsw.max_scan_tuples", "Sets the max number of tuples to visit for iterative scans", NULL, &hnsw_max_scan_tuples, - 20000, 1, INT_MAX, PGC_USERSET, 0, NULL, NULL, NULL); + 20000, 1, INT_MAX, PGC_USERSET, GUC_EXPLAIN, NULL, NULL, NULL); /* Same range as hash_mem_multiplier */ DefineCustomRealVariable("hnsw.scan_mem_multiplier", "Sets the multiple of work_mem to use for iterative scans", NULL, &hnsw_scan_mem_multiplier, - 1, 1, 1000, PGC_USERSET, 0, NULL, NULL, NULL); + 1, 1, 1000, PGC_USERSET, GUC_EXPLAIN, NULL, NULL, NULL); MarkGUCPrefixReserved("hnsw"); } diff --git a/src/ivfflat.c b/src/ivfflat.c index e9659582f..19a3f6ed2 100644 --- a/src/ivfflat.c +++ b/src/ivfflat.c @@ -53,16 +53,16 @@ IvfflatInit(void) DefineCustomIntVariable("ivfflat.probes", "Sets the number of probes", "Valid range is 1..lists.", &ivfflat_probes, - IVFFLAT_DEFAULT_PROBES, IVFFLAT_MIN_LISTS, IVFFLAT_MAX_LISTS, PGC_USERSET, 0, NULL, NULL, NULL); + IVFFLAT_DEFAULT_PROBES, IVFFLAT_MIN_LISTS, IVFFLAT_MAX_LISTS, PGC_USERSET, GUC_EXPLAIN, NULL, NULL, NULL); DefineCustomEnumVariable("ivfflat.iterative_scan", "Sets the mode for iterative scans", NULL, &ivfflat_iterative_scan, - IVFFLAT_ITERATIVE_SCAN_OFF, ivfflat_iterative_scan_options, PGC_USERSET, 0, NULL, NULL, NULL); + IVFFLAT_ITERATIVE_SCAN_OFF, ivfflat_iterative_scan_options, PGC_USERSET, GUC_EXPLAIN, NULL, NULL, NULL); /* If this is less than probes, probes is used */ DefineCustomIntVariable("ivfflat.max_probes", "Sets the max number of probes for iterative scans", NULL, &ivfflat_max_probes, - IVFFLAT_MAX_LISTS, IVFFLAT_MIN_LISTS, IVFFLAT_MAX_LISTS, PGC_USERSET, 0, NULL, NULL, NULL); + IVFFLAT_MAX_LISTS, IVFFLAT_MIN_LISTS, IVFFLAT_MAX_LISTS, PGC_USERSET, GUC_EXPLAIN, NULL, NULL, NULL); MarkGUCPrefixReserved("ivfflat"); } diff --git a/test/expected/settings.out b/test/expected/settings.out new file mode 100644 index 000000000..1fcd11b8e --- /dev/null +++ b/test/expected/settings.out @@ -0,0 +1,47 @@ +SET enable_seqscan = off; +CREATE TABLE t (val vector(3)); +INSERT INTO t (val) VALUES ('[0,0,0]'), ('[1,2,3]'), ('[1,1,1]'); +-- hnsw +CREATE INDEX ON t USING hnsw (val vector_l2_ops); +SET hnsw.ef_search = 100; +SET hnsw.iterative_scan = relaxed_order; +SET hnsw.max_scan_tuples = 10000; +SET hnsw.scan_mem_multiplier = 2; +EXPLAIN (COSTS OFF, SETTINGS) SELECT * FROM t ORDER BY val <-> '[3,3,3]' LIMIT 5; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------- + Limit + -> Index Scan using t_val_idx on t + Order By: (val <-> '[3,3,3]'::vector) + Settings: enable_seqscan = 'off', hnsw.ef_search = '100', hnsw.iterative_scan = 'relaxed_order', hnsw.max_scan_tuples = '10000', hnsw.scan_mem_multiplier = '2' +(4 rows) + +RESET hnsw.ef_search; +RESET hnsw.iterative_scan; +RESET hnsw.max_scan_tuples; +RESET hnsw.scan_mem_multiplier; +-- values at their defaults are not shown +EXPLAIN (COSTS OFF, SETTINGS) SELECT * FROM t ORDER BY val <-> '[3,3,3]' LIMIT 5; + QUERY PLAN +----------------------------------------------- + Limit + -> Index Scan using t_val_idx on t + Order By: (val <-> '[3,3,3]'::vector) + Settings: enable_seqscan = 'off' +(4 rows) + +DROP INDEX t_val_idx; +-- ivfflat +CREATE INDEX ON t USING ivfflat (val vector_l2_ops) WITH (lists = 1); +SET ivfflat.iterative_scan = relaxed_order; +SET ivfflat.max_probes = 1; +EXPLAIN (COSTS OFF, SETTINGS) SELECT * FROM t ORDER BY val <-> '[3,3,3]' LIMIT 5; + QUERY PLAN +------------------------------------------------------------------------------------------------------ + Limit + -> Index Scan using t_val_idx on t + Order By: (val <-> '[3,3,3]'::vector) + Settings: enable_seqscan = 'off', ivfflat.iterative_scan = 'relaxed_order', ivfflat.max_probes = '1' +(4 rows) + +DROP TABLE t; diff --git a/test/sql/settings.sql b/test/sql/settings.sql new file mode 100644 index 000000000..0d88c4ba1 --- /dev/null +++ b/test/sql/settings.sql @@ -0,0 +1,37 @@ +SET enable_seqscan = off; + +CREATE TABLE t (val vector(3)); +INSERT INTO t (val) VALUES ('[0,0,0]'), ('[1,2,3]'), ('[1,1,1]'); + +-- hnsw + +CREATE INDEX ON t USING hnsw (val vector_l2_ops); + +SET hnsw.ef_search = 100; +SET hnsw.iterative_scan = relaxed_order; +SET hnsw.max_scan_tuples = 10000; +SET hnsw.scan_mem_multiplier = 2; + +EXPLAIN (COSTS OFF, SETTINGS) SELECT * FROM t ORDER BY val <-> '[3,3,3]' LIMIT 5; + +RESET hnsw.ef_search; +RESET hnsw.iterative_scan; +RESET hnsw.max_scan_tuples; +RESET hnsw.scan_mem_multiplier; + +-- values at their defaults are not shown + +EXPLAIN (COSTS OFF, SETTINGS) SELECT * FROM t ORDER BY val <-> '[3,3,3]' LIMIT 5; + +DROP INDEX t_val_idx; + +-- ivfflat + +CREATE INDEX ON t USING ivfflat (val vector_l2_ops) WITH (lists = 1); + +SET ivfflat.iterative_scan = relaxed_order; +SET ivfflat.max_probes = 1; + +EXPLAIN (COSTS OFF, SETTINGS) SELECT * FROM t ORDER BY val <-> '[3,3,3]' LIMIT 5; + +DROP TABLE t; \ No newline at end of file

view thread (3+ messages)  latest in thread

Message-ID: <gh-pgvector-pgvector-1060@github.com>
Permalink:  ../gh-pgvector-pgvector-1060@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox