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 B90FD565E9 for ; Sun, 13 Mar 2005 19:44:02 +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 13871-05 for ; Sun, 13 Mar 2005 19:43:53 +0000 (GMT) Received: from sranda.klamovka.kosire.czf (unknown [82.113.63.66]) by svr1.postgresql.org (Postfix) with ESMTP id 67A4A565AF for ; Sun, 13 Mar 2005 19:43:51 +0000 (GMT) Received: (qmail 9130 invoked from network); 13 Mar 2005 20:43:52 +0100 Received: from fordfrog.klamovka.kosire.czf (HELO ?192.168.11.101?) (10.15.70.18) by sranda.klamovka.kosire.czf with AES256-SHA encrypted SMTP; 13 Mar 2005 20:43:52 +0100 Message-ID: <423497FE.6040908@startnet.cz> Date: Sun, 13 Mar 2005 20:43:58 +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> In-Reply-To: <18381.1110741354@sss.pgh.pa.us> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/mixed; boundary="------------070302000306050507080505" X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/228 X-Sequence-Number: 10933 This is a multi-part message in MIME format. --------------070302000306050507080505 Content-Type: text/plain; charset=windows-1250; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >John Arbash Meinel writes: > > >>How about a quick side track. >>Have you played around with your shared_buffers, maintenance_work_mem, >>and work_mem settings? >> >> > >Indeed. The hash joins seem unreasonably slow considering how little >data they are processing (unless this is being run on some ancient >toaster...). One thought that comes to mind is that work_mem may be >set so small that the hashes are forced into multiple batches. > > I've just tried to uncomment the settings for these parameters with with no impact on the query speed. 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 >Another question worth asking is what are the data types of the columns >being joined on. If they are character types, what locale and encoding >is the database using? > > I have checked this and there are some JOINs smallint against integer. Is that problem? I would use smallint for IDPKs of some smaller tables but the lack of SMALLSERIAL and my laziness made me use SERIAL instead which is integer. >That cost would be paid during the bottom-level scans though. The thing >that strikes me here is that nearly all of the cost is being spent >joining. > > >>What version of postgres are you using? >> >> > >And what's the platform (hardware and OS)? > > I've already posted the hardware info. OS is Linux (Gentoo) with kernel 2.6.11. > regards, tom lane > > Miroslav --------------070302000306050507080505 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 --------------070302000306050507080505--