3 query plans

gistfile1.txt

create index i_hosts_i_h on hosts(name, id);
create index i_hosts_h_i on hosts(id, name);

explain analyze verbose
SELECT "vms".id FROM "vms" left join "hosts" on "hosts"."id" = "vms"."host_id" ORDER BY "hosts"."name"
;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=440.76..448.26 rows=3000 width=36) (actual time=30.724..30.884 rows=3000 loops=1)
   Output: vms.id, hosts.name
   Sort Key: hosts.name
   Sort Method: quicksort  Memory: 331kB
   ->  Hash Left Join  (cost=8.25..267.50 rows=3000 width=36) (actual time=0.077..8.968 rows=3000 loops=1)
         Output: vms.id, hosts.name
         Hash Cond: (vms.host_id = hosts.id)
         ->  Seq Scan on public.vms  (cost=0.00..218.00 rows=3000 width=16) (actual time=0.004..4.176 rows=3000 loops=1)
               Output: vms.id, vms.host_id
         ->  Hash  (cost=7.00..7.00 rows=100 width=36) (actual time=0.064..0.064 rows=100 loops=1)
               Output: hosts.name, hosts.id
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on public.hosts  (cost=0.00..7.00 rows=100 width=36) (actual time=0.002..0.041 rows=100 loops=1)
                     Output: hosts.name, hosts.id
 Planning time: 0.321 ms
 Execution time: 31.071 ms
(16 rows)


explain analyze verbose
SELECT "vms".id FROM "vms" ORDER BY (SELECT "hosts"."name" FROM "hosts" WHERE "hosts"."id" = "vms"."host_id")
;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=22141.26..22148.76 rows=3000 width=16) (actual time=55.235..55.371 rows=3000 loops=1)
   Output: vms.id, ((SubPlan 1))
   Sort Key: ((SubPlan 1))
   Sort Method: quicksort  Memory: 331kB
   ->  Seq Scan on public.vms  (cost=0.00..21968.00 rows=3000 width=16) (actual time=0.041..37.531 rows=3000 loops=1)
         Output: vms.id, (SubPlan 1)
         SubPlan 1
           ->  Seq Scan on public.hosts  (cost=0.00..7.25 rows=1 width=28) (actual time=0.006..0.011 rows=1 loops=3000)
                 Output: hosts.name
                 Filter: (hosts.id = vms.host_id)
                 Rows Removed by Filter: 99
 Planning time: 0.210 ms
 Execution time: 55.567 ms

explain analyze verbose
SELECT "vms".id, (SELECT "hosts"."name" FROM "hosts" WHERE "hosts"."id" = "vms"."host_id")
FROM "vms"
;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.vms  (cost=0.00..21968.00 rows=3000 width=16) (actual time=0.031..27.576 rows=3000 loops=1)
   Output: vms.id, (SubPlan 1)
   SubPlan 1
     ->  Seq Scan on public.hosts  (cost=0.00..7.25 rows=1 width=28) (actual time=0.004..0.008 rows=1 loops=3000)
           Output: hosts.name
           Filter: (hosts.id = vms.host_id)
           Rows Removed by Filter: 99
 Planning time: 0.143 ms
 Execution time: 27.737 ms
(9 rows)