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)
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;
 
/*
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.58..9483.92 rows=10178 width=34) (actual time=0.050..58.422 rows=10181 loops=1)
   Outer Tuple Filter: (p.type = 'v'::text)
   Rows Unmatched by Outer Tuple Filter: 5118
   Buffers: shared hit=31180
   ->  Nested Loop Left Join  (cost=0.29..5685.46 rows=10178 width=26) (actual time=0.026..36.407 rows=10181 loops=1)
         Outer Tuple Filter: (p.type = 'p'::text)
         Rows Unmatched by Outer Tuple Filter: 5063
         Buffers: shared hit=15991
         ->  Seq Scan on products p  (cost=0.00..1887.00 rows=10178 width=18) (actual time=0.021..16.838 rows=10181 loops=1)
               Filter: (price > '0.9'::double precision)
               Rows Removed by Filter: 89819
               Buffers: shared hit=637
         ->  Index Scan using phones_pkey on phones ph  (cost=0.29..0.36 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5118)
               Index Cond: (id = p.id)
               Buffers: shared hit=15354
   ->  Index Scan using vehicles_pkey on vehicles v  (cost=0.29..0.36 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5063)
         Index Cond: (id = p.id)
         Buffers: shared hit=15189
 Planning:
   Buffers: shared hit=34
 Planning Time: 0.663 ms
 Execution Time: 59.211 ms
(22 rows)
 */