Huge speed difference between left join and inner join - PostgresRunning a UPDATE on 3 million...
Why do all the books in Game of Thrones library have their covers facing the back of the shelf?
Can the "Friends" spell be used without making the target hostile?
What is the industry term for house wiring diagrams?
Why are carbons of Inositol chiral centers?
Why maximum length of IP, TCP, UDP packet is not suit?
Can we "borrow" our answers to populate our own websites?
Book where a space ship journeys to the center of the galaxy to find all the stars had gone supernova
Methods for writing a code review
Has any human ever had the choice to leave Earth permanently?
Memory usage: #define vs. static const for uint8_t
Is there a verb that means to inject with poison?
What species should be used for storage of human minds?
Does the ditching switch allow an A320 to float indefinitely?
Stuck on a Geometry Puzzle
How do you funnel food off a cutting board?
Why did Luke use his left hand to shoot?
Integration of two exponential multiplied by each other
Where do we learn that students are like offspring?
Eww, those bytes are gross
A Missing Symbol for This Logo
Is there a way to not have to poll the UART of an AVR?
Non-Cancer terminal illness that can affect young (age 10-13) girls?
When obtaining gender reassignment/plastic surgery overseas, is an emergency travel document required to return home?
How is it possible that the folder is there yet isnt in the same time?
Huge speed difference between left join and inner join - Postgres
Running a UPDATE on 3 million recordsPostgreSQL 9.2 (PostGIS) performance problemHow can I speed up a Postgres query containing lots of Joins with an ILIKE conditionpostgres explain plan with giant gaps between operationsSlow fulltext search due to wildly inaccurate row estimatesIndex for numeric field is not usedpostgresql 9.2 hash join issueSorting killing my postgresql queryHow to index two tables for JOINed query optimisationPerformance difference in accessing differrent columns in a Postgres Table
It is known that left join is generally slower than inner join, but this difference seems out of proportion.
I have table A with 2,542,526 rows and table B with 30,444 rows.
I ran this query, which returned values in just 37.6 seconds:
SELECT A.*, B.column_a
INTO C FROM A, B
WHERE ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
Then, I ran this query (basically the same tables and columns but with left join. I wanted to get the extra info that a left join provides).
SELECT A.*, B.column_a
INTO D FROM A
LEFT JOIN B
ON ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
That second query ran for 15 hours 30 minutes (hasn't finished yet, still running).
There are GIST indexes on both geom columns. Why is it taking so long? Am I missing something here? I knew it would take longer, but 15 hours seems out of proportion. I'm not very experienced with left joins, so my guess is that I am doing something wrong. I would like to know if I am making some kind of efficiency mistake or if this is normal.
Explain for first query:
"Nested Loop (cost=0.41..218269.24 rows=9 width=479)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
" -> Index Scan using latlonind on A (cost=0.41..6.84 rows=1 width=473)"
" Index Cond: ((B.geom ~ geom) AND (geom && st_expand(B.geom, '0.000524'::double precision)))"
" Filter: ((B.geom && st_expand(geom, '0.000524'::double precision)) AND _st_contains(B.geom, geom) AND _st_dwithin(geom, B.geom, '0.000524'::double precision))"
Explain for second query:
"Nested Loop Left Join (cost=0.00..65359548445.44 rows=2542526 width=479)"
" Join Filter: ((B.geom ~ A.geom) AND (A.geom && st_expand(B.geom, '0.000524'::double precision)) AND (B.geom && st_expand(A.geom, '0.000524'::double precision)) AND _st_contains(B.geom, A.geom) AND _st_dwithin(v (...)"
" -> Seq Scan on A (cost=0.00..281803.26 rows=2542526 width=473)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
postgresql join
asked 5 mins ago
A.T.A.T.
1
New contributor
A.T. 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 |
It is known that left join is generally slower than inner join, but this difference seems out of proportion.
I have table A with 2,542,526 rows and table B with 30,444 rows.
I ran this query, which returned values in just 37.6 seconds:
SELECT A.*, B.column_a
INTO C FROM A, B
WHERE ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
Then, I ran this query (basically the same tables and columns but with left join. I wanted to get the extra info that a left join provides).
SELECT A.*, B.column_a
INTO D FROM A
LEFT JOIN B
ON ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
That second query ran for 15 hours 30 minutes (hasn't finished yet, still running).
There are GIST indexes on both geom columns. Why is it taking so long? Am I missing something here? I knew it would take longer, but 15 hours seems out of proportion. I'm not very experienced with left joins, so my guess is that I am doing something wrong. I would like to know if I am making some kind of efficiency mistake or if this is normal.
Explain for first query:
"Nested Loop (cost=0.41..218269.24 rows=9 width=479)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
" -> Index Scan using latlonind on A (cost=0.41..6.84 rows=1 width=473)"
" Index Cond: ((B.geom ~ geom) AND (geom && st_expand(B.geom, '0.000524'::double precision)))"
" Filter: ((B.geom && st_expand(geom, '0.000524'::double precision)) AND _st_contains(B.geom, geom) AND _st_dwithin(geom, B.geom, '0.000524'::double precision))"
Explain for second query:
"Nested Loop Left Join (cost=0.00..65359548445.44 rows=2542526 width=479)"
" Join Filter: ((B.geom ~ A.geom) AND (A.geom && st_expand(B.geom, '0.000524'::double precision)) AND (B.geom && st_expand(A.geom, '0.000524'::double precision)) AND _st_contains(B.geom, A.geom) AND _st_dwithin(v (...)"
" -> Seq Scan on A (cost=0.00..281803.26 rows=2542526 width=473)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
postgresql join
asked 5 mins ago
A.T.A.T.
1
New contributor
A.T. 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 |
It is known that left join is generally slower than inner join, but this difference seems out of proportion.
I have table A with 2,542,526 rows and table B with 30,444 rows.
I ran this query, which returned values in just 37.6 seconds:
SELECT A.*, B.column_a
INTO C FROM A, B
WHERE ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
Then, I ran this query (basically the same tables and columns but with left join. I wanted to get the extra info that a left join provides).
SELECT A.*, B.column_a
INTO D FROM A
LEFT JOIN B
ON ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
That second query ran for 15 hours 30 minutes (hasn't finished yet, still running).
There are GIST indexes on both geom columns. Why is it taking so long? Am I missing something here? I knew it would take longer, but 15 hours seems out of proportion. I'm not very experienced with left joins, so my guess is that I am doing something wrong. I would like to know if I am making some kind of efficiency mistake or if this is normal.
Explain for first query:
"Nested Loop (cost=0.41..218269.24 rows=9 width=479)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
" -> Index Scan using latlonind on A (cost=0.41..6.84 rows=1 width=473)"
" Index Cond: ((B.geom ~ geom) AND (geom && st_expand(B.geom, '0.000524'::double precision)))"
" Filter: ((B.geom && st_expand(geom, '0.000524'::double precision)) AND _st_contains(B.geom, geom) AND _st_dwithin(geom, B.geom, '0.000524'::double precision))"
Explain for second query:
"Nested Loop Left Join (cost=0.00..65359548445.44 rows=2542526 width=479)"
" Join Filter: ((B.geom ~ A.geom) AND (A.geom && st_expand(B.geom, '0.000524'::double precision)) AND (B.geom && st_expand(A.geom, '0.000524'::double precision)) AND _st_contains(B.geom, A.geom) AND _st_dwithin(v (...)"
" -> Seq Scan on A (cost=0.00..281803.26 rows=2542526 width=473)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
postgresql join
asked 5 mins ago
A.T.A.T.
1
New contributor
A.T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
It is known that left join is generally slower than inner join, but this difference seems out of proportion.
I have table A with 2,542,526 rows and table B with 30,444 rows.
I ran this query, which returned values in just 37.6 seconds:
SELECT A.*, B.column_a
INTO C FROM A, B
WHERE ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
Then, I ran this query (basically the same tables and columns but with left join. I wanted to get the extra info that a left join provides).
SELECT A.*, B.column_a
INTO D FROM A
LEFT JOIN B
ON ST_Within(A.geom, B.geom)
AND ST_DWithin(A.geom, B.geom, 0.000524)
That second query ran for 15 hours 30 minutes (hasn't finished yet, still running).
There are GIST indexes on both geom columns. Why is it taking so long? Am I missing something here? I knew it would take longer, but 15 hours seems out of proportion. I'm not very experienced with left joins, so my guess is that I am doing something wrong. I would like to know if I am making some kind of efficiency mistake or if this is normal.
Explain for first query:
"Nested Loop (cost=0.41..218269.24 rows=9 width=479)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
" -> Index Scan using latlonind on A (cost=0.41..6.84 rows=1 width=473)"
" Index Cond: ((B.geom ~ geom) AND (geom && st_expand(B.geom, '0.000524'::double precision)))"
" Filter: ((B.geom && st_expand(geom, '0.000524'::double precision)) AND _st_contains(B.geom, geom) AND _st_dwithin(geom, B.geom, '0.000524'::double precision))"
Explain for second query:
"Nested Loop Left Join (cost=0.00..65359548445.44 rows=2542526 width=479)"
" Join Filter: ((B.geom ~ A.geom) AND (A.geom && st_expand(B.geom, '0.000524'::double precision)) AND (B.geom && st_expand(A.geom, '0.000524'::double precision)) AND _st_contains(B.geom, A.geom) AND _st_dwithin(v (...)"
" -> Seq Scan on A (cost=0.00..281803.26 rows=2542526 width=473)"
" -> Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"
postgresql join
postgresql join
asked 5 mins ago
A.T.A.T.
1
New contributor
A.T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 5 mins ago
A.T.A.T.
1
New contributor
A.T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 5 mins ago
A.T.A.T.
1
New contributor
A.T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 5 mins ago
A.T.A.T.
1
asked 5 mins ago
A.T.A.T.
1
1
New contributor
A.T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
A.T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
A.T. 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 |
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
});
}
});
A.T. is a new contributor. Be nice, and check out our Code of Conduct.
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%2f230741%2fhuge-speed-difference-between-left-join-and-inner-join-postgres%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
A.T. is a new contributor. Be nice, and check out our Code of Conduct.
A.T. is a new contributor. Be nice, and check out our Code of Conduct.
A.T. is a new contributor. Be nice, and check out our Code of Conduct.
A.T. 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.
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%2f230741%2fhuge-speed-difference-between-left-join-and-inner-join-postgres%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
