pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dürr Software <info@fduerr.de>
To: pgsql-general@postgresql.org
Subject: PgSQL 15.3: Execution plan not using index as expected
Date: Wed, 9 Aug 2023 10:14:30 +0200
Message-ID: <e7534f4d-489f-e29f-9932-834853445814@fduerr.de> (raw)

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

Attachments:

info.vcf (text/vcard, 213B) download begin:vcard fn;quoted-printable:Franz D=C3=BCrr n;quoted-printable:D=C3=BCrr;Franz email;internet:info@fduerr.de tel;work:08803-4899016 tel;fax:08803-4899017 tel;home:08803-489375 version:2.1 end:vcard

view thread (6+ messages)  latest in thread

Message-ID: <e7534f4d-489f-e29f-9932-834853445814@fduerr.de>
Permalink:  ../e7534f4d-489f-e29f-9932-834853445814@fduerr.de/
Also on:    postgresql.org/message-id/e7534f4d-489f-e29f-9932-834853445814@fduerr.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: info@fduerr.de
  Subject: Re: PgSQL 15.3: Execution plan not using index as expected
  In-Reply-To: <e7534f4d-489f-e29f-9932-834853445814@fduerr.de>
  References: <e7534f4d-489f-e29f-9932-834853445814@fduerr.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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