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

Re: Design Table & Search Question

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: harding(dot)ian(at)gmail(dot)com
Cc: Gabriel Laet <gabriel(dot)laet(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Design Table & Search Question
Date: 2007-05-31 17:46:34
Message-ID: 465F09FA.2000704@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-general
Ian Harding wrote:
> tsearch indexes have to reside in the table where the data is, for the
> automagical functions that come with it to work.  You can define a
> view that joins the tables, then search each of the index columns for
> the values you are looking for.

No they don't.

Joshua D. Drake


> 
> In my experience, the LIKE searches are fast for relatively small
> datasets, but they are often implemented funny, where a search for
> 'ING' (the company) would return zillions of records with verbs (and
> gerunds) in them.  tsearch is smarter than me.  It knows where to stem
> words and rarely gives unexpected results.  Automotive nomenclature
> will seldom be stemmed so you will get whole word searches which is
> usually fine.
> 
> Good luck!
> 
> - Ian
> 
> On 30 May 2007 11:59:04 -0700, Gabriel Laet <gabriel(dot)laet(at)gmail(dot)com> wrote:
>> Hi,
>>
>> I'm developing an application where basically I need to store cars.
>> Every car has a Make and Model association. Right now, I have three
>> tables: MAKE, MODEL (make_id) and CAR (model_id).
>>
>> 1) I'm not sure if I need or not to include "make_id" to the CAR
>> table. To me, it's clear to associate just the Model. Am I right?
>>
>> 2) I'm thinking in the best way to search content. I'll need to search
>> data across multiple-tables, and I'm not sure about the best way to do
>> that. Should I use TSearch2 or just a bunch of LIKEs and JOINs
>> statements?
>>
>> I appreciate any help :)
>> Thanks!
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>        choose an index scan if your joining column's datatypes do not
>>        match
>>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 


-- 

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


In response to

Responses

pgsql-general by date

Next:From: Oleg BartunovDate: 2007-05-31 18:23:21
Subject: Re: TSEARCH2: disable stemming in indexes and triggers
Previous:From: Alvaro HerreraDate: 2007-05-31 17:42:20
Subject: Re: Numeric performances

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