gistfile1.sql

mc_stage=# EXPLAIN SELECT DISTINCT construction_id
mc_stage-# FROM surfaces
mc_stage-# WHERE 
mc_stage-# (id IN (SELECT surface_id FROM stocks WHERE interval IN ('M1212', 'D11212', 'D21212', 'D31212')  AND status IN (1,2,3,4)))
mc_stage-# ;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=11763.63..11835.57 rows=7194 width=4)
   ->  Nested Loop  (cost=10647.01..11745.64 rows=7194 width=4)
         ->  HashAggregate  (cost=10647.01..10652.21 rows=520 width=4)
               ->  Bitmap Heap Scan on stocks  (cost=1437.66..10629.02 rows=7194 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..1435.86 rows=78768 width=0)
                           Index Cond: (("interval")::text = ANY ('{M1212,D11212,D21212,D31212}'::text[]))
         ->  Index Scan using surfaces_pkey on surfaces  (cost=0.00..2.09 rows=1 width=8)
               Index Cond: (id = stocks.surface_id)
(10 rows)

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

Time: 60.164 ms
mc_stage=# 
mc_stage=# select count(*) from stocks;
 count  
--------
 913847
(1 row)

Time: 65.776 ms
mc_stage=# 
mc_stage=# select count(*) from surfaces;
 count  
--------
 111549
(1 row)

Time: 10.802 ms