How to raise index points when equal and like is used with gist?
От | Condor |
---|---|
Тема | How to raise index points when equal and like is used with gist? |
Дата | |
Msg-id | bc563b5da91c856e80e7ca72e7836ca3@stz-bg.com обсуждение исходный текст |
Ответы |
Re: How to raise index points when equal and like is used
with gist ?
Re: How to raise index points when equal and like is used with gist? |
Список | pgsql-general |
Hello, I have a problem with query and index scan based on pg_trgm module. Here is few examples: First example is with equal: explain analyze SELECT * FROM table WHERE firstname = 'OLEG' AND middlename || lastname LIKE '%KUZNICOV%IGORU%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on abonats_tbl (cost=34.42..6043.65 rows=1 width=601) (actual time=2.885..14.062 rows=1 loops=1) Recheck Cond: (firstname = 'OLEG'::text) Filter: ((middlename || lastname) ~~ '%KUZNICOV%IGORU%'::text) Rows Removed by Filter: 1731 -> Bitmap Index Scan on table_firstname_idx (cost=0.00..34.42 rows=1690 width=0) (actual time=0.699..0.699 rows=1732 loops=1) Index Cond: (firstname = 'OLEG'::text) Total runtime: 14.126 ms (7 rows) But if I add one like with gist index result is not filtered here is example: explain analyze SELECT * FROM table WHERE phone LIKE '12%' AND firstname = 'OLEG' AND middlename || lastname LIKE '%KUZNICOV%IGORU%'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on abonats_tbl (cost=1638.89..1816.65 rows=1 width=601) (actual time=219.793..219.793 rows=0 loops=1) Recheck Cond: ((firstname = 'OLEG'::text) AND (phone ~~ '12%'::text)) Filter: ((middlename || lastname) ~~ '%KUZNICOV%IGORU%'::text) Rows Removed by Filter: 65 -> BitmapAnd (cost=1638.89..1638.89 rows=45 width=0) (actual time=219.197..219.197 rows=0 loops=1) -> Bitmap Index Scan on table_firstname_idx (cost=0.00..34.42 rows=1690 width=0) (actual time=0.867..0.867 rows=1732 loops=1) Index Cond: (firstname = 'OLEG'::text) -> Bitmap Index Scan on table_phonegist_idx (cost=0.00..1604.22 rows=33995 width=0) (actual time=217.639..217.639 rows=33256 loops=1) Index Cond: (phone ~~ '12%'::text) Total runtime: 220.426 ms My question is: Is there any way how to make postgresql first to search from field that is with equal I have index there and then to filter result based to other conditions first gist and then other. I think may be I should play with index points. Im using postgresql 9.2.1 x86_64 Regards, C.
В списке pgsql-general по дате отправления: