Postgresql full-text search with other conditions

Question

I have table with tsvector column, also I create index for that column. Also I have some columns(and indexes for those columns) which required for search.

For example: Table: Vacancies
Columns: title, description(title + description = tsvector), salary(int value with index).

I want find all vacancies with text which contains in title+description and also by salary > 1000.

What column will be the first condition.

SELECT * FROM vacancies WHERE title_description_tsvector @@ plainto_tsquery('php developer') AND salary > 1000;

OR

SELECT * FROM vacancies WHERE salary > 1000 AND title_description_tsvector @@ plainto_tsquery('php developer');

The order of the conditions combined with AND is irrelevant. Both queries will return the same thing. — user330315
– user330315, Commented Nov 3, 2016 at 22:06
No, not even for the index usage it will matter. Check the execution plan using explain analyze select .... and you will see — user330315
– user330315, Commented Nov 4, 2016 at 7:17

Vao Tsun · Accepted Answer · 2016-11-04 08:59:11Z

Changing the order in WHERE won't affect optimizer on its decision.

If for some reason you want to force it take one index carnality over the other (eg, you checked plans and see it uses salary index and then Filter: (title_description_tsvector @@ plainto_tsquery('php developer')) so you don't see your index on title_description_tsvector and you feel the optimizer is wrong). So you want to make it first chose wanted index (like with hints in Oracle), you can use CTE to try it, like:

EXPLAIN WITH pre as (SELECT * FROM vacancies WHERE title_description_tsvector @@ plainto_tsquery('php developer') ) SELECT * FROM pre WHERE salary > 1000;

I'm quite sure the cost will be higher though. If not - time to check autovacuum

Collectives™ on Stack Overflow

Postgresql full-text search with other conditions

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related