Received: from icebox.venux.net (icebox.venux.net [216.120.166.10]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA35371 for ; Sun, 21 May 2000 15:10:39 -0400 (EDT) (envelope-from mitch@venux.net) Received: from doot2 (usr3-ip103-grr.wmis.net [209.176.193.153]) by icebox.venux.net (Postfix) with SMTP id A69B726202; Sun, 21 May 2000 15:14:33 -0400 (EDT) Message-ID: <002f01bfc358$f82cfee0$0300000a@doot.org> From: "Mitch Vincent" To: "Tom Lane" Cc: References: <00db01bfc345$e5400100$0300000a@doot.org> <9916.958931894@sss.pgh.pa.us> <001c01bfc350$d6d8ddc0$0300000a@doot.org> <10020.958933715@sss.pgh.pa.us> Subject: Re: Full text indexing (and errors!) Date: Sun, 21 May 2000 15:15:46 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200005/98 attname | attdisbursion | starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval | stahival ---------+---------------+----------+-----------+-------+-------------+----- ----------+--------------+----------+---------- string | 0.00208943 | 1161760 | 1 | 1066 | 0 | 0.0100436 | on | 00 | zzz id | 3.40795e-05 | 1161760 | 2 | 609 | 0 | 0.000170281 | 9807369 | 7647538 | 41122350 (2 rows) There ya go! ----- Original Message ----- From: Tom Lane To: Mitch Vincent Cc: Sent: Sunday, May 21, 2000 2:28 PM Subject: Re: [SQL] Full text indexing (and errors!) > "Mitch Vincent" writes: > >> is there an index on applicants' OID column? > > > No, there is not an index on the applicant OID column.. Since I'm getting > > all the records from the applicants table where the string I search for is > > in the resumes_fti table, I didn't think and index like that would help > > (since I'm qualifying the results based on rows in another table). Am I > > wrong in thinking that? > > If the pattern match is reasonably selective then I'd think that the > best plan would probably be an indexscan on resumes_fti (using the > pattern operator to select rows) and then a nestloop join against the > applicant table using an inner indexscan on OID. In English: look up > the entries in resumes_fti that match the pattern, and then use the OIDs > to look up the applicants entries ;-). But it doesn't work without the > index on OID. > > > select count(app_id) from applicants_resumes; > > 14673 > > select count(id) from resumes_fti; > > 33462249 > > Hmm. So the selectivity being estimated for the pattern match is > 168041/33462249 or about 0.005 ... which is not huge but we'd > probably like it to be smaller. What do you get from the standard > statistical query: > > select attname,attdisbursion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'resumes_fti'; > > regards, tom lane >