Re: Pattern matching ints

On 26/01/15 20:32, Tim Smith wrote:
> Hi,
>
> Is there a more efficient way to pattern match integer columns other
> than something like :
>
> where cast(mynumber as text) ~ '.*123.*'
>
>
> I also seem to recall you can't create indexes on casts either ?

This is perfectly possible:

postgres=# CREATE TABLE foo (id INT);
CREATE TABLE
postgres=# CREATE INDEX idx ON foo (CAST(id AS TEXT) text_pattern_ops);
CREATE INDEX
postgres=# INSERT INTO foo values(generate_series(1,1000000));
INSERT 0 1000000

but not necessarily useful...

postgres=# explain analyze SELECT * from foo where id::text ~ '.*12345.*';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..21925.00 rows=100 width=4) (actual time=17.331..961.384 rows=20 loops=1)
Filter: ((id)::text ~ '.*12345.*'::text)
Rows Removed by Filter: 999980
Planning time: 0.296 ms
Execution time: 961.411 ms
(5 rows)

However you might find the pg_trgm extension [1] useful:

postgres=# CREATE TABLE foo (id INT);
CREATE TABLE
postgres=# CREATE INDEX trgm_idx ON foo using gist(cast(id as text) gist_trgm_ops);
CREATE INDEX
postgres=# INSERT INTO foo values(generate_series(1,1000000));
INSERT 0 1000000
postgres=# explain analyze SELECT * from foo where id::text ~ '.*12345.*';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=200.47..4938.11 rows=5184 width=4) (actual time=61.163..61.211 rows=20 loops=1)
Recheck Cond: ((id)::text ~ '.*12345.*'::text)
Heap Blocks: exact=11
-> Bitmap Index Scan on trgm_idx (cost=0.00..199.17 rows=5184 width=0) (actual time=61.140..61.140 rows=20 loops=1)
Index Cond: ((id)::text ~ '.*12345.*'::text)
Planning time: 0.241 ms
Execution time: 61.257 ms
(7 rows)

[1] http://www.postgresql.org/docs/current/interactive/pgtrgm.html

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services