Thanks to Richard for the help earlier this week with performance
questions, once I was able to get pgsql and mssql using the same
resources and doing some tuning, I was able to get comparable results.
The issue still though, I have this view that I designed with the
thought in mind to provide all fields the user will want to search in
the PHP web app. However, I have found when joining a couple of large
tables, the view count reaches the 40+ million records, this does not
seem to work in pgsql (or in mssql), especially if distinct is needed.
Should I even be attempting this approach?
I have never used TSearch and wonder if that is the solution to this
type of search? From a quick read of some TSearch info I see indexes are
setup on a column basis in a table? So, I could create an index column
for say the first name and last name fields in a contact table, but
cannot create a field with information from different tables? Also, not
all fields are text that I need to search, some boolean for instance,
can I search TSearch index fields and other fields at the same time. Not
sure how TSearch works, just wanted to get an opinion that that may be
what I need before delving into it too much.
--
Robert