Até o ponto que conheço, ILIKE ignora indices.. tente fazer com LIKE.. Att., Renato
2016-05-12 23:43 GMT-03:00 Lucas Possamai <[email protected]>: > Olá galera, > > Possuo a seguinte query: > > explain analyze >> SELECT DISTINCT title >> FROM ja_jobs WHERE title ILIKE '%Hisl 035%' >> and clientid = 2565 AND time_job > 1382496599 >> order BY title >> limit 10 > > > Explain analyze: > > Limit (cost=5946.40..5946.41 rows=1 width=19) (actual >> time=2746.759..2746.772 rows=1 loops=1) >> -> Unique (cost=5946.40..5946.41 rows=1 width=19) (actual >> time=2746.753..2746.763 rows=1 loops=1) >> -> Sort (cost=5946.40..5946.41 rows=1 width=19) (actual >> time=2746.750..2746.754 rows=4 loops=1) >> Sort Key: "title" >> Sort Method: quicksort Memory: 25kB >> -> Bitmap Heap Scan on "ja_jobs" (cost=49.02..5946.39 >> rows=1 width=19) (actual time=576.275..2746.609 rows=4 loops=1) >> Recheck Cond: (("clientid" = 2565) AND ("time_job" > >> 1382496599)) >> Filter: (("title")::"text" ~~* '%Hisl 035%'::"text") >> Rows Removed by Filter: 791 >> -> Bitmap Index Scan on "ix_jobs_client_times" >> (cost=0.00..49.02 rows=1546 width=0) (actual time=100.870..100.870 >> rows=795 loops=1) >> Index Cond: (("clientid" = 2565) AND >> ("time_job" > 1382496599)) >> Total runtime: 2746.879 ms > > > Uma simples query causando todo esse runtime... > > Então, dando uma pesquisada acabei criando um GIN index, para tentar > melhorar a performance e também mudando algumas coisas na Query: > > Nova query: (Removido o DISTINCT e removido também o % da esquerda) > >> explain (analyze, buffers) >> SELECT title >> FROM ja_jobs WHERE title ILIKE 'Hisl 035%' >> and clientid = 2565 AND time_job > 1382496599 >> order BY title >> limit 10 > > > Index: > > CREATE INDEX CONCURRENTLY ix_jobs_trgm_gin ON public.ja_jobs USING >> gin(title gin_trgm_ops); > > > Explain analyze DEPOIS do index: (Acabei ativando o track_io_timing) > > Limit (cost=2275.53..2275.55 rows=9 width=20) (actual >> time=3492.479..3492.483 rows=1 loops=1) >> Buffers: shared hit=4940 read=448 >> I/O Timings: read=83.285 >> -> Sort (cost=2275.53..2275.55 rows=9 width=20) (actual >> time=3492.475..3492.477 rows=1 loops=1) >> Sort Key: "title" >> Sort Method: quicksort Memory: 25kB >> Buffers: shared hit=4940 read=448 >> I/O Timings: read=83.285 >> -> Bitmap Heap Scan on "ja_jobs" (cost=391.62..2275.38 rows=9 >> width=20) (actual time=3492.460..3492.462 rows=1 loops=1) >> Recheck Cond: (("title")::"text" ~~* 'Hisl 035%'::"text") >> Filter: (("time_job" > 1382496599) AND ("clientid" = 2565)) >> Buffers: shared hit=4940 read=448 >> I/O Timings: read=83.285 >> -> Bitmap Index Scan on "ix_jobs_trgm_gin" >> (cost=0.00..391.62 rows=482 width=0) (actual time=3492.427..3492.427 >> rows=1 loops=1) >> Index Cond: (("title")::"text" ~~* 'Hisl 035 >> %'::"text") >> Buffers: shared hit=4939 read=448 >> I/O Timings: read=83.285 >> Total runtime: 3492.531 ms > > > Como puderam ver.. o index não ajudou em nada.. na verdade piorou. > > Não sei oq posso fazer a mais para melhorar a performance. > Vendo os passos aqui [1] e aqui [2], para o pessoal o index teve uma > significante melhoria na performance. Mas pra mim não. > > > *Vocês sabem o que mais eu poderia fazer?* > > > [1] https://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/ > [2] > http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations > > Obrigado > Lucas > > _______________________________________________ > pgbr-geral mailing list > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral >
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
