Index is not using

[K P Manoj <kpmanojpg@xxxxxxxxx>]

Hi All

I am facing query performance in one of my testing server.

How i can create index with table column name ?

EXPLAIN select xxx.* from xxx xxx where exists (select 1 from tmp where mdc_domain_reverse like xxx.reverse_pd || '.%');
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..315085375.74 rows=63 width=3142)
   Join Filter: ((tmp.mdc_domain_reverse)::text ~~ ((xxx.reverse_pd)::text || '.%'::text))
   ->  Seq Scan on xxx  (cost=0.00..6276.47 rows=12547 width=3142)
   ->  Materialize  (cost=0.00..31811.93 rows=1442062 width=17)
         ->  Seq Scan on tmp  (cost=0.00..24601.62 rows=1442062 width=17)


saleshub=# EXPLAIN  create table tmp2 as select xxx.* from xxx xxx where exists (select 1 from tmp where mdc_domain_reverse like 'moc.ytirucesspc%') ;
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.06..6276.53 rows=12547 width=3142)
   One-Time Filter: $0
   InitPlan 1 (returns $0)
     ->  Index Scan using tmp_txt_idx_mdc on tmp  (cost=0.00..8.53 rows=144 width=0)
           Index Cond: (((mdc_domain_reverse)::text ~>=~ 'moc.ytirucesspc'::text) AND ((mdc_domain_reverse)::text ~<~ 'moc.ytirucesspd'::text))
           Filter: ((mdc_domain_reverse)::text ~~ 'moc.ytirucesspc%'::text)
   ->  Seq Scan on xxx  (cost=0.00..6276.47 rows=12547 width=3142)