Re: How to raise index points when equal and like is used with gist ?

On 2012-10-12 11:30, Sergey Konoplev wrote:
> On Fri, Oct 12, 2012 at 1:20 AM, Condor <condor(at)stz-bg(dot)com> wrote:
>> Even without tel filed result and type of scan is the same (Seq
>> Scan).
>
> This is because your table has to few rows and it is easier to seq
> scan. Add more rows, eg. 100 000, then ANALYZE the table and run
> tests. Use random() and generate_series() to generate the data.
>

You was right,
when I read documentation of pg_trgm I see how much time will take to
search in 100 000 rows, but I was misled myself because did not expect
to change the search scan. Seq to Bitmap. I understand my mistake and
change
query to:

EXPLAIN ANALYZE WITH AS ab (SELECT * FROM tables WHERE firstname =
'OLEG' AND middlename || lastname LIKE '%KUZNICOV%IGORU%')
SELECT * FROM ab WHERE tel LIKE '12%';

CTE Scan on ab (cost=6490.15..6531.14 rows=9 width=965) (actual
time=2.256..20.017 rows=43 loops=1)
Filter: (tel ~~ '12%'::text)
Rows Removed by Filter: 1690
CTE ab
-> Bitmap Heap Scan on tables (cost=39.87..6490.15 rows=1822
width=600) (actual time=1.789..17.817 rows=1733 loops=1)
Recheck Cond: (firstname = 'OLEG'::text)
Filter: ((middlename || lastname) ~~
'%KUZNICOV%IGORU%'::text)
-> Bitmap Index Scan on tables_firstname_idx
(cost=0.00..39.42 rows=1823 width=0) (actual time=1.178..1.178 rows=1733
loops=1)
Index Cond: (firstname = 'OLEG'::text)
Total runtime: 20.278 ms

Now is much better 20 ms vs 220 ms.

Thanks for your help.

Cheers,
C