Re: Query Performance

[Ron]

On 9/17/22 00:28, sivapostg...@yahoo.com wrote:

Hello,

My query is like this

Select a.field1, a.field2, a.field3
From   (Select a.field1, b.field2, c.field3
        From   table1 a
        Join   table2 b
        on     b.something = a.something
        Join   table3 c
        On     c.something = a.something
        Where  a.field7 = 'value'

        UNION ALL

        Select a.field4, a.field5, a.field6
        From   table11 a
        Join   table21 b
        On     b.something = a.something
        Where  a.field8 = 'something' ) a
Join   table10 b
On     b.field11 = (Select c.field11
                    From   table10 c
                    Where  c.field10 = a.field1 )                <- 
instead of a.field1, if I hardcode value (eg. '100') query runs faster

Join   table21 c
On     c.something = a.something
...


In the above query, If I substitute a value for a.field1, query runs faster.
Any suggestion/guidance/links to improve the query performance without 
substituting the value ?


Is there an index on table1.field1?
Have you vacuumed or analyzed table1 lately?
Have you decomposed the query into the smallest possible query that 
demonstrates the problem?



--
Angular momentum makes the world go 'round.

Re: Query Performance

[Peter J. Holzer]

On 2022-09-17 05:28:25 +, sivapostg...@yahoo.com wrote:
> My query is like this   
> 
> Select a.field1, a.field2, a.field3
> From   (Select a.field1, b.field2, c.field3
> From   table1 a
> Join   table2 b
> on b.something = a.something
> Join   table3 c
> On c.something = a.something 
> Where  a.field7 = 'value'
> 
> UNION ALL
> 
> Select a.field4, a.field5, a.field6
> From   table11 a
> Join   table21 b
> On b.something = a.something
> Where  a.field8 = 'something' ) a
> Join   table10 b
> On b.field11 = (Select c.field11
> From   table10 c
> Where  c.field10 = a.field1 ) <-
> instead of a.field1, if I hardcode value (eg. '100') query runs faster
> Join   table21 c
> On c.something = a.something
> ...

