Hi!
Table has index on name column:
CREATE TABLE firma2.klient
(
kood character(12) primary key,
nimi character(100),
...
);
CREATE INDEX IF NOT EXISTS klient_nimi_idx
ON firma2.klient USING btree
(nimi COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;
Database settings have default values:
enable_indexonlyscan on
enable_indexscan on
enable_indexonlyscan on
enable_indexscan on
Query
SELECT * FROM firma2.klient WHERE nimi='John';
Runs slowly.
analyze firma2.klient;
explain analyze select * from firma2.klient where nimi='John'
Shows that index is not used:
"Seq Scan on klient (cost=0.00..2287976.20 rows=1 width=4002) (actual time=12769.987..12769.988 rows=0 loops=1)"
" Filter: (nimi = 'John'::bpchar)"
" Rows Removed by Filter: 849971"
"Planning Time: 4.751 ms"
"Execution Time: 12770.029 ms"
How to force Postgres to use index? It probably worked long time but suddenly stopped working today.
Re-started whole windows server but problem persists.
Using
PostgreSQL 17.5 on x86_64-windows, compiled by msvc-19.43.34808, 64-bit
in Windows Server 2022 vers 21H2
Andrus.
Posted also in
https://stackoverflow.com/questions/79832965/how-to-use-index-in-simple-select
