PostGIS raster has so so many functions and probably at least 10 ways of doing something some much much slower than others. Suppose
you have a raster, or you have a raster area of interest — say elevation raster for example, and you want to know the distinct pixel values in the area.
The temptation is to reach for
ST_Value function in raster, but there is a much much more efficient function to use, and that is the
ST_ValueCount function is one of many statistical raster functions available with PostGIS 2.0+. It is a set returning function that returns 2 values for each row: a pixel value (value), and a count of pixels (count) in the raster that have that value. It also has variants that allow you to filter for certain pixel values.
This tip was prompted by the question on stackexchange How can I extract all distinct values from a PostGIS Raster?