gistfile1.txt

qapoc=# explain select tcr.id, rs.status from newsite.test_case_results tcr, public.result_statuses rs, public.examples e, public.scenarios s, public.feature_files pff, newsite.feature_files ff, newsite.test_cases tc where rs.result_parent_type = 'Example' and rs.result_parent_id = e.id and e.scenario_id = s.id and tcr.run_id = rs.run_id and tcr.test_case_id = tc.id and pff.short_name = ff.name and tc.name = to_number(substring(s.short_name, length(s.short_name)-2, length(s.short_name)),'999');
                                                                                                     QUERY PLAN                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=696199.91..1445979.42 rows=61891866 width=11)
   Hash Cond: ((pff.short_name)::text = (ff.name)::text)
   ->  Seq Scan on feature_files pff  (cost=0.00..150.21 rows=2721 width=10)
   ->  Hash  (cost=529158.91..529158.91 rows=9098400 width=21)
         ->  Nested Loop  (cost=37629.94..529158.91 rows=9098400 width=21)
               ->  Hash Join  (cost=37629.94..415420.91 rows=22746 width=11)
                     Hash Cond: ((tcr.test_case_id = tc.id) AND (to_number("substring"((s.short_name)::text, (length((s.short_name)::text) - 2), length((s.short_name)::text)), '999'::text) = (tc.name)::numeric))
                     ->  Hash Join  (cost=37565.39..301397.98 rows=4549237 width=30)
                           Hash Cond: (rs.run_id = tcr.run_id)
                           ->  Hash Join  (cost=32873.71..143155.10 rows=1365582 width=26)
                                 Hash Cond: (rs.result_parent_id = e.id)
                                 ->  Bitmap Heap Scan on result_statuses rs  (cost=25784.11..93060.88 rows=1365582 width=15)
                                       Recheck Cond: ((result_parent_type)::text = 'Example'::text)
                                       ->  Bitmap Index Scan on result_statuses_parent_type_name  (cost=0.00..25442.71 rows=1365582 width=0)
                                             Index Cond: ((result_parent_type)::text = 'Example'::text)
                                 ->  Hash  (cost=5052.96..5052.96 rows=110931 width=19)
                                       ->  Hash Join  (cost=688.03..5052.96 rows=110931 width=19)
                                             Hash Cond: (e.scenario_id = s.id)
                                             ->  Seq Scan on examples e  (cost=0.00..2146.31 rows=110931 width=8)
                                             ->  Hash  (cost=543.57..543.57 rows=11557 width=19)
                                                   ->  Seq Scan on scenarios s  (cost=0.00..543.57 rows=11557 width=19)
                           ->  Hash  (cost=2471.19..2471.19 rows=127719 width=12)
                                 ->  Seq Scan on test_case_results tcr  (cost=0.00..2471.19 rows=127719 width=12)
                     ->  Hash  (cost=39.02..39.02 rows=1702 width=8)
                           ->  Seq Scan on test_cases tc  (cost=0.00..39.02 rows=1702 width=8)
               ->  Materialize  (cost=0.00..9.00 rows=400 width=10)
                     ->  Seq Scan on feature_files ff  (cost=0.00..7.00 rows=400 width=10)