{"id":1910,"date":"2013-09-04T12:47:41","date_gmt":"2013-09-04T12:47:41","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=1910"},"modified":"2013-09-04T12:47:41","modified_gmt":"2013-09-04T12:47:41","slug":"fixing-issues-where-postgres-optimizer-ignores-full-text-indexes","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/fixing-issues-where-postgres-optimizer-ignores-full-text-indexes\/","title":{"rendered":"Fixing Issues Where Postgres Optimizer Ignores Full Text Indexes"},"content":{"rendered":"<p>When I attempted to implement faceted search against a Postgres full-text index, I found I had issues getting Postgres (9.2) to pick up the index:<\/p>\n<pre lang=\"sql\">\nCREATE INDEX search_idx2 ON data2 USING gin(to_tsvector('english', search));\n\nSELECT * \nFROM (\n  SELECT author, count(*) c\n  FROM data2\n  WHERE search @@ to_tsquery('linux')\n  GROUP BY author\n) counts \nORDER BY c desc;\n<\/pre>\n<p>This query will do a full scan on the table (~9-12 minutes on a 1.1 million row table).<\/p>\n<pre>\nSort \n(cost=233587.16..233587.17 rows=6 width=22)\"\n  Sort Key: (count(*))\"\n    HashAggregate  \n    (cost=233586.96..233587.02 rows=6 width=14)\"\n      Seq Scan on data2 \n     (cost=0.00..233578.12 rows=1768 width=14)\"\n        Filter: \n        <sup><a href=\"#footnote_0_1910\" id=\"identifier_0_1910\" class=\"footnote-link footnote-identifier-link\" title=\"search)::text \n         @@ to_tsquery(&#039;linux&#039;::text\">1<\/a><\/sup>\"\n<\/pre>\n<p>The fix is simply to apply the function used on the search column to the query, which cuts the time to ~23 seconds on a 1.1 million row table:<\/p>\n<pre lang=\"sql\">\nSELECT * \nFROM (\n  SELECT author, count(*) c\n  FROM data2\n  WHERE to_tsvector('english', search) @@ to_tsquery('linux')\n  GROUP BY author\n) counts \nORDER BY c desc;\n<\/pre>\n<p>And the resulting plan: <\/p>\n<pre>\nHashAggregate  \n  (cost=74668.49..74669.28 rows=79 width=14) \n  (actual time=23726.071..23726.598 rows=1625 loops=1)\n  Bitmap Heap Scan on data2  \n    (cost=306.87..74535.03 rows=26692 width=14) \n    (actual time=15.051..23683.230 rows=27604 loops=1)\n\n    Recheck Cond: (to_tsvector('english'::regconfig, (search)::text)\n                  @@ to_tsquery('linux'::text))\n    Rows Removed by Index Recheck: 42867\n      Bitmap Index Scan on search_idx2  \n      (cost=0.00..300.19 rows=26692 width=0) \n      (actual time=11.436..11.436 rows=27604 loops=1)\n        Index Cond: (to_tsvector('english'::regconfig, (search)::text) \n                    @@ to_tsquery('linux'::text))\nTotal runtime: 23727.012 ms\n<\/pre>\n<ol class=\"footnotes\"><li id=\"footnote_0_1910\" class=\"footnote\">search)::text \n         @@ to_tsquery('linux'::text<span class=\"footnote-back-link-wrapper\"> [<a href=\"#identifier_0_1910\" class=\"footnote-link footnote-back-link\">&#8617;<\/a>]<\/span><\/li><\/ol>","protected":false},"excerpt":{"rendered":"<p>When I attempted to implement faceted search against a Postgres full-text index, I found I had issues getting Postgres (9.2) to pick up the index: CREATE INDEX search_idx2 ON data2 USING gin(to_tsvector(&#8216;english&#8217;, search)); SELECT * FROM ( SELECT author, count(*) c FROM data2 WHERE search @@ to_tsquery(&#8216;linux&#8217;) GROUP BY author ) counts ORDER BY c &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.garysieling.com\/blog\/fixing-issues-where-postgres-optimizer-ignores-full-text-indexes\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Fixing Issues Where Postgres Optimizer Ignores Full Text Indexes&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[7],"tags":[242,437,523],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/1910"}],"collection":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/comments?post=1910"}],"version-history":[{"count":0,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/1910\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/media?parent=1910"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/categories?post=1910"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/tags?post=1910"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}