table_sql_details_masked.txt
text/plain
Filename: table_sql_details_masked.txt
Type: text/plain
Part: 0
Table "testapp.testablename"
Column | Type | Collation | Nullable | Default
------------------+--------------------------------+-----------+----------+-------------------
lmapuuid | character varying(64) | | not null |
sssid | bigint | | not null |
evctid | bigint | | not null |
lmapparameter | character varying(4000) | | not null |
createtime | timestamp(0) without time zone | | not null | clock_timestamp()
lmdtime | timestamp(0) without time zone | | not null | clock_timestamp()
Indexes:
"pk_testablename" PRIMARY KEY, btree (lmapuuid, sssid)
"idx_testablename_evctid_1" btree (sssid, evctid, lmapparameter, lmapuuid)
Publications:
"testapp_10001"
Triggers:
tr_testablename BEFORE DELETE ON testablename FOR EACH ROW WHEN (USER <> 'batcha'::name OR USER = 'batchb'::name) EXECUTE FUNCTION trigger_fct_tr_testablename()
tr_testablename_lmt BEFORE UPDATE ON testablename FOR EACH ROW WHEN (USER !~~ 'batch%'::text) EXECUTE FUNCTION trigger_fct_common_lmt()
table and index size
select relname,reltuples,relpages,relpages*8/1024 "size(mb)" from pg_class where relname='xxx';
relname | reltuples | relpages size(mb)
----------------------+---------------+----------
testablename | 8.2382416e+07 | 1761908 13764
pk_testablename | 8.2382416e+07 | 729228 5697
idx_testablename_evctid_1 | 8.2382416e+07 | 1742734 13615
show shared_buffers;
shared_buffers
----------------
65369MB
(1 row)
sssid, evctid are bigint , lmapuuid is UUID and stored in database are varchar(64).
SELECT sssid,lmapuuid AS UUID ,evctid,lmapparameter AS lllparm FROM testablename
WHERE evctid = $1 AND sssid = $2
AND lmapparameter LIKE '%x.abc%' AND ((lmapparameter LIKE '%ED=%' AND lmapparameter LIKE '%AID=%') OR (lmapparameter LIKE '%J=%' AND lmapparameter LIKE '%RT=%') OR ((LINKPARAMETER LIKE '%ED=%' OR (LINKPARAMETER LIKE '%J=%' )) AND lmapparameter LIKE '%UJ=1%'))
AND lmapparameter NOT LIKE '%disabled%' AND lmapparameter NOT LIKE '%IEH=%'
FETCH FIRST $3 ROWS ONLY
Limit (cost=0.82..8.63 rows=31 width=116) (actual time=0.023..14.371 rows=7 loops=1)
Output: sssid, lmapuuid, evctid, lmapparameter
Buffers: shared hit=2148
-> Index Only Scan using idx_testablename_evctid_1 on testablename (cost=0.82..30.81 rows=119 width=116) (actual time=0.022..14.368 row
s=7 loops=1)
Output: sssid, lmapuuid, evctid, lmapparameter
Index Cond: ((testablename.sssid = 'xxxxxxx'::bigint) AND (testablename.evctid = 'xxxxxxx'::bigint))
Filter: (((testablename.lmapparameter)::text ~~ '%x.abc%'::text) AND ((testablename.lmapparameter)::text !~~ '%disabled%'::text) AND
((testablename.lmapparameter)::text !~~ '%IEH=%'::text) AND ((((testablename.lmapparameter)::text ~~ '%ED=%'::text) AND ((mtgconferencelinkm
ap.lmapparameter)::text ~~ '%AID=%'::text)) OR (((testablename.lmapparameter)::text ~~ '%J=%'::text) AND ((testablename.lmapparameter)::text
~~ '%RT=%'::text)) OR ((((testablename.lmapparameter)::text ~~ '%ED=%'::text) OR ((testablename.lmapparameter)::text ~~ '%J=%'::text)) AND ((
testablename.lmapparameter)::text ~~ '%UJ=1%'::text))))
Rows Removed by Filter: 36155
Heap Fetches: 260
Buffers: shared hit=2148
Query Identifier: -4801201989067066267
Planning Time: 0.318 ms
Execution Time: 14.391 ms
above are test from psql manual running with prepared statement, running many times, it show shared_hit=2148 , index only scan but with heap fetches.
below are pg_stat_statements snaptshot query about this SQL,
some sessions insert into this table in parallel, and at the same time, many sessions run this select statement, the shared_blks_hit/calls show a huge number
thant tens of times of manually running test 2148.
pg_stat_statements query
avgsblks=shared_blks_hit/calls, avgdirtied=shared_blks_dirtied/call, avgread=shared_blks_read/calls, avgfpi=fpi/calls, avgwal=walbytes/calls
sample_time | calls | min_exec_time | mean_exec_time | max_exec_time | avgsblks | avgdirtied | avgread | avgfpi | avgwal
| avgrtime | avgwtime
------------------------+-------+----------------------+--------------------+--------------------+----------+------------+---------+--------+----------------
--------+--------------------+----------
2024-12-20 02:45:20+00 | 1236 | 0.022557 | 0.50104122815534 | 1.377047 | 810 | 0 | 0 | 0 | 0.3462783171521
0355987 | 0 | 0
2024-12-20 02:55:31+00 | 11435 | 0.017463 | 4.32600441696545 | 200.30973999999998 | 5272 | 0 | 0 | 0 | 0.0696108439003
0607783 | 0 | 0
2024-12-20 03:15:59+00 | 16035 | 0.020143 | 115.54667212641019 | 15157.307508 | 20735 | 2 | 1 | 0 | 2269.353102588
0885563 | 0.8249557188649828 | 0
2024-12-20 03:25:10+00 | 147 | 0.018453999999999998 | 144.3084889591837 | 198.37187500000002 | 206505 | 0 | 0 | 0 | 1.537414965
9863946 | 0 | 0
2024-12-20 05:55:38+00 | 56 | 0.028886000000000002 | 201.53043271428575 | 838.709004 | 196878 | 0 | 0 | 80 | 329936.73214
2857143 | 0 | 0
2024-12-20 06:35:28+00 | 7344 | 0.025979 | 694.054243656318 | 10391.441267 | 36162 | 0 | 0 | 0 | 117.151279956
4270153 | 0 | 0
2024-12-20 07:05:59+00 | 124 | 0.056067 | 161.0531775241936 | 167.077285 | 235815 | 0 | 0 | 0 | 1.032258064
in addtion to this table and query, the workload doing inserting to another two tables in many sessions, and running query select with the index in many sessions too.
the other two tables have similar primary and indexes that having bigint and uuid columns, the query use the index that have bigint, uuid, varchar ,3 column composite index.
table size 3790MB, index size 1445MB.