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 ED8A356521 for ; Sun, 13 Mar 2005 19:26:36 +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 09754-05 for ; Sun, 13 Mar 2005 19:26:35 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id E2FA6563A5 for ; Sun, 13 Mar 2005 19:26:34 +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 j2DJQZr7012944; (envelope-from ) Sun, 13 Mar 2005 13:26:35 -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 j2DJQX5a002875 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Sun, 13 Mar 2005 13:26:34 -0600 (CST) Message-ID: <423493E5.6040203@arbash-meinel.com> Date: Sun, 13 Mar 2005 13:26: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: =?ISO-8859-2?Q?Miroslav_=A9ulc?= 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> In-Reply-To: <42348F54.1000408@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="------------enig93FAA8B7BBEF58F5248439E7" 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.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/227 X-Sequence-Number: 10932 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig93FAA8B7BBEF58F5248439E7 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit Miroslav Šulc wrote: > John Arbash Meinel wrote: ... > Many of the columns are just varchar(1) (because of the migration from > MySQL enum field type) so the record is not so long as it could seem. > These fields are just switches (Y(es) or N(o)). The problem is users > can define their own templates and in different scenarios there might > be displayed different information so reducing the number of fields > would mean in some cases it wouldn't work as expected. But if we > couldn't speed the query up, we will try to improve it other way. > Is there any serious reason not to use so much fields except memory > usage? It seems to me that it shouldn't have a great impact on the > speed in this case. Is there a reason to use varchar(1) instead of char(1). There probably is 0 performance difference, I'm just curious. > >> Have you thought about using a cursor instead of using limit + offset? >> This may not help the overall time, but it might let you split up when >> the time is spent. >> ...... > > > No. I come from MySQL world where these things are not common (at > least when using MyISAM databases). The other reason (if I understand > it well) is that the retrieval of the packages of 30 records is not > sequential. Our app is web based and we use paging. User can select > page 1 and then page 10, then go backward to page 9 etc. > Well, with cursors you can also do "FETCH ABSOLUTE 1 FROM ", which sets the cursor position, and then you can "FETCH FORWARD 30". I honestly don't know how the performance will be, but it is something that you could try. >> And if I understand correctly, you consider all of these to be outer >> joins. Meaning you want *all* of AdDevicesSites, and whatever info goes >> along with it, but there are no restrictions as to what rows you want. >> You want everything you can get. >> >> Do you actually need *everything*? You mention only needing 30, what >> for? > > > For display of single page consisting of 30 rows. The reason I query > all rows is that this is one of the filters users can use. User can > display just bigboards or billboards (or specify more advanced > filters) but he/she can also display AdDevices without any filter > (page by page). Before I select the 30 row, I need to order them by a > key and after that select the records, so this is also the reason why > to ask for all rows. The key for sorting might be different for each run. > How are you caching the information in the background in order to support paging? Since you aren't using limit/offset, and you don't seem to be creating a temporary table, I assume you have a layer inbetween the web server and the database (or possibly inside the webserver) which keeps track of current session information. Is that true? > These might be the other steps in case we cannot speed-up the query. I > would prefer to speed the query up :-) Naturally fast query comes first. I just have the feeling it is either a postgres configuration problem, or an intrinsic problem to postgres. Given your constraints, there's not much that we can change about the query itself. > 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. John =:-> --------------enig93FAA8B7BBEF58F5248439E7 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 iD8DBQFCNJPpJdeBCYSNAAMRAkhzAJ42BdbB9zZAw4Pzm22DdlDe89Aw/gCgs7PD i3k8saCxAiCYvpoujPJzO3E= =o/ya -----END PGP SIGNATURE----- --------------enig93FAA8B7BBEF58F5248439E7--