Обсуждение: Index is not using

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)

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

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