pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to use index in simple select
5+ messages / 3 participants
[nested] [flat]
* How to use index in simple select
@ 2025-11-28 22:57 Andrus <kobruleht2@hot.ee>
0 siblings, 2 replies; 5+ messages in thread
From: Andrus @ 2025-11-28 22:57 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to use index in simple select
@ 2025-11-29 17:27 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Andrus <kobruleht2@hot.ee>
1 sibling, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-11-29 17:27 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; pgsql-general
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to use index in simple select
@ 2025-11-29 17:47 Andrus <kobruleht2@hot.ee>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 5+ messages in thread
From: Andrus @ 2025-11-29 17:47 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to use index in simple select
@ 2025-11-29 17:56 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Andrus <kobruleht2@hot.ee>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2025-11-29 17:56 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; pgsql-general
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to use index in simple select
@ 2025-12-01 10:10 hubert depesz lubaczewski <depesz@depesz.com>
parent: Andrus <kobruleht2@hot.ee>
1 sibling, 0 replies; 5+ messages in thread
From: hubert depesz lubaczewski @ 2025-12-01 10:10 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-12-01 10:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-28 22:57 How to use index in simple select Andrus <kobruleht2@hot.ee>
2025-11-29 17:27 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-11-29 17:47 ` Andrus <kobruleht2@hot.ee>
2025-11-29 17:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-12-01 10:10 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox