K P Manoj wrote: > Please find the details of table description > > test=# \d xxx > Table "public.xxx" > Column | Type | Modifiers > ------------------------------+-----------------------------+----------- > crawler_id | bigint | > effective_org | character varying(255) | > reverse_pd | character varying(255) | > Indexes: > "xxx_rev_pd_idx1" btree (reverse_pd) > > > test =#\d tmp > Table "public.tmp" > Column | Type | Modifiers > --------------------+------------------------+----------- > id | bigint | > mdc_domain_reverse | character varying(255) | > Indexes: > "tmp_idx1" btree (mdc_domain_reverse) > "tmp_txt_idx_mdc" btree (mdc_domain_reverse varchar_pattern_ops) > > > test=# EXPLAIN select xxx.* from xxx xxx where exists (select 1 from tmp where mdc_domain_reverse > like 'ttt' || '.%'); > QUERY PLAN > ------------------------------------------------------------------------ ------------------------------ > ---- > Result (cost=0.03..2249.94 rows=13591 width=3141) > One-Time Filter: $0 > InitPlan 1 (returns $0) > -> Index Only Scan using tmp_txt_idx_mdc on tmp (cost=0.00..4.27 rows=144 width=0) > Index Cond: ((mdc_domain_reverse ~>=~ 'ttt.'::text) AND (mdc_domain_reverse ~<~ > 'ttt/'::text)) > Filter: ((mdc_domain_reverse)::text ~~ 'ttt.%'::text) > -> Seq Scan on xxx (cost=0.00..2249.91 rows=13591 width=3141) > (7 rows) > > saleshub=# 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..341301641.67 rows=68 width=3141) > Join Filter: ((tmp.mdc_domain_reverse)::text ~~ ((xxx.reverse_pd)::text || '.%'::text)) > -> Seq Scan on xxx (cost=0.00..2249.91 rows=13591 width=3141) > -> Materialize (cost=0.00..31811.93 rows=1442062 width=18) > -> Seq Scan on tmp (cost=0.00..24601.62 rows=1442062 width=18) > (5 rows) > > > My question was any chance to use query planner with above index ? or i want to change the query ? It looks like I understood you right, and my answer applies: > 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. There is no chance to have the index used with this query. You'll have to change the query so that the LIKE pattern starts with a constant. Maybe in your case (few entries in "xxx") you could use a PL/SQL function that dynamically generates a query for each row in "xxx". 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
