| From: | "Raymond C(dot) Rodgers" <sinful622(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Dumb question involving to_tsvector and a view | 
| Date: | 2013-02-22 20:42:07 | 
| Message-ID: | 5127D81F.3030000@gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hi folks,
     I'm building a PHP script for a web site I'm developing. At the 
moment, there is absolutely no real data in the database, so obviously 
performance is pretty good right now. I'm in the midst of developing an 
administration page for the site, which will do a full text search on 
several tables separately, and I realized that one of the tables 
currently doesn't have a tsvector column. As I went to add a tsvector 
column, it occurred to me that it might be possible to add a dynamic 
tsvector column through the use of a view, so I created a temporary view 
with a command along the lines of:
     CREATE TEMPORARY VIEW ftstest AS SELECT id, field1, field2, 
TO_TSVECTOR(COALESCE(field1,'') || ' ' || COALESCE(field2,'')) AS 
txtsrch FROM mytable;
     To my surprise, it worked. Now, I'm sitting here thinking about the 
performance impact that doing this would have. I can't help but think 
that a query to this view when the table is filled with thousands or 
tens of thousands of entries would be painfully slow, but would there be 
any real advantage to doing it in a view rather than just adding the 
column to the table? (That's the dumb question.) If the site only had a 
few dozen users, and the amount of data on the site was minimal, this 
wouldn't be too big an issue. Still a bad design decision, but are there 
any good reasons to do it?
Thanks for your patience with this dumb question. :)
Raymond
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Leonardo M. Ramé | 2013-02-22 20:43:05 | Spellcheck function | 
| Previous Message | Maz Mohammadi | 2013-02-22 20:42:06 | Re: confirming security. |