2

I have a large postgresql table (T) that has a small varchar(50) column (C), and there's a btree index on C

neither of the following use the index, and both take a considerate amount of time:

select * from T where C like 'myprefix%'
select * from T where C ~ '^myprefix'

how can I make such queries faster? Am I doing anything wrong? Is there some other kind of extension/index that will help? My pattern are always the prefix (anchored to the left)

AmirGh
  • 65
  • 9
  • 1
    How did you create the index? Can you provide the `CREATE INDEX` command you used? – Nick Jun 13 '16 at 22:42
  • 2
    Check out the answer here: http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations – Nick Jun 13 '16 at 22:45
  • 1
    I created the index using `create index iname on T(C)`, from the link you posted it seems I have to drop it and use `create index iname on T(c varchar_pattern_op)`, is that correct? – AmirGh Jun 13 '16 at 22:51
  • Test it out for yourself. – Nick Jun 13 '16 at 22:53
  • I guess, it is already answered [HERE](https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations) . – bevan Jun 29 '18 at 11:36

0 Answers0