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 BBB4F5638A for ; Mon, 14 Mar 2005 08:58:44 +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 33115-09 for ; Mon, 14 Mar 2005 08:58:42 +0000 (GMT) Received: from sranda.klamovka.kosire.czf (unknown [82.113.63.66]) by svr1.postgresql.org (Postfix) with ESMTP id F1A99563CD for ; Mon, 14 Mar 2005 08:58:40 +0000 (GMT) Received: (qmail 28479 invoked from network); 14 Mar 2005 09:58:42 +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:58:42 +0100 Message-ID: <42355249.8090101@startnet.cz> Date: Mon, 14 Mar 2005 09:58:49 +0100 From: =?ISO-8859-2?Q?Miroslav_=A9ulc?= Organization: StartNet s.r.o. User-Agent: Mozilla Thunderbird 1.0 (X11/20041208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: John Arbash Meinel Cc: 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> In-Reply-To: <423493E5.6040203@arbash-meinel.com> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/mixed; boundary="------------060203020203020207060708" X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/251 X-Sequence-Number: 10956 This is a multi-part message in MIME format. --------------060203020203020207060708 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit 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 --------------060203020203020207060708 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 --------------060203020203020207060708--