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 5A6B555EAE for ; Mon, 14 Mar 2005 16:36:34 +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 78444-06 for ; Mon, 14 Mar 2005 16:36:25 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 3983B52A7A for ; Mon, 14 Mar 2005 16:36:24 +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 j2EGaKDh004391; (envelope-from ) Mon, 14 Mar 2005 10:36:22 -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 j2EGaIrN002461 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Mon, 14 Mar 2005 10:36:19 -0600 (CST) Message-ID: <4235BD81.1060606@arbash-meinel.com> Date: Mon, 14 Mar 2005 10:36:17 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?ISO-8859-15?Q?Miroslav_=A6ulc?= Cc: PFC , PGSQL mailing list Subject: Re: How to read query plan References: <42345D24.8070104@startnet.cz> <4234692E.3050802@arbash-meinel.com> <42347407.2000202@startnet.cz> <42347D5E.9000900@arbash-meinel.com> <42348F54.1000408@startnet.cz> <423493E5.6040203@arbash-meinel.com> <42355249.8090101@startnet.cz> <423556B8.4020500@startnet.cz> In-Reply-To: <423556B8.4020500@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="------------enig3FC72B5A1B44F3B4815DC440" 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/267 X-Sequence-Number: 10972 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig3FC72B5A1B44F3B4815DC440 Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 8bit Miroslav Ķulc wrote: > PFC wrote: > >> Your query seems of the form : >> >> SELECT FROM main_table LEFT JOIN a lot of tables ORDER BY >> sort_key LIMIT N OFFSET M; >> >> I would suggest to rewrite it in a simpler way : instead of >> generating the whole result set, sorting it, and then grabbing a >> slice, generate only the ror id's, grab a slice, and then generate >> the full rows from that. >> >> - If you order by a field which is in main_table : >> SELECT FROM main_table LEFT JOIN a lot of tables WHERE >> main_table.id IN (SELECT id FROM main_table ORDER BY sort_key LIMIT >> N OFFSET M >> ) ORDER BY sort_key LIMIT N OFFSET M; >> >> - If you order by a field in one of the child tables, I guess you >> only want to display the rows in the main table which have this >> field, ie. not-null in the LEFT JOIN. You can also use the principle >> above. >> >> - You can use a straight join instead of an IN. > > > Do you mean something like this? > > SELECT Table.IDPK, Table2.varchar1, Table2.varchar2, ... > FROM Table > LEFT JOIN many tables > INNER JOIN Table AS Table2 > > Miroslav I would also recommend using the subselect format. Where any columns that you are going to need to sort on show up in the subselect. So you would have: SELECT ... FROM main_table LEFT JOIN tablea ON ... LEFT JOIN tableb ON ... ... JOIN other_table ON ... WHERE main_table.idpk IN (SELECT idpk FROM main_table JOIN other_table ON main_table.idpk = other_table. WHERE ... ORDER BY other_table.abcd LIMIT n OFFSET m) ; I think the final LIMIT + OFFSET would give you the wrong results, since you have already filtered out the important rows. I also think you don't need the final order by, since the results should already be in sorted order. Now this also assumes that if someone is sorting on a row, then they don't want null entries. If they do, then you can change the subselect into a left join. But with appropriate selectivity and indexes, an inner join can filter out a lot of rows, and give you better performance. The inner subselect gives you selectivity on the main table, so that you don't have to deal with all the columns in the search, and then you don't have to deal with all the rows later on. I think you can also do this: SELECT ... FROM (SELECT main_table.idpk, other_table. FROM main_table JOIN other_table ....) as p LEFT JOIN ... JOIN main_table ON main_table.idpk = p.idpk; In that case instead of selecting out the id and putting that into the where, you put it in the from, and then join against it. I don't really know which is better. John =:-> --------------enig3FC72B5A1B44F3B4815DC440 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 iD8DBQFCNb2BJdeBCYSNAAMRAoUlAKC0bUOwRIiE/8aTNcfeGqvMRijdpwCeOBcV B/4iGWCyl9z0vn/czJ48SG4= =iF5Y -----END PGP SIGNATURE----- --------------enig3FC72B5A1B44F3B4815DC440--