Re: Postgres views cannot use both union and join/where

[Tom Lane]

"David G. Johnston"  writes:
> On Tuesday, October 19, 2021, Michael Lewis  wrote:
>> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran <
>> mithranakulaseka...@gmail.com> wrote:
>>> create  view template_view (id, name, description, is_staged) as
>>> select t.id,t.name, t.description, false as is_staged
>>> from template t
>>> left join template_staging ts on t.name = ts.name and ts.name is null

>> Does that work? I've only seen that type of logic written as-
>> left join template_staging ts on t.name = ts.name
>> where ts.name is null

> The are functionally equivalent, though the timing of the expression
> evaluation differs slightly.

No, not at all.  Michael's version correctly implements an anti-join,
where the first version does not.  The reason is that the WHERE clause
"sees" the column value post-JOIN, whereas the JOIN/ON clause "sees"
values pre-JOIN.

Assuming that the '=' operator is strict, the first query's ON clause
really reduces to constant false, so that you just get a null-extended
image of the left table.  That's almost surely not what's wanted.

regards, tom lane

Re: Postgres views cannot use both union and join/where

[David G. Johnston]

On Wed, Oct 20, 2021 at 6:58 AM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Tuesday, October 19, 2021, Michael Lewis  wrote:
> >> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran <
> >> mithranakulaseka...@gmail.com> wrote:
> >>> create  view template_view (id, name, description, is_staged) as
> >>> select t.id,t.name, t.description, false as is_staged
> >>> from template t
> >>> left join template_staging ts on t.name = ts.name and ts.name is null
>
> >> Does that work? I've only seen that type of logic written as-
> >> left join template_staging ts on t.name = ts.name
> >> where ts.name is null
>
> > The are functionally equivalent, though the timing of the expression
> > evaluation differs slightly.
>
> No, not at all.  Michael's version correctly implements an anti-join,
> where the first version does not.  The reason is that the WHERE clause
> "sees" the column value post-JOIN, whereas the JOIN/ON clause "sees"
> values pre-JOIN.
>

Yeah, my bad.  I was actually thinking this but then figured the OP
wouldn't have written an anti-join that didn't actually work.

My original email was going to be:

Adding the single table expression to the ON clause is shorthand for
writing:

SELECT t.* FROM template AS t LEFT JOIN (SELECT * FROM template_staging
WHERE template_staging.name IS NULL) AS ts ON t.name = ts.name;

David J.

Re: Lock contention high

[Ashkil Dighin]

Hi
B-tree index used in the postgres environment
Checked on warehouse different values like 100,800,1600,2400 and 3200 with
virtual user 64
On different values(warehouse) the lock contention same i.e. approx 17% and
iostat usage is 30-40%



pg_Count_ware=100
-
17.76%  postgres  postgres[.] LWLockAcquire
4.88%  postgres  postgres[.] _bt_compare
3.10%  postgres  postgres[.] LWLockRelease




pg_Count_ware=800(previously I used Warehouse 800)

17.91%  postgres  postgres[.] LWLockAcquire
5.76%  postgres  postgres[.] _bt_compare
3.06%  postgres  postgres[.] LWLockRelease





pg_Count_ware_1600
-
17.80%  postgres  postgres[.] LWLockAcquire
5.88%  postgres  postgres[.] _bt_compare
2.70%  postgres  postgres[.] LWLockRelease




pg_Count_ware_2400
--
17.77%  postgres  postgres[.] LWLockAcquire
6.01%  postgres  postgres[.] _bt_compare
2.71%  postgres  postgres[.] LWLockRelease




pg_Count_ware_3200
--
17.46%  postgres  postgres[.] LWLockAcquire
6.32%  postgres  postgres[.] _bt_compare
2.86%  postgres  postgres[.] hash_search_with_hash_value



1.Tired different values of lock management values in postgres.conf but it
not helped to reduce lock contention.
deadlock_timeout = 5s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64
max_pred_locks_per_relation = -2

max_pred_locks_per_page = 2
2.Intention to check the postgreSQL scalability and performance or
throughput(TPC-C/TPC-H)
 with HammerDB and pgbench with  server configuration on tune
settings(postgresql.conf)-reduce lock contention
CPU's :256
Threadper core:  2
Core per socket:  64
Sockets:   2
NUMA node0 :   0-63,128-191
NUMA node1 :   64-127,192-255
RAM size :512GB
SSD :1TB

Ref link:
https://www.hammerdb.com/blog/uncategorized/hammerdb-best-practice-for-postgresql-performance-and-scalability/

On Thursday, October 14, 2021, Peter Geoghegan  wrote:

> On Tue, Oct 12, 2021 at 12:45 AM Ashkil Dighin 
> wrote:
> > Lock contention observed high in PostgreSQLv13.3
> > The source code compiled with GNC(GCCv11.x)
> > PostgreSQL version: 13.3
> > Operating system:   RHEL8.3
> > Kernel name:4.18.0-305.10.2.el8_4.x86_64
> > RAM Size:512GB
> > SSD: 1TB
> > The environment used IBM metal and test benchmark environment
> HammerDbv4.2
> > Test case :TPC-C
>
> You didn't say how many TPC-C warehouses you used. In my experience,
> people sometimes run TPC-C with relatively few, which will tend to
> result in extreme contention on certain B-Tree leaf pages. (My
> experiences are with BenchmarkSQL, but I can't imagine HammerDB is too
> much different.)
>
> Assuming that's the case here, for you, then it's not clear that you
> have a real problem. You're really not supposed to run the benchmark
> in that way, per the TPC-C spec, which strictly limits the number of
> transactions per minute per warehouse -- for better or worse, valid
> results generally require that you use lots of warehouses to get a
> very large database (think terabytes). If you run the benchmark with
> 100 warehouses or less, on a big server, then the contention you'll
> see will be out of all proportion to what you're ever likely to see in
> the real world.
>
> --
> Peter Geoghegan
>