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

[David G. Johnston]

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.

It could also be written as an anti-join:

Select * from template as t where not exists (select 1 from
template_staging as ts where t.name = ts.name)

David J.

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

[Michael Lewis]

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

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

[Benedict Holland]

I thought a union mashed together two queries. The where clause can appear
in both. But the execution plan will almost certainly run the first query
and the second query. It should throw an error if the types don't match or
the number of columns don't match.

There are so few use cases for unions that can't get fixed with better
schema designs. I ran into a few over the years.

On Tue, Oct 19, 2021, 9:32 PM David G. Johnston 
wrote:

> On Tue, Oct 19, 2021 at 2:48 PM Mithran Kulasekaran <
> mithranakulaseka...@gmail.com> wrote:
>
>> i think the only problem is when we try to use both union and where/join
>> the issue starts to happen
>>
>
> I'm unconvinced this is actually an issue based upon what is presented
> here.  All I'm seeing is two decidedly different queries resulting in
> different query plans.  That the "problem one" isn't using an index isn't
> surprising given the volume of data involved and the change from specifying
> a literal value in the where clause to letting a join determine which
> results to return.
>
> Assuming you have a real scenario you are testing with being able to
> demonstrate (probably through the use of the query planner GUCs) that
> PostgreSQL can produce a better plan but doesn't by default would be a more
> compelling case.  More generally, you probably need to either use your real
> scenario's data to help demonstrate the issue or create a self-contained
> test that is at least closer to what it produces (this approach still
> benefits from seeing what is happening for real).
>
> David J.
>
>
>

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

[David G. Johnston]

On Tue, Oct 19, 2021 at 2:48 PM Mithran Kulasekaran <
mithranakulaseka...@gmail.com> wrote:

> i think the only problem is when we try to use both union and where/join
> the issue starts to happen
>

I'm unconvinced this is actually an issue based upon what is presented
here.  All I'm seeing is two decidedly different queries resulting in
different query plans.  That the "problem one" isn't using an index isn't
surprising given the volume of data involved and the change from specifying
a literal value in the where clause to letting a join determine which
results to return.

Assuming you have a real scenario you are testing with being able to
demonstrate (probably through the use of the query planner GUCs) that
PostgreSQL can produce a better plan but doesn't by default would be a more
compelling case.  More generally, you probably need to either use your real
scenario's data to help demonstrate the issue or create a self-contained
test that is at least closer to what it produces (this approach still
benefits from seeing what is happening for real).

David J.

Postgres views cannot use both union and join/where

[Mithran Kulasekaran]

Hi,

We are trying to use the postgres view to accommodate some of the complex
workflow related operations, we perform we saw like using union in a where
clause inside a view actually pushed the where clause to both subqueries
and we get good performance using the index , but when used in a join it
does a full scan and filter of the table instead of pushing the filter
column as a where clause. we also found that when used without any
join/where in the union clause (*i.e.,* *select ... from template union all
select ... from template_staging)* works with joins just fine , i think the
only problem is when we try to use both union and where/join the issue
starts to happen is there any specific flag or release planned to address
this issue.

Postgres version: PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit


*SQL Steps:*

create table template
(
id  int primary key,
namevarchar(30) unique,
description varchar(30)
);

create table template_staging
(
id  int primary key,
namevarchar(30) unique,
description varchar(30),
is_deleted  bool
);

insert into template (id, name, description)
values (1, 'test1', 'hello'),
   (2, 'test2', 'hello world 2'),
   (3, 'test3', 'hello world 3');
insert into template_staging (id, name, description, is_deleted)
values (3, 'test3', 'revert hello world 3', false),
   (4, 'test4', 'hello world 2', false),
   (5, 'test5', 'hello world 3', false);

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
UNION ALL
select t.id, t.name, t.description, true as is_stage
from template_staging t
where is_deleted is false;

create table tester(
 id  int primary key,
  template_id int
);
insert into tester (id, template_id)
values (1, 1),
   (2, 2),
   (3, 3),(4, 4);


*Analysis:*

*EXPLAIN ANALYZE select * from template_view where id=1;*

Append  (cost=0.15..16.36 rows=2 width=161) (actual time=0.012..0.015
rows=1 loops=1)
  ->  Index Scan using template_pkey on template t  (cost=0.15..8.17
rows=1 width=161) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (id = 1)
  ->  Index Scan using template_staging_pkey on template_staging t_1
(cost=0.15..8.17 rows=1 width=161) (actual time=0.002..0.002 rows=0
loops=1)
Index Cond: (id = 1)
Filter: (is_deleted IS FALSE)


*EXPLAIN ANALYZE select * from template_view where name='test1';*

Append  (cost=0.15..16.36 rows=2 width=157) (actual time=0.012..0.015
rows=1 loops=1)
  ->  Index Scan using template_name_key on template t  (cost=0.15..8.17
rows=1 width=157) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: ((name)::text = 'test1'::text)
  ->  Index Scan using template_staging_name_key on template_staging t_1
 (cost=0.15..8.17 rows=1 width=157) (actual time=0.002..0.002 rows=0
loops=1)
Index Cond: ((name)::text = 'test1'::text)
Filter: (is_deleted IS FALSE)



*EXPLAIN ANALYZE select * from tester t inner join template_view tv on
tv.id  = t.template_idwhere t.id =1;*

Hash Join  (cost=8.18..48.19 rows=3 width=169) (actual
time=0.024..0.032 rows=1 loops=1)
  Hash Cond: (t_1.id = t.template_id)
  ->  Append  (cost=0.00..38.27 rows=645 width=161) (actual
time=0.008..0.014 rows=6 loops=1)
->  Seq Scan on template t_1  (cost=0.00..14.30 rows=430
width=161) (actual time=0.008..0.009 rows=3 loops=1)
->  Seq Scan on template_staging t_2  (cost=0.00..14.30
rows=215 width=161) (actual time=0.003..0.004 rows=3 loops=1)
  Filter: (is_deleted IS FALSE)
  ->  Hash  (cost=8.17..8.17 rows=1 width=8) (actual time=0.011..0.011
rows=1 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 9kB
->  Index Scan using tester_pkey on tester t  (cost=0.15..8.17
rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)
  Index Cond: (id = 1)


*EXPLAIN (ANALYZE, BUFFERS) select * from template_view where id=1;*

Append  (cost=0.15..16.36 rows=2 width=161) (actual time=0.011..0.015
rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using template_pkey on template t  (cost=0.15..8.17
rows=1 width=161) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (id = 1)
Buffers: shared hit=2
  ->  Index Scan using template_staging_pkey on template_staging t_1
(cost=0.15..8.17 rows=1 width=161) (actual time=0.002..0.002 rows=0
loops=1)
Index Cond: (id = 1)
Filter: (is_deleted IS FALSE)
Buffers: shared hit=1



*EXPLAIN (ANALYZE, BUFFERS) select * from tester t inner join
template_view tv on tv.id  = t.template_idwhere t.id
=1;*

Hash Join  (cost=8.18..48.19 rows=3 width=169) (actual
time=0.019..0.025 rows=1 loops=1)
  Hash Cond: (t_1.id = t.template_id)
  Buffers: shared

Re: Query out of memory

[aditya desai]

Thanks Michael. I will check this further.

On Tue, Oct 19, 2021 at 7:09 PM Michael Lewis  wrote:

> Check explain plan, change work mem to 100MBs and then check explain plan
> again. If it changed, then try explain analyze.
>
> Work mem is limit is used per node in the plan, so especially with
> partitioned tables, that limit is way too high.
>

Re: Query out of memory

[Michael Lewis]

Check explain plan, change work mem to 100MBs and then check explain plan
again. If it changed, then try explain analyze.

Work mem is limit is used per node in the plan, so especially with
partitioned tables, that limit is way too high.

Sv: Fwd: Query out of memory

[Andreas Joseph Krogh]

På tirsdag 19. oktober 2021 kl. 07:58:46, skrev aditya desai <
admad...@gmail.com >: 
Sending to a performance group instead of PLPGSQL.


. 
. 
Hi, 
I am running the below query. Table has 21 million records. I get an Out Of 
Memory error after a while.(from both pgadmin and psql). Can someone review DB 
parameters given below. 

select t.*,g.column,a.column from 
gk_staging g, transaction t,account a 
where 
g.accountcodeis not null AND 
g.accountcode::text <> '' AND 
length(g.accountcode)=13 AND 
g.closeid::text=t.transactionid::text AND 
subsrting(g.accountcode::text,8)=a.mask_code::text 

Below are system parameters. 
shared_buffers=3GB 
work_mem=2GB 
effective_cache_size=10GB 
maintenance_work_mem=1GB 
max_connections=250 

I am unable to paste explain plan here due to security concerns. 

You have not provided schema, explain-output nor the error-message. 
Without this it's pretty much guess-work... 



--
 Andreas Joseph Krogh 

Re: Query out of memory

[aditya desai]

Hi Justin,
Out of memory on pgadmin and psql. I executed it with explain analyze.
Still going out of memory.

 Also currently 250 user connections are not being made. There are hardly
10 connections to database. When I run thi query it is going out of memory.

Also this query is part of a view that gets referred in a
procedure.Transaction table is partitioned table but due to business
requirements partition key is not part of where clause.

Regards,
Aditya.

On Tuesday, October 19, 2021, Justin Pryzby  wrote:

> On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote:
> > I am running the below query. Table has 21 million records. I get an Out
> Of
> > Memory error after a while.(from both pgadmin and psql). Can someone
> review
>
> Is the out of memory error on the client side ?
> Then you've simply returned more rows than the client can support.
>
> In that case, you can run it with "explain analyze" to prove that the
> server
> side can run the query.  That returns no data rows to the client, but
> shows the
> number of rows which would normally be returned.
>
> --
> Justin
>

Re: Fwd: Query out of memory

[Justin Pryzby]

On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote:
> I am running the below query. Table has 21 million records. I get an Out Of
> Memory error after a while.(from both pgadmin and psql). Can someone review

Is the out of memory error on the client side ?
Then you've simply returned more rows than the client can support.

In that case, you can run it with "explain analyze" to prove that the server
side can run the query.  That returns no data rows to the client, but shows the
number of rows which would normally be returned.

-- 
Justin

Re: Query out of memory

[Dave Cramer]

On Tue, 19 Oct 2021 at 05:54, Adam Brusselback 
wrote:

> That work_mem value could be way too high depending on how much ram your
> server has...which would be a very important bit of information to help
> figure this out. Also, what Postgres / OS versions?
>

WORK_MEM is definitely too high. With 250 connections there is no way you
could allocate 2G to each one of them if needed


Dave Cramer
www.postgres.rocks

Re: Query out of memory

[Adam Brusselback]

That work_mem value could be way too high depending on how much ram your
server has...which would be a very important bit of information to help
figure this out. Also, what Postgres / OS versions?