Vinay Varma

Explain Analyze

Let's say we have this query [1]

select name, artist, text
from card
where to_tsvector(name || ' ' || artist || ' ' || text) @@ to_tsquery('Avon');

If we want to make this run faster, we would probably think of creating a new column which helps us save some computation time (especially if the number of rows is huge)

So we create a new column like this

ALTER TABLE card
  ADD COLUMN document tsvector;
update card
set document = to_tsvector(name || ' ' || artist || ' ' || text);

Now you can just query the document column [2]

select name, artist, text
from card
where document @@ to_tsquery('Avon');

Now, How do we knw how much improvement does [2] get us over [1]? Enter explain analyze

explain analyze select name, artist, text
                from card
                where document @@ to_tsquery('Avon');
explain analyze select name, artist, text
                from card
                where document_with_idx @@ to_tsquery('Avon');

You will be presented with the time it took to execute both the queries.

Reference: Full Text Search PostgreSQL