Re: Views don't seem to use indexes?

2021-10-28 Thread Tim Slechta 
Tom, David,

Thank you for the time and information.

I lost my system this morning, so I need to re-establish a system and do
some additional homework.

Thanks again.

-Tim

BTW:  here is the definition of the pworkspaceobject table.

tc=# \d+ pworkspaceobject

 Table "public.pworkspaceobject"
   Column|Type | Collation |
Nullable | Default | Storage  | Stats target | Description
-+-+---+--+-+--+--+-
 puid| character varying(15)   |   |
not null |   | extended |  |
 pobject_name| character varying(128)  |   |
not null |   | extended |  |
 pobject_desc| character varying(240)  |   |
   |   | extended |  |
 pobject_type| character varying(32)   |   |
not null |   | extended |  |
 pobject_application | character varying(32)   |   |
not null |   | extended |  |
 vla_764_7   | integer |   |
not null | 0 | plain|  |
 pip_classification  | character varying(128)  |   |
   |   | extended |  |
 vla_764_10  | integer |   |
not null | 0 | plain|  |
 pgov_classification | character varying(128)  |   |
   |   | extended |  |
 vla_764_12  | integer |   |
not null | 0 | plain|  |
 pfnd0revisionid | character varying(32)   |   |
   |   | extended |  |
 vla_764_18  | integer |   |
not null | 0 | plain|  |
 vla_764_20  | integer |   |
not null | 0 | plain|  |
 rwso_threadu| character varying(15)   |   |
   |   | extended |  |
 rwso_threadc| integer |   |
   |   | plain|  |
 prevision_limit | integer |   |
not null |   | plain|  |
 prevision_number| integer |   |
not null |   | plain|  |
 rowning_organizationu   | character varying(15)   |   |
   |   | extended |  |
 rowning_organizationc   | integer |   |
   |   | plain|  |
 pactive_seq | integer |   |
   |   | plain|  |
 rowning_projectu| character varying(15)   |   |
   |   | extended |  |
 rowning_projectc| integer |   |
   |   | plain|  |
 pfnd0maturity   | integer |   |
   |   | plain|  |
 pdate_released  | timestamp without time zone |   |
   |   | plain|  |
 pfnd0isrevisiondiscontinued | smallint|   |
   |   | plain|  |
 pfnd0inprocess  | smallint|   |
   |   | plain|  |
 aoid| character varying(15)   |   |
not null | NULL::character varying | extended |  |
 arev_category   | integer |   |
not null | 48 | plain|  |
 aspace_uid  | character varying(15)   |   |
   | NULL::character varying | extended |  |
 avalid_from | timestamp without time zone |   |
not null | to_timestamp('1900/01/02 00:00:00'::text, '/MM/DD
HH24:MI:SS'::text)::timestamp without time zone | plain|  |
 avalid_to   | timestamp without time zone |   |
   |   | plain|  |
 vla_764_26  | integer |   |
not null | 0 | plain|  |
 pawp0issuspect  | smallint|   |
   |   | plain|  |
 vla_764_24  | integer |   |
not null | 0 | plain|  |
 vla_764_23  | integer |   |
not null | 0 | plain|  |
Indexes:
"pipworkspaceobject" PRIMARY KEY, btree (puid)
"pipworkspaceobject_0" btree (aoid)
"pipworkspaceobject_1" btree (upper(pobject_type::text))
"pipworkspaceobject_2" btree (upper(pobject_name::text))
"pipworkspaceobject_3" btree (pobject_type)
"pipworkspaceobject_4" btree (pobject_name)

Re: Lock contention high

2021-10-28 Thread Ashkil Dighin 
Hi,
Yes, lock contention reduced with postgresqlv14.
Lock acquire reduced 18% to 10%
10.49 %postgres  postgres[.] LWLockAcquire
5.09%  postgres  postgres[.] _bt_compare

Is lock contention can be reduced to 0-3%?
On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert”


On Tuesday, October 26, 2021, Andres Freund  wrote:

> Hi,
>
> On 2021-10-12 13:05:12 +0530, Ashkil Dighin wrote:
> > PostgreSQL version: 13.3
>
> You could try postgres 14 - that did improve scalability in some areas.
>
>
>
> > Perf data for 24vu(TPC-C)
> > 
> >
> >   18.99%  postgres  postgres[.] LWLockAcquire
> >  7.09%  postgres  postgres[.] _bt_compare
> >  8.66%  postgres  postgres[.] LWLockRelease
> >  2.28%  postgres  postgres[.] GetSnapshotData
> >  2.25%  postgres  postgres[.] hash_search_with_hash_value
> >  2.11%  postgres  postgres[.] XLogInsertRecord
> >  1.98%  postgres  postgres[.] PinBuffer
>
> To be more useful you'd need to create a profile with 'caller' information
> using 'perf record --call-graph dwarf', and then check what the important
> callers are.
>
>
> > Postgres.conf used  in Baremetal
> > 
> > shared_buffers = 128GB(1/4 th RAM size)
> > effective_cachesize=392 GB(1/3 or 75% of RAM size)
>
> If your hot data set is actually larger than s_b, I'd recommend trying a
> larger s_b. It's plausible that a good chunk of lock contention is from
> that.
>
> Greetings,
>
> Andres Freund
>