Well, you are now searching table10 for a constant value (which can be
done once) instead of the output of the union (which has to be done for
each line of the union, so I'm not surprised that it's faster.

What is the output of `explain (analyze)` for the two queries? Is there
an obvious place where an index would help? Can you restructure the
query?

BTW, it is (at least for me) very hard to give advice on a query with
only completely abstract names like `table11` or `field4`: I have no
idea what this is supposed to do, so it's hard to tell if there is a
better way. Using `a` to refer to 3 different things doesn't help
either.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Query Performance

[sivapostg...@yahoo.com]

 I should have given you the full query.   Here it is
 Select a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber, 
a.expirydate,         a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk, 
        Sum(a.quantity) as quantity, Sum(a.freequantity) as freequantity,       
  b.beforetaxrate as beforetaxrate, (b.rate / b.rateper) as purchaserate, 
b.netrate as netrate,         b.netsalesrate as netsalesrate, b.effectiverate 
as effectiverate, b.rateper, a.reportformat,         g.standardcost, 
g.defaultpurchaserate,         g.salesrateone, g.salesratetwo, 
g.salesratethree, g.salesratefour, g.salesratefive, g.salesratesix,         
g.salesrateseven, g.salesrateeight, g.salesratenine, 'N' as negativerate  From  
(Select k.locationname, a.usercode, a.itemname, c.uomname, j.batchnumber, 
j.expirydate,                 j.itemnamefk, j.itemuomfk, k.locationpk,          
     j.receivedquantity as quantity, j.receivedfreequantity as freequantity, 
c.reportformat         From   in_item_name a         Join   in_item_uom b       
  On     b.itemnamefk = a.itemnamepk         Join   gl_uom c         On     
c.uompk = b.uomfk         Join   view_item_receipts j         On     
j.itemnamefk = a.itemnamepk         And    j.itemuomfk = b.itemuompk         
Join   in_location k         On     k.locationpk = j.locationfk         Where  
j.companycode = 'SDM'        And    j.branchcode = '001'        And    
j.accountperiodid = 1        And    j.voucherdate <= '2022/09/17'        And    
j.billstatus <> 'C'         And    j.topparentcode <> 4         And    
(j.receivedquantity <> 0 Or j.receivedfreequantity <> 0)         UNION ALL      
   Select k.locationname, a.usercode, a.itemname, c.uomname, j.batchnumber, 
j.expirydate,                 j.itemnamefk, j.itemuomfk, k.locationpk,          
     (j.issuedquantity * -1) as quantity, (j.issuedfreequantity * -1) as 
freequantity, c.reportformat         From   in_item_name a         Join   
in_item_uom b         On     b.itemnamefk = a.itemnamepk         Join   gl_uom 
c         On     c.uompk = b.uomfk         Join   view_item_issues j         On 
    j.itemnamefk = a.itemnamepk         And    j.itemuomfk = b.itemuompk        
 Join   in_location k         On     k.locationpk = j.locationfk         Where  
j.companycode = 'SDM'        And    j.branchcode = '001'        And    
j.accountperiodid = 1        And    j.voucherdate <= '2022/09/17'        And    
j.billstatus <> 'C'         And    j.topparentcode <> 4         And    
(j.issuedquantity <> 0 Or j.issuedfreequantity <> 0)) a  Left Outer Join 
view_item_receipts b    <- It's actually a view 
of 4 tables which tries to arrive the last purchase rate On     
b.itemreceiptspk = (Select c.itemreceiptspk                             From  
view_item_receipts c                             Where c.companycode = 'SDM'    
                        And   c.branchcode = '001'                            
And   c.accountperiodid = 1                            And   c.voucherdate <= 
'2022/09/17'                            And   c.billstatus <> 'C'               
              And   c.itemnamefk = a.itemnamefk                             And 
  c.itemuomfk = a.itemuomfk                             And   c.batchnumber = 
a.batchnumber                             And   c.expirydate = a.expirydate     
                        And   (c.receivedquantity <> 0 Or 
c.receivedfreequantity <> 0)                             Order by c.voucherdate 
desc, c.vouchernumber desc, c.sequencenumber desc                             
Limit 1 )  Join   in_item_name c  On     c.itemnamepk = a.itemnamefk  Join   
in_item_group f  On     f.itemgrouppk = c.itemgroupfk  Left Outer Join 
in_item_rate g  On     g.itemuomfk = b.itemuomfk  And    g.itemnamefk = 
b.itemnamefk  And    '2022/09/17' between g.fromdate and g.todate  Group By 
a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber, a.expirydate, 
          a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk,           
b.beforetaxrate, b.rate, b.netrate, b.netsalesrate, b.effectiverate, b.rateper, 
a.reportformat,           g.standardcost, g.defaultpurchaserate,           
g.salesrateone, g.salesratetwo, g.salesratethree, g.salesratefour, 
g.salesratefive, g.salesratesix,           g.salesrateseven, g.salesrateeight, 
g.salesratenine  Having   (sum(a.quantity) + sum(a.freequantity)) <> 0  Order 
by 1, 3, 2, 5 

Create a index for companycode,  branchcode,  c.accountperiodid, voucherdate, 
billstatus, itemnamefk, itemuomfk, batchnumber, expirydate, receivedquantity, 
receivedfreequantity
in all the 4 tables that this view got.
Happiness Always
BKR Sivaprakash
On Saturday, 17 September, 2022 at 03:33:48 pm IST, Peter J. Holzer 
 wrote:  
 
 On 2022-09-17 05:28:25 +, sivapostg...@yahoo.com wrote:
> My query is like this  
> 
> Select a.field1, a.field2, a.field3
> From  (Select a.field1, b.field2, c.field3
>        From  table1 a
>       

Re: get user info on log

[Adrian Klaver]

On 9/16/22 19:22, Ganesh Korde wrote:

Hi,

   You can use log_line_prefix in postgresql.conf to log the user name 
into the logs.


That uses the session user, the user that established the connection. 
What the OP is after is the role name that is set by SET ROLE or SET 
SESSION AUTHORIZATION.


To illustrate:

psql -d test -U postgres

test(5432)=# select session_user, current_user;
 session_user | current_user
--+--
 postgres | postgres


test(5432)=# set role aklaver;
SET
test(5432)=> select session_user, current_user;
 session_user | current_user
--+--
 postgres | aklaver
(1 row)


test(5432)=> select 1/0;
ERROR:  division by zero

From log with log_line_prefix of log_line_prefix = '%a-%u-%m-%x'
:

psql-postgres-2022-09-17 14:36:06.635 PDT-0ERROR:  division by zero
psql-postgres-2022-09-17 14:36:06.635 PDT-0STATEMENT:  select 1/0;

Ganesh is looking for psql- not psql-, in 
this case psql-aklaver.




Regards,
Ganesh Korde.

On Fri, 16 Sep 2022, 6:31 pm Marcos Pegoraro, > wrote:


Em qui., 15 de set. de 2022 às 12:59, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> escreveu:

test(5432)=# set role maura;
ERROR:  role "maura" does not exist
test(5432)=# SET SESSION AUTHORIZATION 'maura';
ERROR:  role "maura" does not exist

No, I was asking about an error occurring later, not on set session
authorization command.

set role maura;
--user Maura exists and set was done correctly
--now I´m working as Maura
select 1/0; -- I would like to see this exception on log being
logged as maura

thanks
Marcos




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: get user info on log

[Adrian Klaver]

On 9/16/22 06:00, Marcos Pegoraro wrote:
Em qui., 15 de set. de 2022 às 12:59, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> escreveu:


test(5432)=# set role maura;
ERROR:  role "maura" does not exist
test(5432)=# SET SESSION AUTHORIZATION 'maura';
ERROR:  role "maura" does not exist

No, I was asking about an error occurring later, not on set session 
authorization command.


set role maura;
--user Maura exists and set was done correctly
--now I´m working as Maura
select 1/0; -- I would like to see this exception on log being logged as 
maura


I don't know of a way to get this.



thanks
Marcos




--
Adrian Klaver
adrian.kla...@aklaver.com