Re: Index is not using

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