On 2012-10-12 11:30, Sergey Konoplev wrote:
On Fri, Oct 12, 2012 at 1:20 AM, Condor <condor@xxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
