Should I put criteria inside my join or where clause when optimizing?Optimizing ORDER BY in a full text search queryHow to index WHERE (start_date >= '2013-12-15')How 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 estimatesTrigram search gets much slower as search string gets longerWhy is this query with WHERE, ORDER BY and LIMIT so slow?Index on JSONB not improving query speed
Consonance v. Dissonance
std::variant converting constructor doesn't handle const volatile qualifiers
Which is the current decimal separator?
Can I conceal an antihero's insanity - and should I?
Should you only use colons and periods in dialogues?
Why are some files not movable on Windows 10?
Has SHA256 been broken by Treadwell Stanton DuPont?
I was promised a work PC but still awaiting approval 3 months later so using my own laptop - Is it fair to ask employer for laptop insurance?
What was the ultimate objective of The Party in 1984?
Some Prime Peerage
What next step can I take in solving this sudoku?
How to deal with a DM who makes major changes to a PC without the player's permission?
Is there any reason to concentrate on the Thunderous Smite spell after using its effects?
How many ways you can go from point A to point B
Why is my fire extinguisher emptied after one use?
What is this gigantic dish at Ben Gurion airport?
Is "you will become a subject matter expert" code for "you'll be working on your own 100% of the time"?
Is there a tool to measure the "maturity" of a code in Git?
Output a Super Mario Image
How to be sure services and researches offered by the University are not becoming cases of unfair competition?
How to publish superseding results without creating enemies
Real mode flat model
Asked to Not Use Transactions and to Use A Workaround to Simulate One
Would it be unbalanced to increase Wild Shape uses based on level?
Should I put criteria inside my join or where clause when optimizing?
Optimizing ORDER BY in a full text search queryHow to index WHERE (start_date >= '2013-12-15')How 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 estimatesTrigram search gets much slower as search string gets longerWhy is this query with WHERE, ORDER BY and LIMIT so slow?Index on JSONB not improving query speed
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
1
I am trying to figure out whether query 1 or 2 is more optimal and it appears that having my criteria inside my WHERE clause is more optimal than inside my JOIN clause:
-- 1) "ups" inside where clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" WHERE "carriers"."carrier_name" = 'ups' and "item_events"."property_id" = 895;
-- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..41948.17 rows=2721 width=381) (actual time=35.650..1832.871 rows=3933 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=157.49..40676.07 rows=1134 width=381) (actual time=34.171..1824.416 rows=1311 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.84 rows=1969 width=286) (actual time=30.920..368.885 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1603
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=32.537..32.537 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.784..0.797 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.502 ms
Execution time: 1834.136 ms
Here is Example 2:
-- "ups" inside join clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" AND "carriers"."carrier_name" = 'ups' WHERE "item_events"."property_id" = 895;
-- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..42148.58 rows=4725 width=381) (actual time=6.509..68.214 rows=7205 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop Left Join (cost=157.49..40676.08 rows=1969 width=381) (actual time=2.006..34.262 rows=2402 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.85 rows=1969 width=286) (actual time=1.983..10.989 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1926
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=3.342..3.343 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.008..0.008 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.626 ms
Execution time: 70.719 ms
Here are the schemas (with irrelevant info removed) to the tables:
pz_core_production=> d item_events;
Table "public.item_events"
Column | Type | Collation | Nullable | Default
---------------------------+-----------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('item_events_id_seq'::regclass)
property_id | integer | | |
Indexes:
"item_events_pkey" PRIMARY KEY, btree (id)
"index_item_events_on_property_id_and_captured_at" btree (property_id, captured_at)
pz_core_production=> d carriers;
Table "public.carriers"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('carriers_id_seq'::regclass)
carrier_name | character varying | | |
item_event_id | integer | | |
Indexes:
"carriers_pkey" PRIMARY KEY, btree (id)
"index_carriers_on_carrier_name" btree (carrier_name)
"index_carriers_on_item_event_id_and_carrier_name" btree (item_event_id, carrier_name)
Foreign-key constraints:
"fk_rails_a03506a700" FOREIGN KEY (property_id) REFERENCES properties(id) ON UPDATE CASCADE
postgresql
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
add a comment
|
1
I am trying to figure out whether query 1 or 2 is more optimal and it appears that having my criteria inside my WHERE clause is more optimal than inside my JOIN clause:
-- 1) "ups" inside where clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" WHERE "carriers"."carrier_name" = 'ups' and "item_events"."property_id" = 895;
-- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..41948.17 rows=2721 width=381) (actual time=35.650..1832.871 rows=3933 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=157.49..40676.07 rows=1134 width=381) (actual time=34.171..1824.416 rows=1311 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.84 rows=1969 width=286) (actual time=30.920..368.885 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1603
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=32.537..32.537 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.784..0.797 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.502 ms
Execution time: 1834.136 ms
Here is Example 2:
-- "ups" inside join clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" AND "carriers"."carrier_name" = 'ups' WHERE "item_events"."property_id" = 895;
-- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..42148.58 rows=4725 width=381) (actual time=6.509..68.214 rows=7205 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop Left Join (cost=157.49..40676.08 rows=1969 width=381) (actual time=2.006..34.262 rows=2402 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.85 rows=1969 width=286) (actual time=1.983..10.989 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1926
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=3.342..3.343 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.008..0.008 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.626 ms
Execution time: 70.719 ms
Here are the schemas (with irrelevant info removed) to the tables:
pz_core_production=> d item_events;
Table "public.item_events"
Column | Type | Collation | Nullable | Default
---------------------------+-----------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('item_events_id_seq'::regclass)
property_id | integer | | |
Indexes:
"item_events_pkey" PRIMARY KEY, btree (id)
"index_item_events_on_property_id_and_captured_at" btree (property_id, captured_at)
pz_core_production=> d carriers;
Table "public.carriers"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('carriers_id_seq'::regclass)
carrier_name | character varying | | |
item_event_id | integer | | |
Indexes:
"carriers_pkey" PRIMARY KEY, btree (id)
"index_carriers_on_carrier_name" btree (carrier_name)
"index_carriers_on_item_event_id_and_carrier_name" btree (item_event_id, carrier_name)
Foreign-key constraints:
"fk_rails_a03506a700" FOREIGN KEY (property_id) REFERENCES properties(id) ON UPDATE CASCADE
postgresql
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
You should put it in the place that gives you the correct answer.
– jjanes
8 hours ago
It looks like the difference in time is just because the first query heated up the cache, to the benefit of the second one. When optimizing, you have to run the queries multiple times in different orders.
– jjanes
8 hours ago
The first one is an inner join
– a_horse_with_no_name
5 hours ago
add a comment
|
1
1
1
1
I am trying to figure out whether query 1 or 2 is more optimal and it appears that having my criteria inside my WHERE clause is more optimal than inside my JOIN clause:
-- 1) "ups" inside where clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" WHERE "carriers"."carrier_name" = 'ups' and "item_events"."property_id" = 895;
-- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..41948.17 rows=2721 width=381) (actual time=35.650..1832.871 rows=3933 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=157.49..40676.07 rows=1134 width=381) (actual time=34.171..1824.416 rows=1311 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.84 rows=1969 width=286) (actual time=30.920..368.885 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1603
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=32.537..32.537 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.784..0.797 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.502 ms
Execution time: 1834.136 ms
Here is Example 2:
-- "ups" inside join clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" AND "carriers"."carrier_name" = 'ups' WHERE "item_events"."property_id" = 895;
-- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..42148.58 rows=4725 width=381) (actual time=6.509..68.214 rows=7205 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop Left Join (cost=157.49..40676.08 rows=1969 width=381) (actual time=2.006..34.262 rows=2402 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.85 rows=1969 width=286) (actual time=1.983..10.989 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1926
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=3.342..3.343 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.008..0.008 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.626 ms
Execution time: 70.719 ms
Here are the schemas (with irrelevant info removed) to the tables:
pz_core_production=> d item_events;
Table "public.item_events"
Column | Type | Collation | Nullable | Default
---------------------------+-----------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('item_events_id_seq'::regclass)
property_id | integer | | |
Indexes:
"item_events_pkey" PRIMARY KEY, btree (id)
"index_item_events_on_property_id_and_captured_at" btree (property_id, captured_at)
pz_core_production=> d carriers;
Table "public.carriers"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('carriers_id_seq'::regclass)
carrier_name | character varying | | |
item_event_id | integer | | |
Indexes:
"carriers_pkey" PRIMARY KEY, btree (id)
"index_carriers_on_carrier_name" btree (carrier_name)
"index_carriers_on_item_event_id_and_carrier_name" btree (item_event_id, carrier_name)
Foreign-key constraints:
"fk_rails_a03506a700" FOREIGN KEY (property_id) REFERENCES properties(id) ON UPDATE CASCADE
postgresql
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
I am trying to figure out whether query 1 or 2 is more optimal and it appears that having my criteria inside my WHERE clause is more optimal than inside my JOIN clause:
-- 1) "ups" inside where clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" WHERE "carriers"."carrier_name" = 'ups' and "item_events"."property_id" = 895;
-- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..41948.17 rows=2721 width=381) (actual time=35.650..1832.871 rows=3933 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=157.49..40676.07 rows=1134 width=381) (actual time=34.171..1824.416 rows=1311 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.84 rows=1969 width=286) (actual time=30.920..368.885 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1603
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=32.537..32.537 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.784..0.797 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.502 ms
Execution time: 1834.136 ms
Here is Example 2:
-- "ups" inside join clause
explain analyze select * from item_events LEFT JOIN "carriers" ON "carriers"."item_event_id" = "item_events"."id" AND "carriers"."carrier_name" = 'ups' WHERE "item_events"."property_id" = 895;
-- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1157.49..42148.58 rows=4725 width=381) (actual time=6.509..68.214 rows=7205 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop Left Join (cost=157.49..40676.08 rows=1969 width=381) (actual time=2.006..34.262 rows=2402 loops=3)
-> Parallel Bitmap Heap Scan on item_events (cost=157.05..16828.85 rows=1969 width=286) (actual time=1.983..10.989 rows=1819 loops=3)
Recheck Cond: (property_id = 895)
Heap Blocks: exact=1926
-> Bitmap Index Scan on index_item_events_on_property_id_and_captured_at (cost=0.00..155.87 rows=4725 width=0) (actual time=3.342..3.343 rows=5457 loops=1)
Index Cond: (property_id = 895)
-> Index Scan using index_carriers_on_item_event_id_and_carrier_name on carriers (cost=0.43..12.09 rows=2 width=95) (actual time=0.008..0.008 rows=1 loops=5457)
Index Cond: ((item_event_id = item_events.id) AND ((carrier_name)::text = 'ups'::text))
Planning time: 0.626 ms
Execution time: 70.719 ms
Here are the schemas (with irrelevant info removed) to the tables:
pz_core_production=> d item_events;
Table "public.item_events"
Column | Type | Collation | Nullable | Default
---------------------------+-----------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('item_events_id_seq'::regclass)
property_id | integer | | |
Indexes:
"item_events_pkey" PRIMARY KEY, btree (id)
"index_item_events_on_property_id_and_captured_at" btree (property_id, captured_at)
pz_core_production=> d carriers;
Table "public.carriers"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('carriers_id_seq'::regclass)
carrier_name | character varying | | |
item_event_id | integer | | |
Indexes:
"carriers_pkey" PRIMARY KEY, btree (id)
"index_carriers_on_carrier_name" btree (carrier_name)
"index_carriers_on_item_event_id_and_carrier_name" btree (item_event_id, carrier_name)
Foreign-key constraints:
"fk_rails_a03506a700" FOREIGN KEY (property_id) REFERENCES properties(id) ON UPDATE CASCADE
postgresql
postgresql
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
asked 8 hours ago
Kamilski81Kamilski81
1477 bronze badges
1477 bronze badges
You should put it in the place that gives you the correct answer.
– jjanes
8 hours ago
It looks like the difference in time is just because the first query heated up the cache, to the benefit of the second one. When optimizing, you have to run the queries multiple times in different orders.
– jjanes
8 hours ago
The first one is an inner join
– a_horse_with_no_name
5 hours ago
add a comment
|
You should put it in the place that gives you the correct answer.
– jjanes
8 hours ago
It looks like the difference in time is just because the first query heated up the cache, to the benefit of the second one. When optimizing, you have to run the queries multiple times in different orders.
– jjanes
8 hours ago
The first one is an inner join
– a_horse_with_no_name
5 hours ago
You should put it in the place that gives you the correct answer.
– jjanes
8 hours ago
You should put it in the place that gives you the correct answer.
– jjanes
8 hours ago
It looks like the difference in time is just because the first query heated up the cache, to the benefit of the second one. When optimizing, you have to run the queries multiple times in different orders.
– jjanes
8 hours ago
It looks like the difference in time is just because the first query heated up the cache, to the benefit of the second one. When optimizing, you have to run the queries multiple times in different orders.
– jjanes
8 hours ago
The first one is an inner join
– a_horse_with_no_name
5 hours ago
The first one is an inner join
– a_horse_with_no_name
5 hours ago
add a comment
|
1 Answer
1
active
oldest
votes
5
The queries are not equivalent, they will give different results, so there is very little gain in comparing their performance.
Query 1 has a condition about carriers (the table on the right side of the LEFT join) in the WHERE clause. That essentially converts the join to an INNER join.
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
add a comment
|
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/4.0/"u003ecc by-sa 4.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
);
);
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%2f248737%2fshould-i-put-criteria-inside-my-join-or-where-clause-when-optimizing%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
5
The queries are not equivalent, they will give different results, so there is very little gain in comparing their performance.
Query 1 has a condition about carriers (the table on the right side of the LEFT join) in the WHERE clause. That essentially converts the join to an INNER join.
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
add a comment
|
5
The queries are not equivalent, they will give different results, so there is very little gain in comparing their performance.
Query 1 has a condition about carriers (the table on the right side of the LEFT join) in the WHERE clause. That essentially converts the join to an INNER join.
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
add a comment
|
5
5
5
The queries are not equivalent, they will give different results, so there is very little gain in comparing their performance.
Query 1 has a condition about carriers (the table on the right side of the LEFT join) in the WHERE clause. That essentially converts the join to an INNER join.
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
The queries are not equivalent, they will give different results, so there is very little gain in comparing their performance.
Query 1 has a condition about carriers (the table on the right side of the LEFT join) in the WHERE clause. That essentially converts the join to an INNER join.
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
edited 8 hours ago
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
answered 8 hours ago
ypercubeᵀᴹypercubeᵀᴹ
81.4k11 gold badges140 silver badges230 bronze badges
81.4k11 gold badges140 silver badges230 bronze badges
add a comment
|
add a comment
|
draft saved
draft discarded
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%2f248737%2fshould-i-put-criteria-inside-my-join-or-where-clause-when-optimizing%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
You should put it in the place that gives you the correct answer.
– jjanes
8 hours ago
It looks like the difference in time is just because the first query heated up the cache, to the benefit of the second one. When optimizing, you have to run the queries multiple times in different orders.
– jjanes
8 hours ago
The first one is an inner join
– a_horse_with_no_name
5 hours ago