Horton
Menu

Thread

  1. How to use index in simple select

    Andrus <kobruleht2@hot.ee> — 2025-11-28T22:57:46Z

    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

  2. Re: How to use index in simple select

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-11-29T17:27:43Z

    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

  3. Re: How to use index in simple select

    Andrus <kobruleht2@hot.ee> — 2025-11-29T17:47:23Z

    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.

  4. Re: How to use index in simple select

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-11-29T17:56:31Z

    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

  5. Re: How to use index in simple select

    hubert depesz lubaczewski <depesz@depesz.com> — 2025-12-01T10:10:14Z

    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