Re: [GENERAL] Design Table & Search Question
Ian Harding wrote: On 5/31/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: 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. Hm? Allow me to clarify. To the best of my knowledge, and in my limited experience, the tsearch2() trigger function provided with tsearch will only index fields present in the table upon which it is created, in an index column present in the table upon which it is created. If I am incorrect in this assessment I would be glad to be corrected. Of course, custom triggers can be written to do anything from soup to nuts. Yes you would need a trigger to keep a separate search table but you can certainly join on that table for selects. J - Ian A. Harding Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- === 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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Design Table & Search Question
On 5/31/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: 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. Hm? Allow me to clarify. To the best of my knowledge, and in my limited experience, the tsearch2() trigger function provided with tsearch will only index fields present in the table upon which it is created, in an index column present in the table upon which it is created. If I am incorrect in this assessment I would be glad to be corrected. Of course, custom triggers can be written to do anything from soup to nuts. - Ian A. Harding Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Design Table & Search Question
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 <[EMAIL PROTECTED]> 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/ ---(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
Re: [GENERAL] Design Table & Search Question
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. 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 <[EMAIL PROTECTED]> 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
Re: [GENERAL] Design Table & Search Question
Thank you, Michael! I'm looking some examples and doing tests to find the best search solution. Best, On 5/30/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: On May 30, 2007, at 13:59 , Gabriel Laet wrote: > 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? Based on your rough sketch, I believe so. Here's what I imagine your schema being: CREATE TABLE make ( make_id INTEGER PRIMARY KEY , make_name TEXT NOT NULL UNIQUE ); CREATE TABLE model ( model_id INTEGER PRIMARY KEY , model_name TEXT NOT NULL UNIQUE , make_id INTEGER NOT NULL REFERENCES make ); CREATE TABLE car ( car_id INTEGER PRIMARY KEY , vin TEXT NOT NULL UNIQUE , model_id INTEGER NOT NULL REFERENCES model ); In this schema, you can find the make of a given car by joining through the model table, e.g., SELECT make_name, model_name, vin FROM make NATURAL JOIN model NATURAL JOIN car; > 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? This isn't really an area I have much experience with, so I'll leave it for someone else. You might want to think of adding a column on the car table that includes the make and model names so they could be easily searched by hitting a single table. I think you'd need triggers to update that search column, but it might help. The key is to benchmark the app and see how it performs using different strategies. Michael Glaesemann grzm seespotcode net -- ~Gabriel Laet
Re: [GENERAL] Design Table & Search Question
On May 30, 2007, at 13:59 , Gabriel Laet wrote: 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? Based on your rough sketch, I believe so. Here's what I imagine your schema being: CREATE TABLE make ( make_id INTEGER PRIMARY KEY , make_name TEXT NOT NULL UNIQUE ); CREATE TABLE model ( model_id INTEGER PRIMARY KEY , model_name TEXT NOT NULL UNIQUE , make_id INTEGER NOT NULL REFERENCES make ); CREATE TABLE car ( car_id INTEGER PRIMARY KEY , vin TEXT NOT NULL UNIQUE , model_id INTEGER NOT NULL REFERENCES model ); In this schema, you can find the make of a given car by joining through the model table, e.g., SELECT make_name, model_name, vin FROM make NATURAL JOIN model NATURAL JOIN car; 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? This isn't really an area I have much experience with, so I'll leave it for someone else. You might want to think of adding a column on the car table that includes the make and model names so they could be easily searched by hitting a single table. I think you'd need triggers to update that search column, but it might help. The key is to benchmark the app and see how it performs using different strategies. Michael Glaesemann grzm seespotcode net ---(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
[GENERAL] Design Table & Search Question
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
