X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3ABC6563E3 for ; Mon, 14 Mar 2005 08:43:16 +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 29712-03 for ; Mon, 14 Mar 2005 08:43:12 +0000 (GMT) Received: from sranda.klamovka.kosire.czf (unknown [82.113.63.66]) by svr1.postgresql.org (Postfix) with ESMTP id 3A68D563D6 for ; Mon, 14 Mar 2005 08:43:11 +0000 (GMT) Received: (qmail 11495 invoked from network); 14 Mar 2005 09:43:12 +0100 Received: from fordfrog.klamovka.kosire.czf (HELO ?192.168.11.101?) (10.15.70.18) by smtp.klamovka.kosire.czf with AES256-SHA encrypted SMTP; 14 Mar 2005 09:43:12 +0100 Message-ID: <42354EA7.4020907@startnet.cz> Date: Mon, 14 Mar 2005 09:43:19 +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: pgsql-performance@postgreSQL.org, pgsql-hackers@postgreSQL.org Subject: Re: [PERFORM] 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> <42349C96.60401@startnet.cz> <3174.1110745587@sss.pgh.pa.us> <4234AF56.1040509@startnet.cz> <6715.1110758921@sss.pgh.pa.us> In-Reply-To: <6715.1110758921@sss.pgh.pa.us> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/mixed; boundary="------------030406020708070302050304" X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/731 X-Sequence-Number: 65533 This is a multi-part message in MIME format. --------------030406020708070302050304 Content-Type: text/plain; charset=windows-1250; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >... >I think the reason this is popping to the top of the runtime is that the >joins are so wide (an average of ~85 columns in a join tuple according >to the numbers above). Because there are lots of variable-width columns >involved, most of the time the fast path for field access doesn't apply >and we end up going to nocachegetattr --- which itself is going to be >slow because it has to scan over so many columns. So the cost is >roughly O(N^2) in the number of columns. > > As there are a lot of varchar(1) in the AdDevicesSites table, wouldn't be helpful to change them to char(1)? Would it solve the variable-width problem at least for some fields and speed the query up? >As a short-term hack, you might be able to improve matters if you can >reorder your LEFT JOINs to have the minimum number of columns >propagating up from the earlier join steps. In other words make the >later joins add more columns than the earlier, as much as you can. > > That will be hard as the main table which contains most of the fields is LEFT JOINed with the others. I'll look at it if I find some way to improve it. I'm not sure whether I understand the process of performing the plan but I imagine that the data from AdDevicesSites are retrieved only once when they are loaded and maybe stored in memory. Are the columns stored in the order they are in the SQL command? If so, wouldn't it be useful to move all varchar fields at the end of the SELECT query? I'm just guessing because I don't know at all how a database server is implemented and what it really does. >.. > regards, tom lane > > Miroslav --------------030406020708070302050304 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 --------------030406020708070302050304--