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 3244052A5B; Mon, 14 Mar 2005 15:54:38 +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 65419-07; Mon, 14 Mar 2005 15:54:29 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 2124F55E98; Mon, 14 Mar 2005 15:54:26 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j2EFsT2f002904; (envelope-from ) Mon, 14 Mar 2005 09:54:30 -0600 (CST) Received: from [192.168.1.13] (12-217-241-0.client.mchsi.com [12.217.241.0]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j2EFsSGO022321 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Mon, 14 Mar 2005 09:54:29 -0600 (CST) Message-ID: <4235B3B5.3040209@arbash-meinel.com> Date: Mon, 14 Mar 2005 09:54:29 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?windows-1250?Q?Miroslav_=8Aulc?= Cc: Tom Lane , 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> <42354EA7.4020907@startnet.cz> In-Reply-To: <42354EA7.4020907@startnet.cz> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig6877F4BA4242716DD3BB785E" Content-Transfer-Encoding: 8bit X-Virus-Scanned: ClamAV 0.83/723/Thu Feb 24 05:54:24 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/726 X-Sequence-Number: 65528 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig6877F4BA4242716DD3BB785E Content-Type: text/plain; charset=windows-1250; format=flowed Content-Transfer-Encoding: 8bit Miroslav Šulc wrote: > 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? > I'm guessing there really wouldn't be a difference. I think varchar() and char() are stored the same way, just one always has space padding. I believe they are both varlena types, so they are still "variable" length. >> 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. One thing that you could try, is to select just the primary keys from the main table, and then later on, join back to that table to get the rest of the columns. It is a little bit hackish, but if it makes your query faster, you might want to try 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. > I don't think they are stored in the order of the SELECT <> portion. I'm guessing they are loaded and saved as you go. But that the order of the LEFT JOIN at the end is probably important. >> .. >> regards, tom lane >> >> > Miroslav John =:-> --------------enig6877F4BA4242716DD3BB785E Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCNbO1JdeBCYSNAAMRAsumAJ4yGUVZvYqBgKBLnYI2GVabC69GbwCfeL99 JRGLUKxq6NLt0R5YciE2odM= =wUoo -----END PGP SIGNATURE----- --------------enig6877F4BA4242716DD3BB785E--