2

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
  • 2
    You should put it in the place that gives you the correct answer. – jjanes Sep 13 '19 at 14:51
  • 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 Sep 13 '19 at 14:56
  • The first one is an inner join – a_horse_with_no_name Sep 13 '19 at 17:16
6

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.

|improve this answer|||||
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.