ok, I have an application that I am trying to speed up. Its a reporting application that makes heavy use of the crosstab function.
Here is some of the setup / configuration details:
Postgres 8.3.3
RedHat Enterprise 5.2 (2.6.18 kernel)
sun x4600, 8 dual core opteron 8218 processors, 32BG, StorageTek SAN
6 15k FC disks raid 10 for data,
2 15k FC disks raid 1 for xlog,
2 10k SAS disks raid 1 for OS
The table that I am querying has just under 600k records, 55 columns, 30 indexes
The table is not static, there are several hundred inserts a day into it.
This is not the only application that uses postgres on this server. There are several other transactional apps as well
here is an example query
select "COL_HEAD"[1] as site, "COL_HEAD"[2] as product_line_description, "COL_HEAD"[3] as report_sls, "COL_HEAD"[4] as fy_period, "2006" , "2007" , "2008" , "2009" from public.crosstab('select ARRAY[site::text,product_line_description::text,report_sls::text,fy_period::text] as COL_HEADER, fy_year, sum(invoice_value) from order_data_tbl where fy_year is not null group by site::text,product_line_description::text,report_sls::text,fy_period::text, fy_year order by site::text,product_line_description::text,report_sls::text,fy_period::text', 'select fy_year from order_data_tbl where fy_year is not null group by fy_year order by fy_year') as order_data_tbl("COL_HEAD" text[], "2006" numeric(20,2) , "2007" numeric(20,2) , "2008" numeric(20,2) , "2009" numeric(20,2) )
The crostab function is taking between 5 and 15 seconds to return. While the query is running one of the cores will be close to 100%, but watching iostat makes be believe that the entire table is cached and none of it is being read from disk. Depending on what report is being run the indexes may or may not be of any assistance. In the above query the planner does not use an index. Depending on what the user is looking for some indexes will be used because there is more specified in the where clause, at which point the query time can be under two seconds. The problem is that most reports that get generated with this application don't have a where clause. Are there any changes that can make to my config to speed up these huge aggregating queries?
Here is my postgresql.conf
max_connections = 1500
shared_buffers = 8GB
work_mem = 2GB
maintenance_work_mem = 8GB
max_fsm_pages = 2048000
wal_buffers = 1024kB
checkpoint_segments = 256
checkpoint_timeout = 10min
effective_cache_size = 20GB
default_statistics_target = 100
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1GB
log_error_verbosity = default
autovacuum = on
autovacuum_max_workers = 9
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
synchronize_seqscans = on
log_min_duration_statement = 250
-Jeremiah Elliott
