Postgresql - full-text ordering of sorted out set

Question

I have a Postgresql table with something like 200k tuples, so not that much. What I try to do is filter out some rows and then order them using full-text matching:

SELECT * FROM descriptions as d 
WHERE d.category_id = ? 
AND d.description != '' 
AND regexp_replace(d.description, '(...)', '') !~* '...' 
AND regexp_replace(d.description, '...', '') !~* '...' 
AND d.id != ? 
ORDER BY ts_rank_cd(to_tsvector('english', name), plainto_tsquery('english', 'my search words')) DESC LIMIT 5 OFFSET 0';

There is a GIN index on description field.

Now this query works well only when there is less then 4000 or so records in the category. When its more like 5k or 6k then the query gets extremely slow.

I was trying different variations of this query. What I noticed is when I remove either WHERE clause or ORDER BY clause then I get big speed up. (Of course then I get irrelevant results)

What can I do to speedup this combination? Any way of optimizing or should I look for a solution outside Postgresql?

Additional question:

I'm experimenting further and for example this is the simplest query that I think runs too slow. Can I tell from explain analyze when it uses gist index and when doesn't?

SELECT d.*, d.description <-> 'banana' as dist FROM descriptions as d ORDER BY dist DESC LIMIT 5

"Limit  (cost=16046.88..16046.89 rows=5 width=2425) (actual time=998.811..998.813 rows=5 loops=1)"
"  ->  Sort  (cost=16046.88..16561.90 rows=206010 width=2425) (actual time=998.810..998.810 rows=5 loops=1)"
"        Sort Key: (((description)::text <-> 'banana'::text))"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Seq Scan on products d  (cost=0.00..12625.12 rows=206010 width=2425) (actual time=0.033..901.260 rows=206010 loops=1)"
"Total runtime: 998.866 ms"`

Answered (kgrittn): DESC keyword is not correct for KNN-GiST and it's actually not wanted here. Removing it fixes the problem and gives right results.

Answer 1

For this type of application, we have been moving from the tsearch feature to the trigram feature; when you want to pick a small number of best matches, it is much faster. People here often prefer the semantics of the trigram similarity matching over the text-search ranking, anyway.

http://www.postgresql.org/docs/current/interactive/pgtrgm.html

"Borrowing" the later query from the edited question, formatting it, and including the index creation statement, to make the answer self-contained without a raft of comments:

CREATE INDEX descriptions_description_trgm
  ON descriptions
  USING gist (description gist_trgm_ops);

SELECT d.*, d.description <-> 'banana' as dist
  FROM descriptions as d
  ORDER BY dist LIMIT 5;

This should return rows from the GiST index in "distance" sequence until it hits the LIMIT.

Answer 2

An output of explain analyze of your query would be helpful. But I guess that this regexp_replace lines are your problem. Postgres planner just cannot know how many rows will match this two lines, so it is guessing and planning a query based on this flawed quess.

I'd recommend to create a function like this:

create function good_description(text) returns boolean as $$
  select
   regexp_replace($1, '(...)', '') !~* '...' 
   and
   regexp_replace($1, '...', '') !~* '...'
$$ language sql immutable strict;

And creating a partial index on expression using this function:

create index descriptions_good_description_idx
  on good_description(description)
  where description != '';

And then querying in a way that allows Postgres to use this index:

SELECT * FROM descriptions as d 
WHERE d.category_id = ? 
AND d.description != '' 
AND good_description(d.description)
AND d.id != ? 
ORDER BY ts_rank_cd(
  to_tsvector('english', name),
  plainto_tsquery('english', 'my search words')
) DESC
LIMIT 5 OFFSET 0;