Re: How to enable partial matching on a GIN index

>What's your grounds for claiming that?

It returns no results whenever I use partial search terms. If I search for,
say, "hospital" it returns results containing the exact word "hospital",
but if I search for "hosp" it returns nothing. The doc page explaining that
PG "can" do partial matches, and not that it "does" do partial matches, led
me to believe this is the expected default behaviour. Is this not the case?

Here's code to reproduce the problem:

ALTER TABLE mytable ADD COLUMN search_index tsvector;
CREATE INDEX mytable_search_index_gin ON mytable USING gin(search_index);

INSERT INTO mytable (name, search_index) VALUES ('hospital',
plainto_tsquery('pg_catalog.english', 'hospital'));

SELECT * FROM mytable WHERE (search_index) @@
(plainto_tsquery('pg_catalog.english', 'hospital')); -- returns results
SELECT * FROM mytable WHERE (search_index) @@
(plainto_tsquery('pg_catalog.english', 'hosp')); -- returns nothing

Am I using the index correctly?

On Mon, Jan 11, 2016 at 12:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Chris Spencer <chrisspen(at)gmail(dot)com> writes:
> > I recently setup a GIN index on a large database, and it's performance is
> > spectacular. However, I noticed by default it doesn't allow searching by
> > partial matches.
>
> What's your grounds for claiming that?
>
> We recently fixed a bug whereby the planner overestimated the cost of
> partial-match index scans, which might discourage it from choosing an
> index scan versus other plans, but that's a lot different from "doesn't
> allow". In any case, the bug only manifests if you've never vacuumed the
> table since creating the index ...
>
> regards, tom lane
>