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

Started by Seamus Abshereover 7 years ago3 messagesgeneral
Jump to latest
#1Seamus Abshere
seamus@abshere.net

hi,

I created an index with text_pattern_ops because I want fast prefix search [1]http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html [2]https://blog.2ndquadrant.com/text-search-strategies-in-postgresql/.

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 appears in 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/

#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Seamus Abshere (#1)
Re: Is my text_pattern_ops index working for a LIKE prefix search with column reference?

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Laurenz Albe (#2)
Re: Is my text_pattern_ops index working for a LIKE prefix search with column reference?

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