Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qU6dX-00BfrD-VG for pgsql-general@arkaria.postgresql.org; Thu, 10 Aug 2023 14:27:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qU6dW-00FSTi-G6 for pgsql-general@arkaria.postgresql.org; Thu, 10 Aug 2023 14:27:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTeL0-003QFE-0d for pgsql-general@lists.postgresql.org; Wed, 09 Aug 2023 08:14:38 +0000 Received: from mail.fduerr.de ([161.97.154.227]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qTeKw-001M8T-Pu for pgsql-general@postgresql.org; Wed, 09 Aug 2023 08:14:37 +0000 Received: from [192.168.101.175] (ipbcc02add.dynamic.kabel-deutschland.de [188.192.42.221]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature ECDSA (prime256v1) server-digest SHA256) (No client certificate requested) by mail.fduerr.de (Postfix) with ESMTPSA id C672312009A for ; Wed, 9 Aug 2023 10:14:30 +0200 (CEST) Content-Type: multipart/mixed; boundary="------------Ykt7hqe0Ha5jvHayO0WsSMpq" Message-ID: Date: Wed, 9 Aug 2023 10:14:30 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.13.0 Content-Language: en-US, de-DE To: pgsql-general@postgresql.org From: =?UTF-8?Q?D=c3=bcrr_Software?= Subject: PgSQL 15.3: Execution plan not using index as expected List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Ykt7hqe0Ha5jvHayO0WsSMpq Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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@fduerr.de --------------Ykt7hqe0Ha5jvHayO0WsSMpq Content-Type: text/vcard; charset=UTF-8; name="info.vcf" Content-Disposition: attachment; filename="info.vcf" Content-Transfer-Encoding: base64 YmVnaW46dmNhcmQNCmZuO3F1b3RlZC1wcmludGFibGU6RnJhbnogRD1DMz1CQ3JyDQpuO3F1 b3RlZC1wcmludGFibGU6RD1DMz1CQ3JyO0ZyYW56DQplbWFpbDtpbnRlcm5ldDppbmZvQGZk dWVyci5kZQ0KdGVsO3dvcms6MDg4MDMtNDg5OTAxNg0KdGVsO2ZheDowODgwMy00ODk5MDE3 DQp0ZWw7aG9tZTowODgwMy00ODkzNzUNCnZlcnNpb246Mi4xDQplbmQ6dmNhcmQNCg0K --------------Ykt7hqe0Ha5jvHayO0WsSMpq--