How to use index in simple select

Question

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
cpu_index_tuple_cost       0.005                                                       
cpu_tuple_cost             0.01

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

I don't know Postgresql very well, but do you really need to specify a collation for the index column? I'd expect it to by default have the same collation as the table column, the one used when evaluating search conditions. — jarlh
– jarlh, Commented Nov 28 at 23:19
I tested this on my local instance of PG 18.0, and it does do a Seq Scan, but eventually if I load different test data, it does a Bitmap Heap Scan. It depends on the number of rows and the proportion of rows that match nimi='John'. — Bill Karwin
– Bill Karwin, Commented Nov 29 at 0:52
You don't usually force index use, usually the optimiser can do a pretty good job — Dale K
– Dale K, Commented Nov 29 at 3:08
select indisready, indisvalid,* from pg_index where indexrelid = 'firma2.klient_nimi_idx'::regclass; Someone or something could have disabled it in the catalogs. Here's a fiddle if anyone wants to try and break this in other ways. — Zegarek
– Zegarek, Commented Nov 29 at 8:18
It looks like reindex table firma2.klient restored index scan. create index concurrently was used when lot of transactions using this table where running. — Andrus
– Andrus, Commented Nov 29 at 17:10

hasanadiguzel · Accepted Answer · 2025-12-19 05:23:05Z

PostgreSQL is not using the index by mistake — it is choosing a Sequential Scan because it believes it is cheaper. However, in your case, that cost estimation is misleading due to several important factors.

Main issue: `CHAR(100)` usage

nimi character(100)

CHAR(n) (bpchar) is fixed-length and PostgreSQL right-pads values with spaces.

Example:

'John' → 'John··························'

This causes:

Poor statistics
Wrong selectivity estimates
Planner thinking the index is ineffective

👉 Using CHAR(n) is strongly discouraged in PostgreSQL.

Fix

ALTER TABLE firma2.klient
ALTER COLUMN nimi TYPE varchar(100)
USING rtrim(nimi);

-- or better
ALTER COLUMN nimi TYPE text
USING rtrim(nimi);

Then:

DROP INDEX klient_nimi_idx;

CREATE INDEX klient_nimi_idx
ON firma2.klient (nimi);

ANALYZE firma2.klient;

After this, PostgreSQL will use Index Scan.

`SELECT *` makes index scans expensive

Execution plan shows:

width=4002

Meaning 4 KB per row.

Planner logic:

“If I must fetch the entire row anyway, sequential scan is cheaper than index lookup + heap fetch.”

Test:

EXPLAIN ANALYZE
SELECT kood
FROM firma2.klient
WHERE nimi = 'John';

You’ll likely see Index Scan.

Column statistics are insufficient

Default statistics_target = 100 may not be enough.

ALTER TABLE firma2.klient
ALTER COLUMN nimi SET STATISTICS 1000;

ANALYZE firma2.klient;

Force index usage (testing only)

⚠️ For testing purposes only.

SET enable_seqscan = off;

EXPLAIN ANALYZE
SELECT * FROM firma2.klient
WHERE nimi = 'John';

If the index is used here, the problem is planner cost estimation, not the index.

Collectives™ on Stack Overflow

How to use index in simple select

1 Answer 1

Main issue: `CHAR(100)` usage

Fix

`SELECT *` makes index scans expensive

Test:

Column statistics are insufficient

Force index usage (testing only)

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Main issue: CHAR(100) usage

Fix

SELECT * makes index scans expensive

Test:

Column statistics are insufficient

Force index usage (testing only)

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Main issue: `CHAR(100)` usage

`SELECT *` makes index scans expensive