På torsdag 12. mai 2016 kl. 10:05:01, skrev Andreas Joseph Krogh < andr...@visena.com <mailto: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_clientid_gin ON public.ja_jobs USING gin (title gin_trgm_ops, clientid);
--- insert some test-data As you see, this uses the index (when casting clientid to bigint): andreak=# explain analyze SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%ras du%' and clientid = 12::bigint AND time_job > 257826264 order BY title limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=8.43..8.44 rows=1 width=32) (actual time=0.033..0.034 rows=1 loops=1) -> Unique (cost=8.43..8.44 rows=1 width=32) (actual time=0.032..0.032 rows=1 loops=1) -> Sort (cost=8.43..8.43 rows=1 width=32) (actual time=0.032..0.032 rows=1 loops=1) Sort Key: title Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on ja_jobs (cost=7.20..8.42 rows=1 width=32) (actual time=0.025..0.025 rows=1 loops=1) Recheck Cond: (((title)::text ~~* '%ras du%'::text) AND (clientid = '12'::bigint)) Filter: (time_job > 257826264) Heap Blocks: exact=1 -> Bitmap Index Scan on ix_ja_jobs_trgm_clientid_gin (cost=0.00..7.20 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=1) Index Cond: (((title)::text ~~* '%ras du%'::text) AND (clientid = '12'::bigint)) Planning time: 0.169 ms Execution time: 0.061 ms (13 rows) Forgot to say, this is in PG-9.6 (master), but should work on previous versions. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
