Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA56283 for ; Sun, 21 May 2000 14:29:14 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id OAA10023; Sun, 21 May 2000 14:28:35 -0400 (EDT) To: "Mitch Vincent" cc: pgsql-sql@postgresql.org Subject: Re: Full text indexing (and errors!) In-reply-to: <001c01bfc350$d6d8ddc0$0300000a@doot.org> References: <00db01bfc345$e5400100$0300000a@doot.org> <9916.958931894@sss.pgh.pa.us> <001c01bfc350$d6d8ddc0$0300000a@doot.org> Comments: In-reply-to "Mitch Vincent" message dated "Sun, 21 May 2000 14:17:34 -0400" Date: Sun, 21 May 2000 14:28:35 -0400 Message-ID: <10020.958933715@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/97 "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