Re: Query Performance

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(at)hjp(dot)at> wrote:

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