pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex is not using
9+ messages / 5 participants
[nested] [flat]
* index is not using
@ 2010-02-09 07:43 AI Rumman <rummandba@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: AI Rumman @ 2010-02-09 07:43 UTC (permalink / raw)
To: pgsql-performance; pgsql-general@postgresql.org
I have created a index
create index leadaddress_phone_idx on
leadaddress(regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
'g'::text));
But the index is not using.
explain select * from leadaddress where
regexp_replace(phone,'[^0-9]*','','g') like '%2159438606';
QUERY
PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on leadaddress (cost=100000000.00..100009699.81 rows=1 width=97)
Filter: (regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
'g'::text) ~~ '%2159438606'::text)
Could anyone please tell me why? I analyzed the table after index creation.
^ permalink raw reply [nested|flat] 9+ messages in thread
* index is not using?
@ 2010-02-09 07:55 AI Rumman <rummandba@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: AI Rumman @ 2010-02-09 07:55 UTC (permalink / raw)
To: pgsql-performance
I have created a index
create index leadaddress_phone_idx on
leadaddress(regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
'g'::text));
But the index is not using.
explain select * from leadaddress where
regexp_replace(phone,'[^0-9]*','','g') like '%2159438606';
QUERY
PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on leadaddress (cost=100000000.00..100009699.81 rows=1 width=97)
Filter: (regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
'g'::text) ~~ '%2159438606'::text)
Could anyone please tell me why? I analyzed the table after index creation.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: index is not using?
@ 2010-02-09 08:33 Scott Marlowe <scott.marlowe@gmail.com>
parent: AI Rumman <rummandba@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2010-02-09 08:33 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
On Tue, Feb 9, 2010 at 12:55 AM, AI Rumman <rummandba@gmail.com> wrote:
> I have created a index
> create index leadaddress_phone_idx on
> leadaddress(regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
> 'g'::text));
>
> But the index is not using.
like '%yada'
isn't capable of using an index. If it's left anchored:
like 'yada%'
it can.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: index is not using
@ 2010-02-09 09:34 Guillaume Lelarge <guillaume@lelarge.info>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Guillaume Lelarge @ 2010-02-09 09:34 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org
Le 09/02/2010 08:43, AI Rumman a écrit :
> I have created a index
> create index leadaddress_phone_idx on
> leadaddress(regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
> 'g'::text));
>
> But the index is not using.
>
> explain select * from leadaddress where
> regexp_replace(phone,'[^0-9]*','','g') like '%2159438606';
> QUERY
> PLAN
> --------------------------------------------------------------------------------------------------------
> Seq Scan on leadaddress (cost=100000000.00..100009699.81 rows=1 width=97)
> Filter: (regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
> 'g'::text) ~~ '%2159438606'::text)
>
> Could anyone please tell me why? I analyzed the table after index creation.
>
The index cannot be used if the filter is '%something' or
'%somethingelse%'. I can only be used for 'this%'.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: index is not using
@ 2010-02-09 09:43 Scott Marlowe <scott.marlowe@gmail.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2010-02-09 09:43 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org
I just answered this less than an hour ago... And please don't cross
post to multiple mailing lists.
On Tue, Feb 9, 2010 at 12:43 AM, AI Rumman <rummandba@gmail.com> wrote:
> I have created a index
> create index leadaddress_phone_idx on
> leadaddress(regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
> 'g'::text));
>
> But the index is not using.
>
> explain select * from leadaddress where
> regexp_replace(phone,'[^0-9]*','','g') like '%2159438606';
> QUERY
> PLAN
> --------------------------------------------------------------------------------------------------------
> Seq Scan on leadaddress (cost=100000000.00..100009699.81 rows=1 width=97)
> Filter: (regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
> 'g'::text) ~~ '%2159438606'::text)
>
> Could anyone please tell me why? I analyzed the table after index creation.
>
--
When fascism comes to America, it will be intolerance sold as diversity.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Index is not using
@ 2012-11-12 07:31 K P Manoj <kpmanojpg@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: K P Manoj @ 2012-11-12 07:31 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index is not using
@ 2012-11-12 08:31 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: K P Manoj <kpmanojpg@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Albe Laurenz @ 2012-11-12 08:31 UTC (permalink / raw)
To: K P Manoj *EXTERN* <kpmanojpg@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index is not using
@ 2012-11-12 08:42 K P Manoj <kpmanojpg@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 9+ messages in thread
From: K P Manoj @ 2012-11-12 08:42 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-performance
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.albe@wien.gv.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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index is not using
@ 2012-11-12 09:44 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: K P Manoj <kpmanojpg@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Albe Laurenz @ 2012-11-12 09:44 UTC (permalink / raw)
To: K P Manoj *EXTERN* <kpmanojpg@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2012-11-12 09:44 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-09 07:43 index is not using AI Rumman <rummandba@gmail.com>
2010-02-09 09:34 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-02-09 09:43 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-02-09 07:55 index is not using? AI Rumman <rummandba@gmail.com>
2010-02-09 08:33 ` Re: index is not using? Scott Marlowe <scott.marlowe@gmail.com>
2012-11-12 07:31 Index is not using K P Manoj <kpmanojpg@gmail.com>
2012-11-12 08:31 ` Re: Index is not using Albe Laurenz <laurenz.albe@wien.gv.at>
2012-11-12 08:42 ` Re: Index is not using K P Manoj <kpmanojpg@gmail.com>
2012-11-12 09:44 ` Re: Index is not using Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox