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
