gistfile1.sql

mc_production=# EXPLAIN SELECT DISTINCT construction_id
mc_production-# FROM surfaces
mc_production-# WHERE
mc_production-# (id IN (SELECT surface_id FROM stocks WHERE interval IN ('M1212', 'D11212', 'D21212', 'D31212')  AND status IN (1,2,3,4)))
mc_production-# ;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=56154.68..56503.41 rows=34873 width=4)
   ->  Nested Loop  (cost=53131.33..56067.50 rows=34873 width=4)
         ->  HashAggregate  (cost=53131.33..53136.56 rows=523 width=4)
               ->  Bitmap Heap Scan on stocks  (cost=8652.43..53044.15 rows=34873 width=4)
                     Recheck Cond: (("interval")::text = ANY ('{M1212,D11212,D21212,D31212}'::text[]))
                     Filter: (status = ANY ('{1,2,3,4}'::integer[]))
                     ->  Bitmap Index Scan on index_stocks_on_interval  (cost=0.00..8643.71 rows=383736 width=0)
                           Index Cond: (("interval")::text = ANY ('{M1212,D11212,D21212,D31212}'::text[]))
         ->  Index Scan using surfaces_pkey on surfaces  (cost=0.00..5.59 rows=1 width=8)
               Index Cond: (id = stocks.surface_id)
(10 rows)

Time: 1.199 ms
mc_production=#
mc_production=# select count(*) from (SELECT DISTINCT construction_id
mc_production(#   FROM surfaces
mc_production(#   WHERE
mc_production(#   (id IN (SELECT surface_id FROM stocks WHERE interval IN ('M1212', 'D11212', 'D21212', 'D31212')  AND status IN (1,2,3,4)))
mc_production(# ) t
mc_production-# ;
 count
-------
  7597
(1 row)

Time: 194.994 ms
mc_production=#
mc_production=# select count(*) from stocks;
 count
--------
 913929
(1 row)

Time: 108.333 ms
mc_production=#
mc_production=# select count(*) from surfaces;
 count
--------
 111545
(1 row)

Time: 28.036 ms