0

I have to perform a join between two table using ts_vectors.

The "queries" table contains 2,000 records and reports > 10,000 records. Performing this query below takes 14 seconds on real data.

SELECT *
FROM queries 
LEFT JOIN reports ON (
         reports.query_tsvector           = queries.query_tsvector
    OR  reports.keyword_text_tsvector = queries.query_tsvector
)

Optimizations

Instead of this

LEFT JOIN reports ON (
      strip(to_tsvector(reports.query)        = strip(to_tsvector(queries.query)
   OR strip(to_tsvector(reports.keyword_text) = strip(to_tsvector(queries.query) 
)

I have created two tsvector columns and index them. But it's not enough.

2
  • 1
    Indexes on each tsvector column of the reports table work fine for me, giving me an efficient BitmapOr plan. Maybe you just did it wrong, you haven't provided enough information for us to know.
    – jjanes
    Nov 6, 2022 at 16:22
  • 14 seconds is way too expensive for just 2000 -> 10000 rows. Show required details to pin down what's going wrong. See: dba.stackexchange.com/tags/postgresql-performance/info Do you actually need SELECT *? (Joining on tsvector = tsvector is odd to begin with.) Nov 7, 2022 at 5:13

1 Answer 1

0

The OR forces PostgreSQL to perform a nested loop join.

Rewrite it as a UNION:

SELECT *
FROM queries
   LEFT JOIN reports
      ON reports.query_tsvector = queries.query_tsvector
UNION
SELECT *
FROM queries
   LEFT JOIN reports
      ON reports.keyword_text_tsvector = queries.query_tsvector

Perhaps that won't make a lot of difference (if the comparison between the tsvectors is the dominant cost), but at least it gives PostgreSQL the chance to use a hash join.

I guess this is an experiment, since the query doesn't seem to make a lot of sense.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.