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.
Limit (cost=418.57..418.58 rows=1 width=20) (actual time=4439.367..4439.381 rows=1 loops=1) Buffers: shared hit=6847 -> Unique (cost=418.57..418.58 rows=1 width=20) (actual time=4439.363..4439.374 rows=1 loops=1) Buffers: shared hit=6847 -> Sort (cost=418.57..418.58 rows=1 width=20) (actual time=4439.360..4439.365 rows=4 loops=1) Sort Key: "title" Sort Method: quicksort Memory: 25kB Buffers: shared hit=6847 -> Bitmap Heap Scan on "ja_jobs" (cost=414.55..418.56 rows=1 width=20) (actual time=4439.312..4439.329 rows=4 loops=1) Recheck Cond: (("clientid" = 31239) AND ("time_job" > 1457826264) AND (("title")::"text" ~~* 'RYAN SHOWER%'::"text")) Buffers: shared hit=6847 -> BitmapAnd (cost=414.55..414.55 rows=1 width=0) (actual time=4439.280..4439.280 rows=0 loops=1) Buffers: shared hit=6843 -> Bitmap Index Scan on "ix_jobs_client_times" (cost=0.00..50.67 rows=1711 width=0) (actual time=0.142..0.142 rows=795 loops=1) Index Cond: (("clientid" = 31239) AND ("time_job" > 1457826264)) Buffers: shared hit=8 -> Bitmap Index Scan on "ix_ja_jobs_trgm_gin" (cost=0.00..363.62 rows=483 width=0) (actual time=4439.014..4439.014 rows=32 loops=1) Index Cond: (("title")::"text" ~~* 'RYAN SHOWER%'::"text") Buffers: shared hit=6835Total runtime: 4439.427 ms
pgsql-general by date:
Соглашаюсь с условиями обработки персональных данных
