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.
BKR Sivaprakash
On Saturday, 17 September, 2022 at 03:33:48 pm IST, Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
On 2022-09-17 05:28:25 +0000, sivapostgres@xxxxxxxxx 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@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
> 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@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
