Dear list,
i have a strange problem when migrating a DB from version 9.3.4 to 15.3:
An index which seems perfect for the query and is used in 9.3.4 as
expected is not used in 15.3.
I just wonder, whether the bug is on my side or on PgSQL's..
The details:
===========================================================================
PostgreSQL 9.3.4:
test=# select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.8.3-2) 4.8.3, 64-bit
(1 row)
test=> \d client_session
Table "client_session"
Column | Type | Modifiers
---------------+--------------------------------+-------------------------------------------------------------------
id | bigint | not null default
nextval('client_session_id_seq'::regclass)
tstamp_start | timestamp(3) without time zone | not null default now()
permit_id | character varying(63) | not null default
"current_user"()
user_id | character varying(63) | not null default
"session_user"()
Indexes:
"client_session_pkey" PRIMARY KEY, btree (id)
"client_session_user_id_idx" btree (user_id, tstamp_start DESC)
vdws=# explain analyze SELECT permit_id FROM client_session WHERE
user_id=SESSION_USER::VARCHAR ORDER BY tstamp_start DESC LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..2.37 rows=1 width=23) (actual time=0.134..0.134
rows=1 loops=1)
-> Index Scan using client_session_user_id_idx on client_session
(cost=0.57..52337.99 rows=29181 width=23) (actual time=0.133..0.133
rows=1 loops=1)
Index Cond: ((user_id)::text = (("session_user"())::character
varying)::text)
Total runtime: 0.165 ms
(4 rows)
===========================================================================
PostgreSQL 15.3:
test=# select version();
version
---------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 Zeile)
test=# \d client_session
Tabelle »client_session«
Spalte | Typ | Sortierfolge | NULL
erlaubt? | Vorgabewert
---------------+--------------------------------+--------------+---------------+--------------------------------------------------
id | bigint | | not
null | nextval('client_session_id_seq'::regclass)
tstamp_start | timestamp(3) without time zone | | not
null | now()
permit_id | character varying(63) | | not
null | "current_user"()
user_id | character varying(63) | | not
null | "session_user"()
Indexe:
"client_session_pkey" PRIMARY KEY, btree (id)
"client_session_user_id_idx" btree (user_id, tstamp_start DESC)
test=# explain analyze SELECT permit_id FROM client_session WHERE
user_id=SESSION_USER::VARCHAR ORDER BY tstamp_start DESC LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3778568.38..3778568.50 rows=1 width=152) (actual
time=8431.320..8437.169 rows=1 loops=1)
-> Gather Merge (cost=3778568.38..3853392.64 rows=641306
width=152) (actual time=8383.774..8389.622 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=3777568.36..3778369.99 rows=320653 width=152)
(actual time=8372.263..8372.263 rows=0 loops=3)
Sort Key: tstamp_start DESC
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on client_session
(cost=0.00..3775965.09 rows=320653 width=152) (actual
time=6150.412..8372.191 rows=1 loops=3)
Filter: ((user_id)::text =
((SESSION_USER)::character varying)::text)
Rows Removed by Filter: 51303778
Planning Time: 0.203 ms
JIT:
Functions: 13
Options: Inlining true, Optimization true, Expressions true,
Deforming true
Timing: Generation 1.644 ms, Inlining 120.073 ms, Optimization
70.361 ms, Emission 28.476 ms, Total 220.554 ms
Execution Time: 8438.307 ms
(18 rows)
Thanks a lot for your help
--
======================================
Dürr Software Entw.
Guggenberg 26, DE-82380 Peißenberg
fon: +49-8803-4899016 fax: +49-8803-4899017
info@xxxxxxxxx
begin:vcard
fn;quoted-printable:Franz D=C3=BCrr
n;quoted-printable:D=C3=BCrr;Franz
email;internet:info@xxxxxxxxx
tel;work:08803-4899016
tel;fax:08803-4899017
tel;home:08803-489375
version:2.1
end:vcard
