Re: How to raise index points when equal and like is usedwith gist ? - Mailing list pgsql-general

On 2012-10-12 11:30, Sergey Konoplev wrote:
> On Fri, Oct 12, 2012 at 1:20 AM, Condor <condor@stz-bg.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