Обсуждение: Re: Query Performance
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 ?
Happiness Always
BKR Sivaprakash
BKR Sivaprakash
On 9/17/22 00:28, sivapostgres@yahoo.com wrote:
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?
Hello,My query is like thisSelect a.field1, a.field2, a.field3From (Select a.field1, b.field2, c.field3From table1 aJoin table2 bon b.something = a.somethingJoin table3 cOn c.something = a.somethingWhere a.field7 = 'value'UNION ALLSelect a.field4, a.field5, a.field6From table11 aJoin table21 bOn b.something = a.somethingWhere a.field8 = 'something' ) aJoin table10 bOn b.field11 = (Select c.field11From table10 cWhere c.field10 = a.field1 ) <- instead of a.field1, if I hardcode value (eg. '100') query runs fasterJoin table21 cOn 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.
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.
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!"
> 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!"