Postgresql effectual select one word from string column

84 Views Asked by At

I have problem with select from big table like this:

ID | stringColumn
1  | asd, www, some, ect
2  | lol, dbr, aha, itp

I need to check if the stringColumn contains specific word. For example: search for word 'www' give me ID 1

My all solutions was suboptimal. I was waiting 30s for result.

[EDIT] Query:

SELECT "TABLE"."ID" FROM "TABLE" 
WHERE 
string_to_array("TABLE"."stringColumn", ', ')&& 
string_to_array('123, asdasd, 12345, 145, 1456', ', ');

Explain analyze:

Hash Join  (cost=0.28..126.47 rows=2 width=4)(actual time=0.132..3.553rows=88 loops=1)"
Hash Cond: ((unnest(string_to_array("TABLE"."stringColumn", ', '::text))) = (unnest('{123,12345,145,1456}'::text[])))"
 ->  Seq Scan on "TABLE"  (cost=0.00..120.19 rows=435 width=32) 
(actual time=0.074..3.341 rows=916 loops=1)
  ->  Hash  (cost=0.27..0.27 rows=1 width=32) 
(actual time=0.040..0.040 rows=4 loops=1)"
        ->  Result  (cost=0.00..0.26 rows=1 width=0) 
(actual time=0.034..0.038 rows=4 loops=1)"
Total runtime: 3.576 ms

Second query:

SELECT  "TABLE"."ID" FROM "TABLE", 
(SELECT unnest( string_to_array('123,asdasd, 12345, 145, 1456', ', ') ) as t) 
as tab1
WHERE "TABLE"."stringColumn" LIKE '% '||tab1.t||',%';

Explain analyze:

Nested Loop  (cost=0.00..18.23 rows=2 width=4) 
(actual time=0.739..1.633 rows=13 loops=1)"
  Join Filter: ("TABLE"."stringColumn" ~~ (('% '::text || (unnest('{123,asdasd,12345,145,1456}'::text[]))) || ',%'::text))
  ->  Result  (cost=0.00..0.26 rows=1 width=0) 
(actual time=0.103..0.112 rows=5 loops=1)
  ->  Seq Scan on "TABLE"  (cost=0.00..10.35 rows=435 width=32) 
(actual time=0.005..0.066 rows=436 loops=5)
Total runtime: 1.666 ms
0

There are 0 best solutions below