X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 851079FA42B for ; Wed, 21 Jun 2006 11:53:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 99799-06 for ; Wed, 21 Jun 2006 11:53:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id 61D119FA19B for ; Wed, 21 Jun 2006 11:53:10 -0300 (ADT) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [63.226.156.118] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 94272236; Wed, 21 Jun 2006 07:53:04 -0700 From: "Dave Dutcher" To: "'nicky'" , Subject: Re: Speeding up query, Joining 55mil and 43mil records. Date: Wed, 21 Jun 2006 09:53:03 -0500 Message-ID: <00cb01c69542$663f1c30$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CC_01C69518.7D691430" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 In-Reply-To: <44994DE7.5070801@valuecare.nl> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/399 X-Sequence-Number: 19756 This is a multi-part message in MIME format. ------=_NextPart_000_00CC_01C69518.7D691430 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Could you post an explain analyze of the query? Just FYI, if you do an explain analyze of the insert statement, it will actually do the insert. If you don't want that just post an explain analyze of the select part. To me it would be interesting to compare just the select parts of the query between Postgres and MSSQL. That way you would know if your Postgres install is slower at the query or slower at the insert. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of nicky Sent: Wednesday, June 21, 2006 8:47 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Speeding up query, Joining 55mil and 43mil records. Hello People, I'm trying to solve a 'what i feel is a' performance/configuration/query error on my side. I'm fairly new to configuring PostgreSQL so, i might be completely wrong with my configuration. My database consists of 44 tables, about 20GB. Two of those tables are 'big/huge'. Table src.src_faktuur_verricht contains 43million records (9GB) and table src.src_faktuur_verrsec contains 55million records (6GB). Below is the 'slow' query. INSERT INTO rpt.rpt_verrichting (verrichting_id ,verrichting_secid ,fout_status ,patientnr ,verrichtingsdatum ,locatie_code ,afdeling_code ,uitvoerder_code ,aanvrager_code ,verrichting_code ,dbcnr ,aantal_uitgevoerd ,kostenplaats_code ,vc_patientnr ,vc_verrichting_code ,vc_dbcnr ) SELECT t1.id , t0.secid , t1.status , t1.patientnr , t1.datum , t1.locatie , t1.afdeling , t1.uitvoerder , t1.aanvrager , t0.code , t1.casenr , t0.aantal , t0.kostplaats , null , null , null FROM src.src_faktuur_verrsec t0 JOIN src.src_faktuur_verricht t1 ON t0.id = t1.id WHERE substr(t0.code,1,2) not in ('14','15','16','17') AND (substr(t0.correctie,4,1) <> '1' OR t0.correctie is null) AND EXTRACT(YEAR from t1.datum) > 2004; Output from explain Hash Join (cost=1328360.12..6167462.76 rows=7197568 width=118) Hash Cond: (("outer".id)::text = ("inner".id)::text) -> Seq Scan on src_faktuur_verrsec t0 (cost=0.00..2773789.90 rows=40902852 width=52) Filter: ((substr((code)::text, 1, 2) <> '14'::text) AND (substr((code)::text, 1, 2) <> '15'::text) AND (substr((code)::text, 1, 2) <> '16'::text) AND (substr((code)::text, 1, 2) <> '17'::text) AND ((substr((correctie)::text, 4, 1) <> '1'::text) OR (correctie IS NULL))) -> Hash (cost=1188102.97..1188102.97 rows=8942863 width=80) -> Bitmap Heap Scan on src_faktuur_verricht t1 (cost=62392.02..1188102.97 rows=8942863 width=80) Recheck Cond: (date_part('year'::text, datum) > 2004::double precision) -> Bitmap Index Scan on src_faktuur_verricht_idx1 (cost=0.00..62392.02 rows=8942863 width=0) Index Cond: (date_part('year'::text, datum) > 2004::double precision) The db server runs PostgreSQL 8.1.4 on FreeBSD 6.1-Stable. 2GB of RAM. It contains two SATA150 disks, one contains PostgreSQL and the rest of the operating system and the other disk holds the pg_xlog directory. Changed lines from my postgresql.conf file shared_buffers = 8192 temp_buffers = 4096 work_mem = 65536 maintenance_work_mem = 1048576 max_fsm_pages = 40000 fsync = off wal_buffers = 64 effective_cache_size = 174848 The query above takes around 42 minutes. However, i also have a wimpy desktop machine with 1gb ram. Windows with MSSQL 2000 (default installation), same database structure, same indexes, same query, etc and it takes 17 minutes. The big difference makes me think that i've made an error with my PostgreSQL configuration. I just can't seem to figure it out. Could someone perhaps give me some pointers, advice? Thanks in advance. Nicky ------=_NextPart_000_00CC_01C69518.7D691430 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Message
Could=20 you post an explain analyze of the query?  Just FYI, if you do an = explain=20 analyze of the insert statement, it will actually do the insert.  = If you=20 don't want that just post an explain analyze of the select=20 part.
 
