Re: [PERFORM] SMP on a heavy loaded database

2013-01-03 Thread Scott Marlowe
On Thu, Jan 3, 2013 at 4:45 PM, nobody nowhere wrote: > Centos 5.X kernel 2.6.18-274 > pgsql-9.1 from pgdg-91-centos.repo > relatively small database 3.2Gb > Lot of insert, update, delete. > > I see non balanced _User_ usage on 14 CPU, exclusively assigned to the > hardware raid controller. > Wha

Re: [PERFORM] serious under-estimation of n_distinct for clustered distributions

2013-01-03 Thread Stefan Andreatta
On 12/29/2012 10:57 PM, Peter Geoghegan wrote: On 29 December 2012 20:57, Stefan Andreatta wrote: ... The general advice here is: 1) Increase default_statistics_target for the column. I tried that, but to get good estimates under these circumstances, I need to set the statistics_target so

Re: [PERFORM] Simple join doesn't use index

2013-01-03 Thread Stefan Andreatta
On 01/03/2013 11:54 PM, Alex Vinnik wrote: Don't understand why PG doesn't use views_visit_id_index in that query but rather scans whole table. One explanation I have found that when resulting dataset constitutes ~15% of total number of rows in the table then seq scan is used. In this case result

[PERFORM] SMP on a heavy loaded database

2013-01-03 Thread nobody nowhere
Centos 5.X kernel 2.6.18-274 pgsql-9.1 from pgdg-91-centos.repo relatively small database 3.2Gb Lot of insert, update, delete. I see non balanced _User_ usage on 14 CPU, exclusively assigned to the hardware raid controller. What I'm doing wrong, and is it possible somehow to fix? Thanks in advan

Re: [PERFORM] Simple join doesn't use index

2013-01-03 Thread Jeremy Harris
On 01/03/2013 10:54 PM, Alex Vinnik wrote: I have implemented my first app using PG DB and thought for a minute(may be two) that I know something about PG but below problem totally destroyed my confidence :). Please help me to restore it. https://wiki.postgresql.org/wiki/SlowQueryQuestions -- J

[PERFORM] Simple join doesn't use index

2013-01-03 Thread Alex Vinnik
Hi everybody, I have implemented my first app using PG DB and thought for a minute(may be two) that I know something about PG but below problem totally destroyed my confidence :). Please help me to restore it. Here is simple join query. It runs just fine on MS SQL 2008 and uses all available inde

Re: [PERFORM] FW: performance issue with a 2.5gb joinded table

2013-01-03 Thread Daniel Westermann
-Original Message- From: Heikki Linnakangas [mailto:hlinnakan...@vmware.com] Sent: Donnerstag, 3. Januar 2013 18:02 To: Daniel Westermann Cc: 'pgsql-performance@postgresql.org' Subject: Re: [PERFORM] FW: performance issue with a 2.5gb joinded table On 03.01.2013 15:30, Daniel Westermann w

Re: [PERFORM] FW: performance issue with a 2.5gb joinded table

2013-01-03 Thread Heikki Linnakangas
On 03.01.2013 15:30, Daniel Westermann wrote: What additionally makes me wonder is, that the same table in oracle is taking much less space than in postgresql: SQL> select sum(bytes) from dba_extents where segment_name = 'TEST1'; SUM(BYTES) -- 1610612736 select pg_relation_size('mgmt

[PERFORM] FW: performance issue with a 2.5gb joinded table

2013-01-03 Thread Daniel Westermann
Hi Listers, we migrated an oracle datawarehouse to postgresql 9.1 ( ppas 9.1.7.12 ) and are facing massive issues with response times in postgres when compared to the oracle system. Both database run on the same hardware and storage ( rhel5.8 64bit ). Oracle memory parameters are: SGA=1gb PGA=