Date: Mon, 1 Dec 2025 11:10:14 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Andrus <kobruleht2@hot.ee>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: How to use index in simple select
Message-ID: <aS1phiFqCNcmroTg@depesz.com>
Reply-To: depesz@depesz.com
References: <bf6ab185-fb9d-4504-9ddc-61af2e4bc028@hot.ee>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <bf6ab185-fb9d-4504-9ddc-61af2e4bc028@hot.ee>
Archived-At: <https://www.postgresql.org/message-id/aS1phiFqCNcmroTg%40depesz.com>
Precedence: bulk

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