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 1qUlsD-00EWjq-TX for pgsql-general@arkaria.postgresql.org; Sat, 12 Aug 2023 10:29:34 +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 1qUlsA-00GcH7-Sx for pgsql-general@arkaria.postgresql.org; Sat, 12 Aug 2023 10:29:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUkUu-00GAWL-Gq for pgsql-general@lists.postgresql.org; Sat, 12 Aug 2023 09:01:24 +0000 Received: from mail.fduerr.de ([161.97.154.227]) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qUkUr-0022HJ-QL for pgsql-general@postgresql.org; Sat, 12 Aug 2023 09:01:24 +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 9FDFC1200D9; Sat, 12 Aug 2023 11:01:19 +0200 (CEST) Content-Type: multipart/mixed; boundary="------------X4gcbRJLWPCEKWLVOSjIGEeh" Message-ID: Date: Sat, 12 Aug 2023 11:01:18 +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: Adrian Klaver Cc: pgsql-general References: <075af529-be65-dd6b-3e30-032400b1f3d7@fduerr.de> <1785955c-fcaf-6fe3-18ce-88e896b23ec7@aklaver.com> From: =?UTF-8?Q?D=c3=bcrr_Software?= Subject: Re: PgSQL 15.3: Execution plan not using index as expected In-Reply-To: <1785955c-fcaf-6fe3-18ce-88e896b23ec7@aklaver.com> 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. --------------X4gcbRJLWPCEKWLVOSjIGEeh Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Thanks Adrian, sorry for the misunderstanding. I ran ANALYZE, it didn't change a thing (as expected). Anyway, I pinned the problem down now: It's the use of CURRENT_USER (or SESSION_USER etc.) in the WHERE condition. If i replace it with 'postgres' (the result of CURRENT_USER) the planner works as expected.. The old 9.x - version of PgSQL didn't have that problem. Test case: -- our test table with index on user_id CREATE TABLE tt (  user_id VARCHAR(63) NOT NULL DEFAULT SESSION_USER ); CREATE INDEX tt_user_id_idx ON tt(user_id); -- fill with test data INSERT INTO tt(user_id) select 'U' || i from generate_series(1,100000) as i; INSERT INTO tt(user_id) select SESSION_USER from generate_series(1,100); -- query using CURRENT_USER as WHERE-condition - doesn't use index EXPLAIN ANALYZE SELECT * FROM tt WHERE user_id::character varying(63)=CURRENT_USER::character varying(63) LIMIT 1;                                                QUERY PLAN ---------------------------------------------------------------------------------------------------------  Limit  (cost=0.00..21.65 rows=1 width=6) (actual time=18.143..18.143 rows=1 loops=1)    ->  Seq Scan on tt  (cost=0.00..2446.00 rows=113 width=6) (actual time=18.141..18.141 rows=1 loops=1)          Filter: ((user_id)::text = ((CURRENT_USER)::character varying(63))::text)          Rows Removed by Filter: 100000  Planning Time: 0.154 ms  Execution Time: 18.163 ms (6 Zeilen) SELECT CURRENT_USER;  current_user --------------  postgres (1 Zeile) -- query using result of CURRENT_USER as WHERE-condition - uses index EXPLAIN ANALYZE SELECT * FROM tt WHERE user_id::character varying(63)='postgres'::character varying(63) LIMIT 1;                                                            QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------  Limit  (cost=0.42..0.47 rows=1 width=6) (actual time=0.018..0.019 rows=1 loops=1)    ->  Index Only Scan using tt_user_id_idx on tt (cost=0.42..6.39 rows=113 width=6) (actual time=0.017..0.018 rows=1 loops=1)          Index Cond: (user_id = 'postgres'::text)          Heap Fetches: 0  Planning Time: 0.081 ms  Execution Time: 0.026 ms (6 Zeilen) -- CURRENT_USER is not expensive.. EXPLAIN ANALYZE SELECT CURRENT_USER;                                      QUERY PLAN -------------------------------------------------------------------------------------  Result  (cost=0.00..0.01 rows=1 width=64) (actual time=0.005..0.006 rows=1 loops=1)  Planning Time: 0.031 ms  Execution Time: 0.025 ms (3 Zeilen) I hope that this should clarify the problem. Thanks and kind regards ====================================== Dürr Software Entw. info@fduerr.de Am 11.08.23 um 16:32 schrieb Adrian Klaver: > On 8/11/23 03:11, Dürr Software wrote: > > Please reply to list also > Ccing list >> Dear Adrian, >> >> thanks for the reply. Of course i ran ANALYZE on the 15.3 system, its >> in the second part of my post, but here again, FYI: > > That is EXPLAIN ANALYZE where it is an option to the command: > > https://www.postgresql.org/docs/current/sql-explain.html > > ANALYZE > >     Carry out the command and show actual run times and other > statistics. This parameter defaults to FALSE. > > > What I was talking about was the ANALYZE command: > > https://www.postgresql.org/docs/current/sql-analyze.html > > ANALYZE collects statistics about the contents of tables in the > database, and stores the results in the pg_statistic system catalog. > Subsequently, the query planner uses these statistics to help > determine the most efficient execution plans for queries. > >> >> test=# \d client_session >>                                                   Tabelle >> »client_session« >>     Spalte     |              Typ               | Sortierfolge | NULL >> erlaubt? |                   Vorgabewert >> ---------------+--------------------------------+--------------+---------------+-------------------------------------------------- >> >> id            | bigint                         |              | not >> null       | nextval('admin.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::character varying(63)=SESSION_USER::character varying(63) >> ORDER BY tstamp_start DESC LIMIT 1; >>                                                                     QUERY >> PLAN >> --------------------------------------------------------------------------------------------------------------------------------------------------- >> >> Limit  (cost=2852336.36..2852336.48 rows=1 width=23) (actual >> time=5994.540..6000.702 rows=1 loops=1) >>    ->  Gather Merge  (cost=2852336.36..2852697.59 rows=3096 width=23) >> (actual time=5946.422..5952.583 rows=1 loops=1) >>          Workers Planned: 2 >>          Workers Launched: 2 >>          ->  Sort  (cost=2851336.34..2851340.21 rows=1548 width=23) >> (actual time=5934.963..5934.964 rows=1 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..2851328.60 rows=1548 width=23) (actual >> time=3885.774..5934.915 rows=1 loops=3) >>                      Filter: ((user_id)::text = >> ((SESSION_USER)::character varying(63))::text) >>                      Rows Removed by Filter: 37163374 >> Planning Time: 0.167 ms >> JIT: >>    Functions: 13 >>    Options: Inlining true, Optimization true, Expressions true, >> Deforming true >>    Timing: Generation 0.940 ms, Inlining 119.027 ms, Optimization >> 79.333 ms, Emission 29.624 ms, Total 228.924 ms >> Execution Time: 6001.014 ms >> (18 Zeilen) >> >> Funny thing: if i create an index on tstamp_start alone, it is used >> just perfectly: >> >> Indexe: >>      "client_session_pkey" PRIMARY KEY, btree (id) >>      "client_session_tstamp_start" btree (tstamp_start) >>      "client_session_user_id_idx" btree (user_id, tstamp_start DESC) >> >> test=# explain analyze SELECT permit_id FROM admin.client_session >> WHERE user_id::character varying(63)=SESSION_USER::character >> varying(63) ORDER BY tstamp_start DESC LIMIT 1; >> >> QUERY PLAN >> --------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> >>   Limit  (cost=0.57..1787.85 rows=1 width=23) (actual >> time=0.721..0.723 rows=1 loops=1) >>     ->  Index Scan Backward using client_session_tstamp_start on >> client_session (cost=0.57..6639766.39 rows=3715 width=23) (actual >> time=0.719..0.719 rows=1 loops=1) >>           Filter: ((user_id)::text = ((SESSION_USER)::character >> varying(63))::text) >>   Planning Time: 0.227 ms >>   Execution Time: 0.761 ms >> (5 Zeilen) >> >> ====================================== >> Dürr Software Entw. >> info@fduerr.de >> >> Am 10.08.23 um 16:41 schrieb Adrian Klaver: >>> On 8/9/23 01:14, Dürr Software wrote: >>>> 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. >>> >>> Did you run ANALYZE on the 15.3 database after the migration? >>> >>> >> > --------------X4gcbRJLWPCEKWLVOSjIGEeh Content-Type: text/vcard; charset=UTF-8; name="info.vcf" Content-Disposition: attachment; filename="info.vcf" Content-Transfer-Encoding: base64 YmVnaW46dmNhcmQNCmZuO3F1b3RlZC1wcmludGFibGU6RnJhbnogRD1DMz1CQ3JyDQpuO3F1 b3RlZC1wcmludGFibGU6RD1DMz1CQ3JyO0ZyYW56DQplbWFpbDtpbnRlcm5ldDppbmZvQGZk dWVyci5kZQ0KdGVsO3dvcms6MDg4MDMtNDg5OTAxNg0KdGVsO2ZheDowODgwMy00ODk5MDE3 DQp0ZWw7aG9tZTowODgwMy00ODkzNzUNCnZlcnNpb246Mi4xDQplbmQ6dmNhcmQNCg0K --------------X4gcbRJLWPCEKWLVOSjIGEeh--