Dumb question involving to_tsvector and a view

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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.