Your tiff file is definitely inefficient, files around 1-2GB are normally better since GeoServer won't have to open as many files to generate the output.
Inner tiles help when you are zoomed in and need only to access a portion of the file.
Having a 4 times jump between levels will improve disk occupation, but will also slow down image serving and also quality of the output.
The rest I already wrote in the presentation ;-)
If you want to display large rasters in QGIS, you have to build pyramids, either for a tif image on the file system or for a image registered in Postgis.
The performance difference in QGIS rendering between a large raster in the file system or in Postgis is miminal. Users will not notice the difference. But - if and only if - you build the pyramids with the option -l
.
If you simple import the image without the -l option, or with just -l 4
it will not work.
If you use, for example, -l 2,4,8,16
, four levels of pyramids will be created, like in the layer below:
If you want to have a better user experience, you should add more levels of pyramids, like -l 2,4,8,16,32,64,128,256
. This will create eight levels of pyramids.
To summarize, the answer to this question is: import the raster with the option -l
and use the same number of pyramid levels as you use for the same raster on the file system.
For example:
raster2pgsql -s 3161 -d -C -I -M -l 2,4,8,16,32,64,128,256 D:\PostGIS_data\dem.img -t 100x100 raster.dem100 | psql -h localhost -U postgres -p 5432 -d postgres
Best Answer
Since Postgis is a component of Postgres I would recommend this great book (I own it and I found it extremely valuable) on Postgres performance tuning:
http://www.packtpub.com/postgresql-90-high-performance/book
It starts from the basics (planning the hardware, os, etc) and then grows into explaining all those misterious configuration params that I never knew how to tune before. After that it shows how to analyze slow queries, explains how the optimizer works, how to monitor general database activity and find bottlenecks.
The author is a postgres developer so he really knows what he's talking about and the book has been also praised from the development group.
The book is focused on version 9 but it always says when a solution applies or not and with which differences to prior versions (down to 8.0, if I recall correctly).