CODEWITHSUNDEEP
postgresqlsearchindexinggo-gin
Oliver
Oliver

Reputation: 1

PostgreSQL full text search stops returning results

I created a GIN index on a table in PostgreSQL database. It was created without issue, see below create index statement. Then I used a select query to search the table, also see below.

create index posts_full_text_search_json_idx on posts using gin (
to_tsvector('english', ((json->>'name')) || ' ' || ((json->>'city')) || ' ' || ((json->>'state')) || ' ' || ((json->>'abbr')) || ' ' || ((json->>'category')) || ' ' || ((json->>'subcategory')))
);

select * from posts where approved=true and (to_tsvector('english', ((json->>'name')) || ' ' || ((json->>'city')) || ' ' || ((json->>'state')) || ' ' || ((json->>'abbr')) || ' ' || ((json->>'category')) || ' ' || ((json->>'subcategory'))) @@ to_tsquery('english', :terms)) limit 200;

:terms is swapped for the search terms via PHP PDO.

This worked right away, no issues, very fast results (about 20-50ms). But, recently it stopped returning results, no matter what I search for, even if there is guaranteed to be results.

Thanks for reading!

I did some Google searching and did a REINDEX of the index and that did not help, I've restarted the PostgreSQL server, rebooted a few times. But the query still returns 0 results. I'm out of solutions. Has anybody run into this before?

Upvotes: 0

Views: 36

Answers (0)

Related Questions