K P Manoj wrote: > 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) I don't really understand what your problem is, but if you are complaining that no index is used for the LIKE condition in the first query, you're out of luck: The planner has no way of knowing if the contents of xxx.reverse_pd start with "%" or not. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
