How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in...
0
I have the following legacy tables:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 | Mathew
------------------------------------
payroll
-------------------------------------
| employee_id | salary
-------------------------------------
| 2 | 200000
| 3 | 90000
-------------------------------------
I want to get the following data, after joins and filters:
-----------------------------------------------------------
| address_id | employee_id | address
-----------------------------------------------------------
| 1 | 2 | street 1, NY
| 2 | 2 | street 2, DC
------------------------------------------------------------
I have the following query:
SELECT employee_id, salary, address_arr
FROM employee
LEFT JOIN payroll on payroll.employee_id = employee.employee_id
INNER JOIN
(
SELECT employee_id, ARRAY_AGG(address) as address_arr
FROM addresses
GROUP BY employee_id
) table_address ON table_address.employee_id = employee.employee_id
WHERE employee.employee_id < 1000000
LIMIT 100
OFFSET 0
Above query gives the desired output but is highly unoptimized as the GROUP BY operation occurs over the complete addresses table before being used for JOIN operation the outer query.
Kindly answer:
- How can we avoid the
GROUP BYoperation to occurs over the complete
addresses table by usingLIMIT OFFSETof the outer query? - Will the condition
WHERE employee.employee_id < 1000000be applied on subquery before or after theGROUP BYoperation in the inner query. If the condition is applied after theGROUP BY, how can we avoid that?
postgresql join postgresql-performance group-by subquery
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
I have the following legacy tables:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 | Mathew
------------------------------------
payroll
-------------------------------------
| employee_id | salary
-------------------------------------
| 2 | 200000
| 3 | 90000
-------------------------------------
I want to get the following data, after joins and filters:
-----------------------------------------------------------
| address_id | employee_id | address
-----------------------------------------------------------
| 1 | 2 | street 1, NY
| 2 | 2 | street 2, DC
------------------------------------------------------------
I have the following query:
SELECT employee_id, salary, address_arr
FROM employee
LEFT JOIN payroll on payroll.employee_id = employee.employee_id
INNER JOIN
(
SELECT employee_id, ARRAY_AGG(address) as address_arr
FROM addresses
GROUP BY employee_id
) table_address ON table_address.employee_id = employee.employee_id
WHERE employee.employee_id < 1000000
LIMIT 100
OFFSET 0
Above query gives the desired output but is highly unoptimized as the GROUP BY operation occurs over the complete addresses table before being used for JOIN operation the outer query.
Kindly answer:
- How can we avoid the
GROUP BYoperation to occurs over the complete
addresses table by usingLIMIT OFFSETof the outer query? - Will the condition
WHERE employee.employee_id < 1000000be applied on subquery before or after theGROUP BYoperation in the inner query. If the condition is applied after theGROUP BY, how can we avoid that?
postgresql join postgresql-performance group-by subquery
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Whereaddress_idandaddress
– Akina
51 secs ago
add a comment |
0
0
0
I have the following legacy tables:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 | Mathew
------------------------------------
payroll
-------------------------------------
| employee_id | salary
-------------------------------------
| 2 | 200000
| 3 | 90000
-------------------------------------
I want to get the following data, after joins and filters:
-----------------------------------------------------------
| address_id | employee_id | address
-----------------------------------------------------------
| 1 | 2 | street 1, NY
| 2 | 2 | street 2, DC
------------------------------------------------------------
I have the following query:
SELECT employee_id, salary, address_arr
FROM employee
LEFT JOIN payroll on payroll.employee_id = employee.employee_id
INNER JOIN
(
SELECT employee_id, ARRAY_AGG(address) as address_arr
FROM addresses
GROUP BY employee_id
) table_address ON table_address.employee_id = employee.employee_id
WHERE employee.employee_id < 1000000
LIMIT 100
OFFSET 0
Above query gives the desired output but is highly unoptimized as the GROUP BY operation occurs over the complete addresses table before being used for JOIN operation the outer query.
Kindly answer:
- How can we avoid the
GROUP BYoperation to occurs over the complete
addresses table by usingLIMIT OFFSETof the outer query? - Will the condition
WHERE employee.employee_id < 1000000be applied on subquery before or after theGROUP BYoperation in the inner query. If the condition is applied after theGROUP BY, how can we avoid that?
postgresql join postgresql-performance group-by subquery
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have the following legacy tables:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 | Mathew
------------------------------------
payroll
-------------------------------------
| employee_id | salary
-------------------------------------
| 2 | 200000
| 3 | 90000
-------------------------------------
I want to get the following data, after joins and filters:
-----------------------------------------------------------
| address_id | employee_id | address
-----------------------------------------------------------
| 1 | 2 | street 1, NY
| 2 | 2 | street 2, DC
------------------------------------------------------------
I have the following query:
SELECT employee_id, salary, address_arr
FROM employee
LEFT JOIN payroll on payroll.employee_id = employee.employee_id
INNER JOIN
(
SELECT employee_id, ARRAY_AGG(address) as address_arr
FROM addresses
GROUP BY employee_id
) table_address ON table_address.employee_id = employee.employee_id
WHERE employee.employee_id < 1000000
LIMIT 100
OFFSET 0
Above query gives the desired output but is highly unoptimized as the GROUP BY operation occurs over the complete addresses table before being used for JOIN operation the outer query.
Kindly answer:
- How can we avoid the
GROUP BYoperation to occurs over the complete
addresses table by usingLIMIT OFFSETof the outer query? - Will the condition
WHERE employee.employee_id < 1000000be applied on subquery before or after theGROUP BYoperation in the inner query. If the condition is applied after theGROUP BY, how can we avoid that?
postgresql join postgresql-performance group-by subquery
postgresql join postgresql-performance group-by subquery
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
asked 6 mins ago
nimeshkiranvermanimeshkiranverma
1012
1012
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
nimeshkiranverma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Whereaddress_idandaddress
– Akina
51 secs ago
add a comment |
Whereaddress_idandaddress
– Akina
51 secs ago
Where
address_id and address– Akina
51 secs ago
Where
address_id and address– Akina
51 secs ago
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
nimeshkiranverma is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229214%2fhow-to-apply-outer-limit-offset-and-filters-in-the-subquery-to-avoid-grouping-ov%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
nimeshkiranverma is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
nimeshkiranverma is a new contributor. Be nice, and check out our Code of Conduct.
nimeshkiranverma is a new contributor. Be nice, and check out our Code of Conduct.
nimeshkiranverma is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229214%2fhow-to-apply-outer-limit-offset-and-filters-in-the-subquery-to-avoid-grouping-ov%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Where
address_idandaddress– Akina
51 secs ago