1

PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

I have a table named main_email with column 'sender', type character varying(150), not nullable.

I created both gin and gist trgm indexes:

create index test_gin_sender_idx on main_email using gin (sender gin_trgm_ops);
create index test_gist_sender_idx on main_email using gist (sender gist_trgm_ops);

On my smaller test database that has 17,219 rows in this table the index is used:

explain analyze select * from main_email where sender like '%andrew%';

QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on main_email  (cost=4.32..27.56 rows=6 width=1434) 
(actual time=1.593..4.032 rows=4327 loops=1)
   Recheck Cond: ((sender)::text ~~ '%andrew%'::text)
   Heap Blocks: exact=1197
   ->  Bitmap Index Scan on test_gist_sender_idx  (cost=0.00..4.32 rows=6 width=0) (actual time=1.363..1.363 rows=4327 loops=1)
         Index Cond: ((sender)::text ~~ '%andrew%'::text)
 Planning time: 0.167 ms
 Execution time: 4.449 ms

On the larger production database with ~1.5 million rows the index is not used and instead I get a parallel seq scan:

explain analyze select * from main_email where sender like '%andrew%';
QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..194958.39 rows=4672 width=853) (actual time=0.310..244.993 rows=4396 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on main_email  (cost=0.00..193491.19 rows=1947 width=853) (actual time=0.049..239.576 rows=1465 loops=3)
         Filter: ((sender)::text ~~ '%andrew%'::text)
         Rows Removed by Filter: 514074
 Planning time: 0.153 ms
 Execution time: 247.316 ms

Why is the planner choosing to do a sequence scan instead of using the index? Is it really faster to choose the sequence scan? If so, why and is there any way to further improve the performance of this query?

  • Are table statistics updated? Try ANALYZE main_email and re-run the explain. – Diego Henrique Sep 27 '18 at 12:05
  • You can probably force it to use the index by doing set enable_seqscan=off. Then you can see 1) if it actually is faster, and if so 2) why the EXPLAIN ANALYZE thought it would be slower. Please post the EXPLAIN ANALYZE – jjanes Sep 29 '18 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.