Skip site navigation (1) Skip section navigation (2)

Re: Full text indexing (and errors!)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mitch Vincent" <mitch(at)venux(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Full text indexing (and errors!)
Date: 2000-05-21 19:44:15
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
"Mitch Vincent" <mitch(at)venux(dot)net> writes:
>  attname | attdisbursion | starelid | staattnum | staop | stanullfrac |
> stacommonfrac | stacommonval | staloval | stahival
> ---------+---------------+----------+-----------+-------+-------------+-----
> ----------+--------------+----------+----------
>  string  |    0.00208943 |  1161760 |         1 |  1066 |           0 |
> 0.0100436 | on           | 00       | zzz

Hmm, so the most common word is "on" accounting for about 1% of the
entries.  Although I don't think that stacommonfrac directly affects
this particular query plan, it'd still be a good idea to try to push it
down.  fti.c has a provision for ignoring "stop words", but its stopword
list seems to be empty by default.  You might want to throw in "the" and
"on" and any other noisewords you're unlikely to want to search for.
That should help reduce the size of the fti table, too...

Actually ... waitasec.  stacommonfrac *does* affect this query plan in
7.0 release.  Before you do anything else, try enabling the new LIKE
estimator code (see contrib/likeplanning/ for details) and see what
sort of plan you get then.  The estimated selectivity should go *way*
down, and that ought to change the plan.

You'd still be well advised to get rid of as many stop words as you can.

			regards, tom lane

In response to

pgsql-sql by date

Next:From: Patrick CoulombeDate: 2000-05-22 04:50:19
Subject: duplicate key
Previous:From: Mitch VincentDate: 2000-05-21 19:15:46
Subject: Re: Full text indexing (and errors!)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group