crosstab speed

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux