Re: Query Performance

On 9/17/22 00:28, sivapostgres@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.

On 2022-09-17 05:28:25 +0000, sivapostgres@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.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

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 <hjp-pgsql@hjp.at> wrote:

On 2022-09-17 05:28:25 +0000, sivapostgres@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.
|_|_) |                    |
| |  | hjp@hjp.at        |    -- Charles Stross, "Creative writing
__/  | http://www.hjp.at/ |      challenge!"