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 53EC1563B3 for ; Mon, 14 Mar 2005 09:08:53 +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 37277-05 for ; Mon, 14 Mar 2005 09:08:49 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 63AAF5638A for ; Mon, 14 Mar 2005 09:08:47 +0000 (GMT) Received: (qmail 23861 invoked from network); 14 Mar 2005 10:08:53 +0100 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 14 Mar 2005 10:08:53 +0100 Date: Mon, 14 Mar 2005 10:08:48 +0100 To: =?iso-8859-15?B?TWlyb3NsYXYgpnVsYw==?= , "John Arbash Meinel" Subject: Re: How to read query plan Cc: "PGSQL mailing list" 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> From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <42355249.8090101@startnet.cz> User-Agent: Opera M2/7.54 (Linux, build 955) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/253 X-Sequence-Number: 10958 Instead of a varchar(1) containing 'y' or 'n' you could use a BOOL or an integer. 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. On Mon, 14 Mar 2005 09:58:49 +0100, Miroslav Ķulc wrote: > John Arbash Meinel wrote: > >>> In fact, on MySQL I didn't see any slow reactions so I didn't measure >>> and inspect it. But I can try it if I figure out how to copy the >>> database from PostgreSQL to MySQL. >> >> >> I figured you still had a copy of the MySQL around to compare to. You >> probably don't need to spend too much time on it yet. > > So I have some results. I have tested the query on both PostgreSQL 8.0.1 > and MySQL 4.1.8 with LIMIT set to 30 and OFFSET set to 6000. PostgreSQL > result is 11,667.916 ms, MySQL result is 448.4 ms. > > Both databases are running on the same machine (my laptop) and contain > the same data. However there are some differences in the data table > definitions: > 1) in PostgreSQL I use 'varchar(1)' for a lot of fields and in MySQL I > use 'enum' > 2) in PostgreSQL in some cases I use connection fields that are not of > the same type (smallint <-> integer (SERIAL)), in MySQL I use the same > types > >> >> John >> =:-> > > Miroslav