Skip to content

Instantly share code, notes, and snippets.

@lettergram

lettergram/fast-accurate-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/ff64900e8b73dc391f8beae6dda38372 to your computer and use it in GitHub Desktop.
Save lettergram/ff64900e8b73dc391f8beae6dda38372 to your computer and use it in GitHub Desktop.
Download ZIP
Fast & Accurate Full-Text Search
Raw
fast-accurate-full-text-search.sql
SELECT story_url, count(*) as count, max(updated_at) as updated_at
FROM comments, plainto_tsquery('google') AS q
WHERE (tsv_comment_text @@ q) AND created_at > '2018-01-01' AND created_at < '2018-07-12'
GROUP BY story_url HAVING (count(*) > '7') ORDER BY count DESC LIMIT 15;
Planning time: 0.264 ms
Execution time: 2661.027 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 | 72 |
2018-06-30 19:05:12 | 2018-07-01 20:25:09
https://www.nytimes.com/2018/06/29/technology/computer-networks-speed-nasdaq.html | 71 |
2018-07-01 11:50:09 | 2018-07-01 22:03:59
https://github.com/harababurel/gcsf | 49 | 2018-07-01 02:35:09 | 2018-07-01 21:15:09
https://torrentfreak.com/google-downranks-65000-pirate-sites-in-search-results-180629/ | 17 |
2018-06-30 19:30:08 | 2018-07-01 12:55:09
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment