X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 14CEC5662A for ; Sun, 13 Mar 2005 20:03:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17964-09 for ; Sun, 13 Mar 2005 20:03:29 +0000 (GMT) Received: from sranda.klamovka.kosire.czf (unknown [82.113.63.66]) by svr1.postgresql.org (Postfix) with ESMTP id 5ECAE56626 for ; Sun, 13 Mar 2005 20:03:27 +0000 (GMT) Received: (qmail 31552 invoked from network); 13 Mar 2005 21:03:28 +0100 Received: from fordfrog.klamovka.kosire.czf (HELO ?192.168.11.101?) (10.15.70.18) by ns.klamovka.kosire.czf with AES256-SHA encrypted SMTP; 13 Mar 2005 21:03:28 +0100 Message-ID: <42349C96.60401@startnet.cz> Date: Sun, 13 Mar 2005 21:03:34 +0100 From: =?windows-1250?Q?Miroslav_=8Aulc?= Organization: StartNet s.r.o. User-Agent: Mozilla Thunderbird 1.0 (X11/20041208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: John Arbash Meinel , PGSQL mailing list Subject: Re: How to read query plan References: <42345D24.8070104@startnet.cz> <4234692E.3050802@arbash-meinel.com> <42347407.2000202@startnet.cz> <423478B1.7080106@arbash-meinel.com> <18381.1110741354@sss.pgh.pa.us> <423497FE.6040908@startnet.cz> <19293.1110743673@sss.pgh.pa.us> In-Reply-To: <19293.1110743673@sss.pgh.pa.us> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/mixed; boundary="------------000100030707080605050102" X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/231 X-Sequence-Number: 10936 This is a multi-part message in MIME format. --------------000100030707080605050102 Content-Type: text/plain; charset=windows-1250; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >=?windows-1250?Q?Miroslav_=8Aulc?= writes: > > >>shared_buffers = 48000 # min 16, at least max_connections*2, >>8KB each >>work_mem = 1024 # min 64, size in KB >>maintenance_work_mem = 16384 # min 1024, size in KB >>max_stack_depth = 2048 # min 100, size in KB >> >> > >Hmm. Given the small size of the auxiliary tables, you'd think they'd >fit in 1MB work_mem no problem. But try bumping work_mem up to 10MB >just to see if it makes a difference. (BTW, you do know that altering >the .conf file doesn't in itself do anything? You have to SIGHUP the >postmaster to make it notice the change ... and for certain parameters >such as shared_buffers, you actually have to stop and restart the >postmaster. You can use the SHOW command to verify whether a change >has taken effect.) > > I've tried to set work_mem to 10240, restarted postmaster and tried the EXPLAIN ANALYZE but there is only cca 200 ms speedup. >>I have checked this and there are some JOINs smallint against integer. >>Is that problem? >> >> >That probably explains why some of the joins are merges instead of >hashes --- hash join doesn't work across datatypes. Doesn't seem like >it should be a huge problem though. I was more concerned about the >possibility of slow locale-dependent string comparisons. > > There are only JOINs number against number. I've tried to change one of the fields from smallint to integer but there was no speedup. > regards, tom lane > > Miroslav --------------000100030707080605050102 Content-Type: text/x-vcard; charset=utf-8; name="miroslav.sulc.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="miroslav.sulc.vcf" begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:miroslav.sulc@startnet.cz title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard --------------000100030707080605050102--