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
