Re: [bulk] Re: [bulk] Re: Problem with LIKE-Performance

Hi Richard,

RH> As you can see, the plan is still scanning all the rows. In any case,
RH> you've changed the query - this has % at the beginning and end, which no
RH> index will help you with.

I realize that, the index definately helped a lot with the query where
the % is just at the end. The time went down to 0.203 ms after I
changed the index to varchar_pattern_ops.

Index Scan using idx_image_title on image  (cost=0.00..6.01 rows=1 width=1311) (actual time=0.027..0.108 rows=33
loops=1)
Index Cond: (((title)::text ~>=~ 'Davorka'::character varying) AND ((title)::text ~<~ 'Davorkb'::character varying))
Filter: ((title)::text ~~ 'Davorka%'::text)
Total runtime: 0.203 ms

Although 13 sec. for the first select seems a bit odd, I think after
the Database-Cache on the Table kicks in, it should be fine with ~500 ms

Best regards
Manuel