Обсуждение: How to use index in simple select

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

On 11/28/25 14:57, Andrus wrote:
> 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
> 
> 

Seems to be already answered:

"It looks like reindex table firma2.klient restored index scan. create 
index concurrently was used when lot of transactions using this table 
where running"



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

Seems to be already answered:

"It looks like reindex table firma2.klient restored index scan. create index concurrently was used when lot of transactions using this table where running" 

Is it possible that reindex changes query from sequential scan to use index ?

Andrus.

On 11/29/25 09:47, Andrus wrote:
> Hi!
> 
>> Seems to be already answered:
>>
>> "It looks like reindex table firma2.klient restored index scan. create 
>> index concurrently was used when lot of transactions using this table 
>> where running"
>>
> Is it possible that reindex changes query from sequential scan to use 
> index ?

More likely it was one of the build index concurrently caveats detailed 
here:

https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

> 
> Andrus.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, Nov 29, 2025 at 12:57:46AM +0200, Andrus wrote:
> 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;

You got your help, hopefully, but please, please, please, for the love
of anything that you care about:

1. read, and apply: https://wiki.postgresql.org/wiki/Don't_Do_This ->
   specifically the part about char(n) datatype
   since wiki seems to be having problems for some time now, here is
   archived version:
   https://web.archive.org/web/20251002222437/https://wiki.postgresql.org/wiki/Don't_Do_This

2. Why did you specify so many things in your index? Generally you
   should use CONCURRENTLY (which you didn't), but you don't need
   tablespace definition, nor collate, nor ordering, nor nulls last.
   Unless you know, for a fact, with proof, that you know what you're
   doing and it makes sense.
   CREATE index concurrently klient_nimi_idx on firma2.klient (nimi);
   should be enough.

Best regards,

depesz