Re: Full text index not being used

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Alex <alex(at)liivid(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Full text index not being used
Date: 2009-02-01 17:49:06
Message-ID: Pine.LNX.4.64.0902012044110.9554@sn.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Alex,

what text you're indexing ? I don't believe you have meaningful
very long words ( > 2047 characters).

Do you really need multicolumn index ?

I'd recommend to separate problem - create column fts for
tsvector('english',full_listing), create index on it and try full-text
query. The way you're doing imply calling to_tsvector every time you
search, which can be very costly.

Olegk

On Sun, 1 Feb 2009, Alex wrote:

> So this seems to be because the result size is too big. I still don't
> know why it is looping through every record and printing a warning,
> but adding a LIMIT makes the queries complete in a reasonable time
> (although not all that fast).
>
> However I need to sort and also have many other facets that may or may
> not be included in the query. Adding a sort makes it load every
> record again and take forever.
>
> I tried to create an index including all of the fields I query on to
> see if that would work, but I get an error the the index row is too
> large:
>
> => create index master_index on source_listings(geo_lat, geo_lon,
> price, bedrooms, region, city, listing_type, to_tsvector('english',
> full_listing), post_time);
> NOTICE: word is too long to be indexed
> DETAIL: Words longer than 2047 characters are ignored.
> NOTICE: word is too long to be indexed
> DETAIL: Words longer than 2047 characters are ignored.
> NOTICE: word is too long to be indexed
> DETAIL: Words longer than 2047 characters are ignored.
> NOTICE: word is too long to be indexed
> DETAIL: Words longer than 2047 characters are ignored.
> ERROR: index row requires 13356 bytes, maximum size is 8191
>
> Any ideas about how to resolve this?
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gregory Stark 2009-02-01 18:23:55 Re: Indices types, what to use. Btree, Hash, Gin or Gist
Previous Message Gregory Stark 2009-02-01 17:19:34 Re: Full text index not being used, even though it is in the plan