reproduction.sql

DROP TABLE IF EXISTS products,vehicles,phones;
CREATE TABLE products (id int PRIMARY KEY,
  payload text DEFAULT 'product', price real DEFAULT random(), type text);
CREATE TABLE vehicles (id int PRIMARY KEY, maxspeed int DEFAULT 250);
CREATE TABLE phones (id int PRIMARY KEY, screensize real DEFAULT 6.1);

INSERT INTO products (id,type) SELECT x,'v' FROM generate_series(1,5E4) AS x;
INSERT INTO products (id,type) SELECT x,'p' FROM generate_series(1+5E4,1E5) AS x;
INSERT INTO vehicles (id) SELECT x FROM generate_series(1,5E4) AS x;
INSERT INTO phones (id) SELECT x FROM generate_series(1+5E4,1E5) AS x;
VACUUM ANALYZE products, vehicles, phones;

 SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM products p LEFT JOIN phones ph ON (p.id = ph.id AND p.type = 'p')
  LEFT JOIN vehicles v ON (p.id = v.id AND p.type = 'v') WHERE p.price > 0.9;
 
/*
 Nested Loop Left Join (actual rows=10023 loops=1)
   ->  Nested Loop Left Join (actual rows=10023 loops=1)
         ->  Seq Scan on products p (actual rows=10023 loops=1)
               Filter: (price > '0.9'::double precision)
               Rows Removed by Filter: 89977
         ->  Index Scan using phones_pkey on phones ph (actual rows=1 loops=4938)
               Index Cond: (id = p.id)
   ->  Index Scan using vehicles_pkey on vehicles v (actual rows=1 loops=5085)
         Index Cond: (id = p.id)
 Planning Time: 0.562 ms
 Execution Time: 39.865 ms
 */

/*
 Nested Loop Left Join (actual rows=10124 loops=1)
   Join Filter: (p.type = 'v'::text)
   ->  Nested Loop Left Join (actual rows=10124 loops=1)
         Join Filter: (p.type = 'p'::text)
         ->  Seq Scan on products p (actual rows=10124 loops=1)
               Filter: (price > '0.9'::double precision)
               Rows Removed by Filter: 89876
         ->  Index Scan using phones_pkey on phones ph (actual rows=0 loops=10124)
               Index Cond: (id = p.id)
   ->  Index Scan using vehicles_pkey on vehicles v (actual rows=1 loops=10124)
         Index Cond: (id = p.id)
 Planning Time: 0.400 ms
 Execution Time: 58.499 ms
 */