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 ?  (Sergey Konoplev <gray.ru@gmail.com>)
Re: How to raise index points when equal and like is used with gist?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Vineet Deodhar
Дата:
Сообщение: Re: auto-increment field : in a simple way
Следующее
От: JC de Villa
Дата:
Сообщение: Re: auto-increment field : in a simple way