Обсуждение: Is my text_pattern_ops index working for a LIKE prefix search withcolumn reference?
Is my text_pattern_ops index working for a LIKE prefix search withcolumn reference?
От
Seamus Abshere
Дата:
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/
Re: Is my text_pattern_ops index working for a LIKE prefix searchwith column reference?
От
Laurenz Albe
Дата:
Seamus Abshere wrote: > I created an index with text_pattern_ops because I want fast prefix search [1] [2]. > > ...but I don't think it's working when I use a column reference || '%'... An index can only be used for expressions like <indexed expression> <operator from the opclass> <constant> and you have a column refrence on the right side. Perhaps you can rephrase your query in such a fashion. Yours, Laurenz Albe
Laurenz Albe <laurenz.albe@cybertec.at> writes: > Seamus Abshere wrote: >> I created an index with text_pattern_ops because I want fast prefix search [1] [2]. >> ...but I don't think it's working when I use a column reference || '%'... > An index can only be used for expressions like > <indexed expression> <operator from the opclass> <constant> > and you have a column refrence on the right side. "constant" is a bit too strong here, but it definitely can't be "expression using another column from the same table". It has to be an expression that will hold stable throughout a scan of the table you wish to index. regards, tom lane