Re: Index is not using

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux