sql statement not using all primary key values and poor performance

Поиск
Список
Период
Сортировка
От James Pang
Тема sql statement not using all primary key values and poor performance
Дата
Msg-id CAHgTRfe6e0KUOXBchzXhA3FG-Pn__OY6g301b_W+Zt3a6Xvhbg@mail.gmail.com
обсуждение исходный текст
Ответы Re: sql statement not using all primary key values and poor performance
Re: sql statement not using all primary key values and poor performance
Список pgsql-performance
Hi experts,
   we have a SQL from Postgresql JDBC,  primary is based on (bigint,varchar2,bigint),  but from sql plan, it convert to ::numeric so 
the plan just use one "varchar" key column and use the other 2 bigint keys as filters. what's the cause about that ? 

                            Table "test.xxxxxx"
      Column      |              Type              | Collation | Nullable | Default
------------------+--------------------------------+-----------+----------+---------
 xxxid           | bigint                         |           | not null |
 paramname        | character varying(512)         |           | not null |
 paramvalue       | character varying(1536)        |           |          |
 sssid           | bigint                         |           | not null |
 createtime       | timestamp(0) without time zone |           |          |
 lastmodifiedtime | timestamp(0) without time zone |           |          |
 mmmuuid          | character varying(32)          |           |          |
Indexes:
      "pk_xxxxxx" PRIMARY KEY, btree (xxxid, paramname, sssid)
      "idx_xxxxxx_mmmuuid" btree (sssid, mmmuuid, paramname)
 


SET extra_float_digits = 3


duration: 7086.014 ms  plan:
        Query Text: SELECT XXXFID, PARAMNAME, PARAMVALUE, SSSID, CREATETIME, LASTMODIFIEDTIME, MMMUUID FROM test.XXXXXX WHERE  ( ( XXXID = $1  ) )  AND  ( ( PARAMNAME = $2  ) )  AND  ( ( SSSID = $3  ) )
        Index Scan using pk_xxxxxx on test.xxxxxx  (cost=0.57..2065259.09 rows=1 width=86) (actual time=7086.010..7086.011 rows=0 loops=1)
          Output: confid, paramname, paramvalue, sssid, createtime, lastmodifiedtime, mmmuuid
          Index Cond: ((xxxxxx.paramname)::text = 'cdkkifffff'::text)   <<< just use only one key instead all primary keys.
          Filter: (((xxxxxx.xxxid)::numeric = '18174044'::numeric) AND ((xxxxxx.sssid)::numeric = '253352'::numeric))    <<< it's bigint but converted to numeric 
          Buffers: shared read=1063470
          I/O Timings: read=4402.029

it's from JDBC, we saw this JDBC driver try to set extra_float_digits = 3 before running the SQL ,does that make planner to convert bigint to numeric ? 

Thanks,

James

В списке pgsql-performance по дате отправления: