Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-23 Thread Lucas Possamai
Hi there, The problem was solved by using lowercase *New index:* > CREATE INDEX CONCURRENTLY ON public.ja_jobs (clientid, lower(title) > varchar_pattern_ops, time_job); *New query:* > SELECT DISTINCT title > FROM public.ja_jobs WHERE lower(title) LIKE lower('RYAN > WER') >

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-14 Thread Jeff Janes
On Thu, May 12, 2016 at 2:32 PM, Lucas Possamai wrote: >> -> Bitmap Index Scan on "ix_jobs_trgm_gin" >> (cost=0.00..335.64 rows=485 width=0) (actual time=3883.886..3883.886 rows=32 >> loops=1) >> Index Cond: (("title")::"text" ~~* '%RYAN >> WER%'::"t

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-12 Thread Lucas Possamai
> > With those sizes, the gin index will probably be naturally kept mostly > in the file-system cache, if it is used regularly. So the original > slowness of your first query is likely just a cold-cache problem. Can > you generate a stream of realistic queries and see what it stabilizes > at? > >

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-12 Thread Jeff Janes
On Wed, May 11, 2016 at 11:59 PM, Lucas Possamai wrote: > >> >> How big is the table? The gin index? shared_buffers? RAM? What >> kind of IO system do you have, and how many other things were going on >> with it? > > > - Just a reminder that I'm not running these tests on my prod server.. I'm

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-12 Thread Andreas Joseph Krogh
På torsdag 12. mai 2016 kl. 10:05:01, skrev Andreas Joseph Krogh < andr...@visena.com >: [snp] I created this test:   create table ja_jobs(id bigserial primary key, title varchar not null, clientid bigint not null, time_job bigint not null); CREATE INDEX ix_ja_jobs_trgm_

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-12 Thread Andreas Joseph Krogh
På torsdag 12. mai 2016 kl. 09:57:58, skrev Andreas Joseph Krogh < andr...@visena.com >: På torsdag 12. mai 2016 kl. 02:30:33, skrev Lucas Possamai < drum.lu...@gmail.com >: Hi there!   I've got a simple but slow query:    SELECT DISTINCT titl

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-12 Thread Andreas Joseph Krogh
På torsdag 12. mai 2016 kl. 02:30:33, skrev Lucas Possamai mailto:drum.lu...@gmail.com>>: Hi there!   I've got a simple but slow query:    SELECT DISTINCT title   FROM ja_jobs WHERE title ILIKE '%RYAN WER%' and clientid = 31239  AND time_job > 1457826264 order BY title limit 10   Explain analy

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-12 Thread Lucas Possamai
> How big is the table? The gin index? shared_buffers? RAM? What > kind of IO system do you have, and how many other things were going on > with it? > - Just a reminder that I'm not running these tests on my prod server.. I'm running on my test server. So the confs will be different The table

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-11 Thread Jeff Janes
On Wed, May 11, 2016 at 5:30 PM, Lucas Possamai wrote: > Hi there! > > I've got a simple but slow query: > >> SELECT DISTINCT title >> FROM ja_jobs WHERE title ILIKE '%RYAN WER%' >> and clientid = 31239 AND time_job > 1457826264 >> order BY title >> limit 10 > > CREATE INDEX ix_ja_jobs_trgm_gi

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-11 Thread Jan de Visser
On Wed, May 11, 2016 at 10:03 PM, Lucas Possamai wrote: > >>> >> Trying redoing the query with CTE as below: >> >> WITH ja_jobs as >> (SELECT DISTINCT title >> FROM ja_jobs >> WHERE clientid = 31239 AND time_job > 1457826264 >> ) >> SELECT title >> FROM ja_jobs >> WHERE title ILI

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-11 Thread Lucas Possamai
> > >> > Trying redoing the query with CTE as below: > > WITH ja_jobs as > (SELECT DISTINCT title > FROM ja_jobs > WHERE clientid = 31239 AND time_job > 1457826264 > ) > SELECT title > FROM ja_jobs > WHERE title ILIKE 'RYAN WER%' > ORDER BY title > LIMIT 10; > hmm.. still slow =

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-11 Thread Melvin Davidson
On Wed, May 11, 2016 at 9:36 PM, Lucas Possamai wrote: > >>> >> The main problem is WHERE title ILIKE '%RYAN WER%' >> When you put a % on the left of the text, there is no way to optimize >> that, so yes, it will be slow. >> >> If you can eliminate the leading percent and just have trailing, it w

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-11 Thread Lucas Possamai
> > >> > The main problem is WHERE title ILIKE '%RYAN WER%' > When you put a % on the left of the text, there is no way to optimize > that, so yes, it will be slow. > > If you can eliminate the leading percent and just have trailing, it will > be much faster. > > Hmm.. yep.. I suppose I can do tha

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-11 Thread Melvin Davidson
On Wed, May 11, 2016 at 8:30 PM, Lucas Possamai wrote: > Hi there! > > I've got a simple but slow query: > > SELECT DISTINCT title >> FROM ja_jobs WHERE title ILIKE '%RYAN WER%' >> and clientid = 31239 AND time_job > 1457826264 >> order BY title >> limit 10 > > > Explain analyze: > > Limit (co

[GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

2016-05-11 Thread Lucas Possamai
Hi there! I've got a simple but slow query: SELECT DISTINCT title > FROM ja_jobs WHERE title ILIKE '%RYAN WER%' > and clientid = 31239 AND time_job > 1457826264 > order BY title > limit 10 Explain analyze: Limit (cost=5946.40..5946.41 rows=1 width=19) (actual > time=2746.759..2746.772 rows=