Skip to content

Instantly share code, notes, and snippets.

@mariusk
Created December 12, 2012 11:28
Show Gist options
  • Save mariusk/4267093 to your computer and use it in GitHub Desktop.
Save mariusk/4267093 to your computer and use it in GitHub Desktop.
Postgresql resorting to scans for single term full text searches, not sure why...
alter function to_tsvector(regconfig,text) cost 1000;
set enable_indexscan=false;
explain select * from proxyrec where to_tsvector('english', cs_ua) @@ to_tsquery('mozilla') order by ts limit 1
QUERY PLAN
----------
Limit (cost=347040.40..347040.40 rows=1 width=1052)
-> Sort (cost=347040.40..347178.66 rows=55302 width=1052)
Sort Key: ts
-> Bitmap Heap Scan on proxyrec (cost=603.09..346763.89 rows=55302 width=1052)
Recheck Cond: (to_tsvector('english'::regconfig, (cs_ua)::text) @@ to_tsquery('mozilla & perl'::text))
-> Bitmap Index Scan on proxyrec_text_ua (cost=0.00..589.27 rows=55302 width=0)
Index Cond: (to_tsvector('english'::regconfig, (cs_ua)::text) @@ to_tsquery('mozilla & perl'::text))
set enable_indexscan=true;
explain select * from proxyrec where to_tsvector('english', cs_ua) @@ to_tsquery('mozilla') order by ts limit 1
QUERY PLAN
----------
Limit (cost=0.00..11511.21 rows=1 width=1051)
-> Index Scan using proxrec_tssip on proxyrec (cost=0.00..636592669.35 rows=55302 width=1052)
Filter: (to_tsvector('english'::regconfig, (cs_ua)::text) @@ to_tsquery('mozilla'::text)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment