TSearch is a Full-Text Search engine that is packaged with PostgreSQL. The key developers of TSearch are Oleg Bartunov and Teodor Sigaev who have also done extensive
work with GiST and GIN indexes used by PostGIS, PgSphere and other projects. For more about how TSearch and OpenFTS got started check out A Brief History of FTS in PostgreSQL.
Check out the TSearch Official Site if you are interested in related TSearch tips or interested in donating to this very worthy project.
Tsearch is different from regular string searching in
PostgreSQL in a couple of key ways.
It is well-suited for searching large blobs of text since each word is indexed using a Generalized Inverted Index (GIN) or Generalized Search Tree (GiST) and searched using text search vectors. GIN is generally used for indexing. Search vectors
are at word and phrase boundaries.
TSearch has a concept of Linguistic significance using various language dictionaries, ISpell, thesaurus, stop words, etc. therefore it can ignore common words and
equate like meaning terms and phrases.
TSearch is for the most part case insensitive.
While various dictionaries and configs are available out of the box with TSearch, one can create new ones and customize existing further to
cater to specific niches within industries - e.g. medicine, pharmaceuticals, physics, chemistry, biology, legal matters.
Prior to PostgreSQL 8.3, it was a contrib module
located in the shared/contribs folder. As of PostgreSQL 8.3 it is now fully integrated into the PostgreSQL core.
The official documents for using TSearch in 8.3 are located in
Chapter 12. Full Text Search of the official
In this article we shall provide a quick primer to using TSearch in 8.3.
In the next month's issue of the Postgres OnLine Journal we shall provide a TSearch cheat sheet similar to our PostgreSQL 8.3 cheat sheet.
FWTools GIS Toolkit is a freely available open source toolkit for Windows and Linux that can do more than GIS tricks.
It is a precompiled bundle of Open Source GIS tools.
The FW comes from the initials of Frank Warmerdam,
the originator of the toolkit and current President of the Open Source Geospatial Foundation (OSGEO).
This is a library which historically has been developed and maintained by Frank Warmerdam, but has started to garner quite a few developers.
GDAL is X/MIT licensed (similar to BSD license), therefore the licensing is very generous for commercial use. The toolkit can be downloaded from
One of our favorite features of PgAdmin is the graphical explain plan feature. While a graphical explain plan is not a complete
substitute for EXPLAIN or EXPLAIN ANALYZE text plans, it does provide a quick and easy to read view that can be used for further analysis.
In this article, we'll walk thru using
the explain plan to troubleshoot query performance.
To use the graphical explain plan feature in PgAdmin III - do the following
Launch PgAdmin III and select a database.
Click the SQL icon
Type in a query or set of queries, and highlight the text of the query you want to analyse.
Click the F7 button or go under Query->Explain or click the Explain Query icon .
If you see no graphical explain plan, make sure that Query->Explain options->Verbose is unchecked - otherwise graphical explain will not work
In terms of Explain option under the Query->Explain options-> you can choose Analyze which will give you the actual Explain plan in use and actual time and will take longer to run. Unchecking
this feature gives you the approximate explain plan and does not include time since its approximate. In terms of the graphical display - the raw display doesn't look too different between the 2, but if you click
on a section of the graph, a little tip will pop up showing the stats for that part of the graph. For analyze, you will see time metrics in the tip.