gistfile1.txt

                                                                                          QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=45.17..818.21 rows=3 width=85) (actual time=16.043..22.796 rows=2 loops=1)
   ->  Nested Loop Left Join  (cost=44.75..800.09 rows=3 width=85) (actual time=16.019..22.741 rows=2 loops=1)
         Join Filter: (fs.environment_id = env.id)
         Rows Removed by Join Filter: 6
         ->  Nested Loop  (cost=44.75..798.86 rows=3 width=89) (actual time=16.007..22.724 rows=2 loops=1)
               Join Filter: (fp.value_type_id = vt.id)
               Rows Removed by Join Filter: 10
               ->  Seq Scan on value_types vt  (cost=0.00..1.06 rows=6 width=14) (actual time=0.003..0.005 rows=6 loops=1)
               ->  Materialize  (cost=44.75..797.54 rows=3 width=91) (actual time=2.667..3.785 rows=2 loops=6)
                     ->  Nested Loop  (cost=44.75..797.53 rows=3 width=91) (actual time=15.992..22.698 rows=2 loops=1)
                           ->  Hash Join  (cost=44.62..797.03 rows=3 width=83) (actual time=15.986..22.686 rows=2 loops=1)
                                 Hash Cond: (f.fact_value_id = fv.id)
                                 ->  Seq Scan on facts f  (cost=0.00..605.28 rows=39228 width=16) (actual time=0.003..4.079 rows=39228 loops=1)
                                 ->  Hash  (cost=44.58..44.58 rows=3 width=83) (actual time=11.295..11.295 rows=2 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                       ->  Nested Loop  (cost=4.73..44.58 rows=3 width=83) (actual time=11.257..11.291 rows=2 loops=1)
                                             ->  Bitmap Heap Scan on fact_paths fp  (cost=4.32..19.24 rows=3 width=55) (actual time=11.241..11.264 rows=2 loops=1)
                                                   Recheck Cond: ((path ~ 'abc'::text) AND (path IS NOT NULL))
                                                   Rows Removed by Index Recheck: 8
                                                   Filter: (value_type_id <> 5)
                                                   ->  Bitmap Index Scan on fact_paths_path_trgm  (cost=0.00..4.32 rows=4 width=0) (actual time=11.209..11.209 rows=10 loops=1)
                                                         Index Cond: ((path ~ 'abc'::text) AND (path IS NOT NULL))
                                             ->  Index Scan using fact_values_path_id_value_key on fact_values fv  (cost=0.41..8.43 rows=1 width=44) (actual time=0.010..0.010 rows=1 loops=2)
                                                   Index Cond: (path_id = fp.id)
                           ->  Index Scan using factsets_pkey on factsets fs  (cost=0.14..0.16 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=2)
                                 Index Cond: (id = f.factset_id)
         ->  Materialize  (cost=0.00..1.06 rows=4 width=12) (actual time=0.004..0.005 rows=4 loops=2)
               ->  Seq Scan on environments env  (cost=0.00..1.04 rows=4 width=12) (actual time=0.002..0.003 rows=4 loops=1)
   ->  Nested Loop Left Join  (cost=0.42..6.02 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=2)
         ->  Index Scan using certnames_pkey on certnames  (cost=0.14..1.15 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=2)
               Index Cond: (name = fs.certname)
               Filter: (deactivated IS NULL)
         ->  Nested Loop Semi Join  (cost=0.29..4.85 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=2)
               ->  Index Scan using idx_reports_certname on reports  (cost=0.14..3.16 rows=1 width=134) (actual time=0.002..0.002 rows=0 loops=2)
                     Index Cond: (certnames.name = certname)
               ->  Index Only Scan using idx_latest_reports_report on latest_reports  (cost=0.15..1.31 rows=3 width=98) (never executed)
                     Index Cond: (report = (reports.hash)::text)
                     Heap Fetches: 0
 Total runtime: 23.647 ms
(39 rows)

Time: 31.418 ms