View single-metacortex-api-sample.sh
{
"author": <Required string>,
"source": <Required string>,
"comment_text": <Required string>,
"comment_link": <Required string>,
"content_title": <string>,
"content_link": <string>,
"discussion_link": <string>,
"context_link": <string>,
"created_at": <datetime>,
View response.sh
{
"author": "lettergram",
"source": "Hacker News",
"comment_text": "This is a stupid, irrelevant example",
"comment_link": "http://example.com",
"sentiment": "Negative",
"score": -0.763,
"key_words": "example",
"created_at": "2018-06-27 22:10:38"
}
View example-curl.sh
curl https://meacortex-server.com/comments \
-H "Content-type: application/json" \
-H "X-Auth-Token: token" \
-d '{ "author": "lettergram", \
"source": "Hacker News", \
"comment_text": "This is a stupid, irrelevant example", \
"comment_link": "http://example.com" }'\
-X POST -v
View trigger-function-on-update.sql
CREATE FUNCTION comment_text_search_trigger() RETURNS trigger AS $$
begin
new.tsv_comment_text :=
setweight(to_tsvector(coalesce(new.story_title,'')), 'A') ||
setweight(to_tsvector(coalesce(new.comment_text,'')), 'B');
return new;
end
$$ LANGUAGE plpgsql;
/* Trigger on update */
View weight-comments.sql
UPDATE comments SET tsv_comment_text = setweight(to_tsvector(coalesce(story_title,'')), 'A')
|| setweight(to_tsvector(coalesce(comment_text,'')), 'B');
View alter-table-add-index.sqp
ALTER TABLE comments ADD COLUMN tsv_comment_text tsvector;
CREATE INDEX tsv_comment_text_idx ON comments USING gin(tsv_comment_text);
View fast-accurate-full-text-search.sql
SELECT story_url, count(*) as count,
min(updated_at) as created_at, 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') */
View tsvector-full-text-search.sql
EXPLAIN ANALYZE SELECT story_url, count(*) as count, min(updated_at) as created_at,
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
View comments_table.sql
id | integer |
story_title | character varying |
story_link | character varying |
author | character varying |
source | character varying |
comment_text | text |
comment_link | character varying |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
View slow-semi-accurate-full-text-search.sql
SELECT story_url, count(*) as count,
min(updated_at) as created_at, max(updated_at) as updated_at FROM "comments"
WHERE ("comments"."updated_at" BETWEEN '2018-01-01' AND '2018-07-06')
AND (comment_text LIKE '%google%')
GROUP BY story_url, story_title HAVING (count(*) > '7')
ORDER BY count DESC LIMIT 15;
Planning time: 0.472 ms
Execution time: 42839.333 ms