Is my text_pattern_ops index working for a LIKE prefix search withcolumn reference?

Поиск
Список
Период
Сортировка
От Seamus Abshere
Тема Is my text_pattern_ops index working for a LIKE prefix search withcolumn reference?
Дата
Msg-id 1535036221.3796184.1483854296.64E1B683@webmail.messagingengine.com
обсуждение исходный текст
Ответы Re: Is my text_pattern_ops index working for a LIKE prefix searchwith column reference?  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-general
hi,

I created an index with text_pattern_ops because I want fast prefix search [1] [2].

CREATE INDEX ON mytable USING BTREE (state, city, address text_pattern_ops);

(Note it's composite - I'm looking for prefixes on the address part only.) I can see that it "works" (purely uses the
index)for prefix searches without column references:
 

# explain select 1 from mytable where state = 'x' and city = 'x' and address like 'asd%';
QUERY PLAN
--
 Index Only Scan using mytable_state_city_house_number_and_s_idx1 on mytable  (cost=0.41..4.44 rows=1 width=4)
   Index Cond: ((state = 'x'::text) AND (city = 'x'::text) AND (address ~>=~ 'asd'::text) AND (address ~<~
'ase'::text))
   Filter: (address ~~ 'asd%'::text)
(3 rows)

...but I don't think it's working when I use a column reference || '%'... I say that because "address" no longer
appearsin the "Index Cond":
 

# explain select (select 1 from mytable where state = 'x' and city = 'x' and address like
generate_series.generate_series::text|| '%') t2 from generate_series(0, 10);
 
QUERY PLAN
--
 Function Scan on generate_series  (cost=0.00..4455.00 rows=1000 width=4)
   SubPlan 1
     ->  Index Only Scan using mytable_state_city_house_number_and_s_idx1 on mytable  (cost=0.41..4.44 rows=1 width=4)
           Index Cond: ((state = 'x'::text) AND (city = 'x'::text))
           Filter: (address ~~ ((generate_series.generate_series)::text || '%'::text))
(5 rows)

Any advice? I'm on 10.3. Also, has this changed between 9.6 and 10.3?

And... maybe I'm asking the wrong question, how can I do a fast search on state, city, address prefix?

Thanks,
Seamus

[1] http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html
[2] https://blog.2ndquadrant.com/text-search-strategies-in-postgresql/


В списке pgsql-general по дате отправления:

Предыдущее
От: Dmitri Maziuk
Дата:
Сообщение: Re: Upgrade/Downgrade
Следующее
От: Paul Tilles
Дата:
Сообщение: extracting the sql for a function