Hi, Kevin:
Second machine config parameters: shared_buffers = 8GB work_mem = 1 GB (was 512MB) maintenace_work_mem = 4 GB #seq_page_cost = 1.0 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.005 #cpu_operator_cost = 0.0025 random_page_cost = 2.0 effective_cache_size = 110GB I try to change from_collapse_limit, join_collapse_limit and io_con, w/o success. I create a database with this tables only, vaccum analyze them and test with only my connection to postgresql. Now we have another querys(all with aggregates) that the time is 15x - 20x slower than Oracle and SQL Server. All tables have indexes (btree) with fields in the where/order/group parameters. Maxim: The developer is changing from a Desktop application (ODBC with Use Declare/Fetch, 'single' querys with local summing and aggregation) for a client/server web application (.NET, most querys with aggregate). Unfortunattly we cant change this querys, but I will try your solution to see what happens. Take a look at another big query generated by the development tool. Oracle/SQL Server runs the same query (with the same data but in a slow machine) in about 2 seconds: http://explain.depesz.com/s/wxq Best regards, Alexandre 2015-08-05 14:24 GMT-03:00 Kevin Grittner <kgri...@ymail.com>: > Alexandre de Arruda Paes <adald...@gmail.com> wrote: > > > We did the following tests: > > > > 1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA > disk,Core i5) > > 2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, > SAS disks) > > That's only part of the information we would need to be able to > give specific advice. Please read this page: > > https://wiki.postgresql.org/wiki/SlowQueryQuestions > > One possibility is that you are running with the default > configuration, rather than having tuned for the hardware. You are > very likely to need to adjust shared_buffers, effective_cache_size, > work_mem, maintenance_work_mem, random_page_cost, cpu_tuple_cost, > and (at least for the second machine) effective_io_concurrency. If > the queries have a lot of joins you may need to increase > from_collapse_limit and/or join_collapse_limit. You also may need > to adjust [auto]vacuum and/or background writer settings. Various > OS settings may matter, too. > > To get a handle on all this, it might be worth looking for Greg > Smith's book on PostgreSQL high performance. > > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
