Should I put criteria inside my join or where clause when optimizing?

Question

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

Answer 1

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.

Answer 2

In the general case, restricting the right-hand table with join conditions before projecting it against the left-hand table is more efficient.

From the limited understanding I can gain of your data,

• Option 1 selects all the events having carrier 'ups' and item_events.property 895 - it's effectively an inner join.

• Option 2 selects all the events having item_events.property 895 and gives carrier properties only for carrier 'ups', which is probably not what you want.

• Unless item_event_id in your table carriers refers only to the latest event, it seems to me that the table should really be carriers_events and should refer to a separate carriers table.