Skip to content

Instantly share code, notes, and snippets.

@lettergram

lettergram/tsvector-full-text-search.sql

Last active August 19, 2018 05:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save lettergram/c2526f7ed3aeaa457aa388f7d2ac30ad to your computer and use it in GitHub Desktop.
Save lettergram/c2526f7ed3aeaa457aa388f7d2ac30ad to your computer and use it in GitHub Desktop.
Download ZIP
Super Slow & Reasonably Accurate Full-Text Search in PostgreSQL
Raw
tsvector-full-text-search.sql
EXPLAIN ANALYZE SELECT story_url, count(*) as count, max(updated_at) as updated_at
FROM "comments"
WHERE ("comments"."updated_at" BETWEEN '2018-01-01' AND '2018-07-07')
AND (to_tsvector('english', comment_text) @@ to_tsquery('english', 'google'))
GROUP BY story_url HAVING (count(*) > '7')
ORDER BY count DESC LIMIT 15;
Planning time: 0.276 ms
Execution time: 152242.143 ms
/* WHERE ("comments"."updated_at" BETWEEN '2018-07-01' AND '2018-07-06') */
https://medium.com/@serverpunch/why-you-should-not-use-google-cloud-75ea2aec00de | 94 |
2018-07-01 01:39:39 | 2018-07-01 20:25:09
https://github.com/harababurel/gcsf | 14 | 2018-07-01 04:30:09 | 2018-07-01 19:20:10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment