Re: Index is not using

Hi Albe,
Thank you for your reply ,

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 ?

Regards
Manoj K P

On Mon, Nov 12, 2012 at 2:01 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> 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
>