0

can anyone explain why the first query is bit faster than the second?

explain analyze select count(*) from view_recurrence_service_plan_description a
left join (select * from vehicle_roster where cast(schedule_date as date)=current_date) vr on a.recurrence_service_plan_id=vr.recurrence_service_plan_id
left join vehicle_route vroute on vroute.vehicle_route_id=vr.vehicle_route_id  
left join vehicle on vehicle.vehicle_id=vroute.vehicle_id;  
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=736.29..736.30 rows=1 width=0) (actual time=13.206..13.206 rows=1 loops=1)
   ->  Hash Left Join  (cost=1.15..706.42 rows=11946 width=0) (actual time=0.041..11.401 rows=11946 loops=1)
         Hash Cond: (recurrence_service_plan.recurrence_service_plan_id = vehicle_roster.recurrence_service_plan_id)
         ->  Seq Scan on recurrence_service_plan  (cost=0.00..660.46 rows=11946 width=24) (actual time=0.010..8.170 rows=11946 loops=1)
               Filter: (NOT hide)
         ->  Hash  (cost=1.14..1.14 rows=1 width=16) (actual time=0.021..0.021 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 0kB
               ->  Seq Scan on vehicle_roster  (cost=0.00..1.14 rows=1 width=16) (actual time=0.021..0.021 rows=0 loops=1)
                     Filter: ((schedule_date)::date = ('now'::cstring)::date)
                     Rows Removed by Filter: 10
 Total runtime: 13.286 ms
(11 rows)



=========================================================================================================================================================


explain analyze select count(*) from view_recurrence_service_plan_description a
left join vehicle_roster vr on a.recurrence_service_plan_id=vr.recurrence_service_plan_id and cast(vr.schedule_date as date)=current_date
left join vehicle_route vroute on vroute.vehicle_route_id=vr.vehicle_route_id
left join vehicle on vehicle.vehicle_id=vroute.vehicle_id;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=736.29..736.30 rows=1 width=0) (actual time=13.355..13.356 rows=1 loops=1)
   ->  Hash Left Join  (cost=1.15..706.42 rows=11946 width=0) (actual time=0.040..11.549 rows=11946 loops=1)
         Hash Cond: (recurrence_service_plan.recurrence_service_plan_id = vr.recurrence_service_plan_id)
         ->  Seq Scan on recurrence_service_plan  (cost=0.00..660.46 rows=11946 width=24) (actual time=0.010..7.905 rows=11946 loops=1)
               Filter: (NOT hide)
         ->  Hash  (cost=1.14..1.14 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 0kB
               ->  Seq Scan on vehicle_roster vr  (cost=0.00..1.14 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=1)
                     Filter: ((schedule_date)::date = ('now'::cstring)::date)
                     Rows Removed by Filter: 10
 Total runtime: 13.436 ms
(11 rows)

Im working with multiple left joins and I'm trying to optimize it. Every second counts for me. Can I use left join lateral here? Any tips on optimizing?

Eleven
  • 339
  • 2
  • 6
  • 20
  • So 13 **milli**seconds (that's 0.013 seconds) is still too slow? How fast do you need it to be? –  Nov 02 '15 at 11:34
  • that is just excerpt of my whole query. I have 10 left joins which I really need. The whole query runs 15 secs by the way – Eleven Nov 02 '15 at 11:37
  • 3
    Then show us the complete query –  Nov 02 '15 at 11:40
  • 1
    You can't really cut out 99% of the query and say it's slow, when you don't show the slow part. – Craig Ringer Nov 02 '15 at 13:18
  • With times this close, it's not clear that one is truly faster than the other. You should measure the average execution time for each of these over several (10?) runs, probably after warmup (i.e. run it 11 times and average runs 2-11). – Ken Geis Nov 02 '15 at 18:19

1 Answers1

0

As you didn't provide the whole query, and as we don't know the functional meaning of your query, I would suggest the following :

Without changing your query, you can right now identify the columns used in your where statements and verify that you have indexes. (Depending of course on the volume of data). If everything seems to be fine for you in terms of indexes, try to update your table statistics. Otherwise, add indexes if necessary.

RMS
  • 26
  • 2