To me=20 it would be interesting to compare just the select parts of the query = between=20 Postgres and MSSQL.  That way you would know if your Postgres = install is=20 slower at the query or slower at the insert.
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20 nicky
Sent: Wednesday, June 21, 2006 8:47 = AM
To:=20 pgsql-performance@postgresql.org
Subject: [PERFORM] Speeding = up=20 query, Joining 55mil and 43mil records.

Hello People,

I'm trying to solve a 'what i = feel is a'=20 performance/configuration/query error on my side. I'm fairly new to=20 configuring PostgreSQL so, i might be completely wrong with my = configuration.=20

My database consists of 44 tables, about 20GB. Two of those = tables are=20 'big/huge'. Table src.src_faktuur_verricht contains 43million records = (9GB)=20 and table src.src_faktuur_verrsec contains 55million records (6GB).=20

Below is the 'slow' query.

INSERT INTO=20 = rpt.rpt_verrichting
(verrichting_id
,verrichting_secid
,fout_sta= tus
,patientnr
,verrichtingsdatum
,locatie_code
,afdeling_cod= e
,uitvoerder_code
,aanvrager_code
,verrichting_code
,dbcnr,aantal_uitgevoerd
,kostenplaats_code
,vc_patientnr
,vc_verric= hting_code
,vc_dbcnr
)
SELECT =20 t1.id
,      =20 t0.secid
,      =20 t1.status
,      =20 t1.patientnr
,      =20 t1.datum
,      =20 t1.locatie
,      =20 t1.afdeling
,      =20 t1.uitvoerder
,      =20 t1.aanvrager
,      =20 t0.code
,      =20 t1.casenr
,      =20 t0.aantal
,      =20 t0.kostplaats
,      =20 null
,      =20 null
,       = null
FROM   =20 src.src_faktuur_verrsec t0 = JOIN
       =20 src.src_faktuur_verricht t1 = ON
       =20 t0.id =3D t1.id
WHERE   substr(t0.code,1,2) not in=20 ('14','15','16','17')
AND     = (substr(t0.correctie,4,1)=20 <> '1' OR t0.correctie is null)
AND    =20 EXTRACT(YEAR from t1.datum) > 2004;


Output from=20 explain

Hash Join  (cost=3D1328360.12..6167462.76 = rows=3D7197568=20 width=3D118)
  Hash Cond: (("outer".id)::text =3D=20 ("inner".id)::text)

  ->  Seq Scan on = src_faktuur_verrsec=20 t0  (cost=3D0.00..2773789.90 rows=3D40902852=20 width=3D52)
        Filter:=20 ((substr((code)::text, 1, 2) <> '14'::text) AND = (substr((code)::text, 1,=20 2) <> '15'::text) AND (substr((code)::text, 1, 2) <> = '16'::text)=20 AND (substr((code)::text, 1, 2) <> '17'::text) AND=20 ((substr((correctie)::text, 4, 1) <> '1'::text) OR (correctie IS = NULL)))
  ->  Hash  = (cost=3D1188102.97..1188102.97=20 rows=3D8942863 = width=3D80)
       =20 ->  Bitmap Heap Scan on src_faktuur_verricht t1 =20 (cost=3D62392.02..1188102.97 rows=3D8942863=20 = width=3D80)
         &nbs= p;   =20 Recheck Cond: (date_part('year'::text, datum) > 2004::double=20 = precision)
          = ;   =20 ->  Bitmap Index Scan on src_faktuur_verricht_idx1 =20 (cost=3D0.00..62392.02 rows=3D8942863=20 = width=3D0)
          = ;         =20 Index Cond: (date_part('year'::text, datum) > 2004::double=20 precision)


The db server runs PostgreSQL 8.1.4 on FreeBSD=20 6.1-Stable. 2GB of RAM.
It contains two SATA150 disks, one = contains=20 PostgreSQL and the rest of the operating system and the other disk = holds the=20 pg_xlog directory.

Changed lines from my postgresql.conf=20 file

shared_buffers =3D 8192
temp_buffers =3D = 4096
work_mem =3D=20 65536
maintenance_work_mem =3D 1048576
max_fsm_pages =3D = 40000
fsync =3D=20 off
wal_buffers =3D 64
effective_cache_size =3D = 174848

The query=20 above takes around 42 minutes.

However, i also have a wimpy = desktop=20 machine with 1gb ram. Windows with MSSQL 2000 (default installation), = same=20 database structure, same indexes, same query, etc and it takes 17 = minutes. The=20 big difference makes me think that i've made an error with my = PostgreSQL=20 configuration. I just can't seem to figure it out.

Could = someone=20 perhaps give me some pointers, advice?

Thanks in advance.=20

Nicky




------=_NextPart_000_00CC_01C69518.7D691430--