Postgresql why is INNER JOIN so much slower than WHERE
up vote
0
down vote
favorite
I have 2 tables where I copy file name from one table to another in an update operation. Using INNER JOIN makes the query run in 22 seconds when there are just ~4000 rows. Using a WHERE clause allows it to run it in about 200 milliseconds. How and why is this happening, does the INNER JOIN result in additional looping?
Example 1 using INNER JOIN - Takes 22 seconds when table a has about 4k records.
UPDATE table_a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
INNER JOIN table_a AS a
ON tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
Example 2 using WHERE runs in about 200 ms.
UPDATE table_a AS a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
WHERE tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
sql postgresql inner-join
asked Nov 19 at 19:13
Abe
4,09873565
add a comment |
up vote
0
down vote
favorite
I have 2 tables where I copy file name from one table to another in an update operation. Using INNER JOIN makes the query run in 22 seconds when there are just ~4000 rows. Using a WHERE clause allows it to run it in about 200 milliseconds. How and why is this happening, does the INNER JOIN result in additional looping?
Example 1 using INNER JOIN - Takes 22 seconds when table a has about 4k records.
UPDATE table_a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
INNER JOIN table_a AS a
ON tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
Example 2 using WHERE runs in about 200 ms.
UPDATE table_a AS a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
WHERE tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
sql postgresql inner-join
asked Nov 19 at 19:13
Abe
4,09873565
1
I think the first query is missing information that connects the first reference totable_ato the second one. Because of that, I think it updates waaay more data, (all rows, multiple times). So, what is your goal anyway with the first query? Why would you want to write it like that?
– GolezTrol
Nov 19 at 19:22
Yes, that seems to be it. I was just wondering how the INNER JOIN ended up being a cross join and it looks like it is because the 2 table a references are different.
– Abe
Nov 19 at 19:47
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have 2 tables where I copy file name from one table to another in an update operation. Using INNER JOIN makes the query run in 22 seconds when there are just ~4000 rows. Using a WHERE clause allows it to run it in about 200 milliseconds. How and why is this happening, does the INNER JOIN result in additional looping?
Example 1 using INNER JOIN - Takes 22 seconds when table a has about 4k records.
UPDATE table_a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
INNER JOIN table_a AS a
ON tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
Example 2 using WHERE runs in about 200 ms.
UPDATE table_a AS a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
WHERE tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
sql postgresql inner-join
asked Nov 19 at 19:13
Abe
4,09873565
I have 2 tables where I copy file name from one table to another in an update operation. Using INNER JOIN makes the query run in 22 seconds when there are just ~4000 rows. Using a WHERE clause allows it to run it in about 200 milliseconds. How and why is this happening, does the INNER JOIN result in additional looping?
Example 1 using INNER JOIN - Takes 22 seconds when table a has about 4k records.
UPDATE table_a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
INNER JOIN table_a AS a
ON tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
Example 2 using WHERE runs in about 200 ms.
UPDATE table_a AS a SET file_name = tmp.file_name FROM
(
SELECT b.customer_id, b.file_name, b.file_id FROM table_b AS b WHERE b.status = 'A'
) tmp
WHERE tmp.customer_id=a.customer_id AND tmp.file_id=a.file_id;
sql postgresql inner-join
sql postgresql inner-join
asked Nov 19 at 19:13
Abe
4,09873565
asked Nov 19 at 19:13
Abe
4,09873565
edited Nov 19 at 19:51
asked Nov 19 at 19:13
Abe
4,09873565
asked Nov 19 at 19:13
Abe
4,09873565
asked Nov 19 at 19:13
Abe
4,09873565
4,09873565
1
I think the first query is missing information that connects the first reference totable_ato the second one. Because of that, I think it updates waaay more data, (all rows, multiple times). So, what is your goal anyway with the first query? Why would you want to write it like that?
– GolezTrol
Nov 19 at 19:22
Yes, that seems to be it. I was just wondering how the INNER JOIN ended up being a cross join and it looks like it is because the 2 table a references are different.
– Abe
Nov 19 at 19:47
add a comment |
1
I think the first query is missing information that connects the first reference totable_ato the second one. Because of that, I think it updates waaay more data, (all rows, multiple times). So, what is your goal anyway with the first query? Why would you want to write it like that?
– GolezTrol
Nov 19 at 19:22
Yes, that seems to be it. I was just wondering how the INNER JOIN ended up being a cross join and it looks like it is because the 2 table a references are different.
– Abe
Nov 19 at 19:47
1
1
I think the first query is missing information that connects the first reference to
table_a to the second one. Because of that, I think it updates waaay more data, (all rows, multiple times). So, what is your goal anyway with the first query? Why would you want to write it like that?– GolezTrol
Nov 19 at 19:22
I think the first query is missing information that connects the first reference to
table_a to the second one. Because of that, I think it updates waaay more data, (all rows, multiple times). So, what is your goal anyway with the first query? Why would you want to write it like that?– GolezTrol
Nov 19 at 19:22
Yes, that seems to be it. I was just wondering how the INNER JOIN ended up being a cross join and it looks like it is because the 2 table a references are different.
– Abe
Nov 19 at 19:47
Yes, that seems to be it. I was just wondering how the INNER JOIN ended up being a cross join and it looks like it is because the 2 table a references are different.
– Abe
Nov 19 at 19:47
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
The queries are doing totally different things. The first is updating every row in table_a with the expression. I am guessing that there are even multiple updates on the same row.
The two table_as in the first version are two different references to the table. The effect is a cross join because you have no conditions combining them.
The second method is the correct syntax for what you want to do in Postgres.
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
Out of curiosity, how can the equivalent of the second query be written in terms of INNER JOIN?
– Abe
Nov 19 at 19:49
1
@Abe . . . You needjoinconditions (in thewhereclause betweentable_aanda) -- perhaps on the primary key.
– Gordon Linoff
Nov 19 at 19:55
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
The queries are doing totally different things. The first is updating every row in table_a with the expression. I am guessing that there are even multiple updates on the same row.
The two table_as in the first version are two different references to the table. The effect is a cross join because you have no conditions combining them.
The second method is the correct syntax for what you want to do in Postgres.
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
Out of curiosity, how can the equivalent of the second query be written in terms of INNER JOIN?
– Abe
Nov 19 at 19:49
1
@Abe . . . You needjoinconditions (in thewhereclause betweentable_aanda) -- perhaps on the primary key.
– Gordon Linoff
Nov 19 at 19:55
add a comment |
up vote
2
down vote
accepted
The queries are doing totally different things. The first is updating every row in table_a with the expression. I am guessing that there are even multiple updates on the same row.
The two table_as in the first version are two different references to the table. The effect is a cross join because you have no conditions combining them.
The second method is the correct syntax for what you want to do in Postgres.
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
Out of curiosity, how can the equivalent of the second query be written in terms of INNER JOIN?
– Abe
Nov 19 at 19:49
1
@Abe . . . You needjoinconditions (in thewhereclause betweentable_aanda) -- perhaps on the primary key.
– Gordon Linoff
Nov 19 at 19:55
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
The queries are doing totally different things. The first is updating every row in table_a with the expression. I am guessing that there are even multiple updates on the same row.
The two table_as in the first version are two different references to the table. The effect is a cross join because you have no conditions combining them.
The second method is the correct syntax for what you want to do in Postgres.
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
The queries are doing totally different things. The first is updating every row in table_a with the expression. I am guessing that there are even multiple updates on the same row.
The two table_as in the first version are two different references to the table. The effect is a cross join because you have no conditions combining them.
The second method is the correct syntax for what you want to do in Postgres.
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
answered Nov 19 at 19:15
Gordon Linoff
748k34285391
748k34285391
Out of curiosity, how can the equivalent of the second query be written in terms of INNER JOIN?
– Abe
Nov 19 at 19:49
1
@Abe . . . You needjoinconditions (in thewhereclause betweentable_aanda) -- perhaps on the primary key.
– Gordon Linoff
Nov 19 at 19:55
add a comment |
Out of curiosity, how can the equivalent of the second query be written in terms of INNER JOIN?
– Abe
Nov 19 at 19:49
1
@Abe . . . You needjoinconditions (in thewhereclause betweentable_aanda) -- perhaps on the primary key.
– Gordon Linoff
Nov 19 at 19:55
Out of curiosity, how can the equivalent of the second query be written in terms of INNER JOIN?
– Abe
Nov 19 at 19:49
Out of curiosity, how can the equivalent of the second query be written in terms of INNER JOIN?
– Abe
Nov 19 at 19:49
1
1
@Abe . . . You need
join conditions (in the where clause between table_a and a) -- perhaps on the primary key.– Gordon Linoff
Nov 19 at 19:55
@Abe . . . You need
join conditions (in the where clause between table_a and a) -- perhaps on the primary key.– Gordon Linoff
Nov 19 at 19:55
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53381176%2fpostgresql-why-is-inner-join-so-much-slower-than-where%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
1
I think the first query is missing information that connects the first reference to
table_ato the second one. Because of that, I think it updates waaay more data, (all rows, multiple times). So, what is your goal anyway with the first query? Why would you want to write it like that?– GolezTrol
Nov 19 at 19:22
Yes, that seems to be it. I was just wondering how the INNER JOIN ended up being a cross join and it looks like it is because the 2 table a references are different.
– Abe
Nov 19 at 19:47