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 0C34052AAA for ; Thu, 3 Mar 2005 21:37:37 +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 04256-09 for ; Thu, 3 Mar 2005 21:37:28 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 09B8152A4D for ; Thu, 3 Mar 2005 21:37:27 +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 j23LbNtu004675; (envelope-from ) Thu, 3 Mar 2005 15:37:23 -0600 (CST) Received: from [192.168.1.13] (63-230-158-178.cdrr.qwest.net [63.230.158.178]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j23LbLqu022107 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Thu, 3 Mar 2005 15:37:22 -0600 (CST) Message-ID: <42278391.7090509@arbash-meinel.com> Date: Thu, 03 Mar 2005 15:37:21 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jeremiah Jahn Cc: postgres performance Subject: Re: name search query speed References: <1109867909.16326.58.camel@bluejay.goodinassociates.com> <42274D5A.1070402@arbash-meinel.com> <1109880887.16326.86.camel@bluejay.goodinassociates.com> In-Reply-To: <1109880887.16326.86.camel@bluejay.goodinassociates.com> 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="------------enigE590B28BECAE19068497DD61" 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 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/65 X-Sequence-Number: 10770 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigE590B28BECAE19068497DD61 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jeremiah Jahn wrote: >On Thu, 2005-03-03 at 11:46 -0600, John A Meinel wrote: > > ... >Not really, about 2% of the returned rows are thrown away for security >reasons based on the current user, security groups they belong to and >different flags in the data itself. So the count for this is generated >on the fly needed for pagination in the app which expresses the total >number of finds, but only displays 40 of them. If any one knows a way to >determine the total number of matches without needing to iterate through >them using jdbc, I'm all ears as this would save me huge amounts of time >and limit/offset would become an option. > > > Well, what is wrong with "select count(*) from "? Are you saying 2% are thrown away, or only 2% are kept? Is this being done at the client side? Is there a way to incorporate the security info into the database, so that the query actually only returns the rows you care about? That seems like it would be a decent way to speed it up, if you can restrict the number of rows that it needs to look at. There are other alternatives, such as materialized views, or temp tables, where you select into the temp table the rows that the user would request, and then you generate limit/offset from that. The first query would be a little slow, since it would get all the rows, but all subsequent accesses for that user could be really fast. The other possibility is to do "limit 200", and then in your list of pages, you could have: 1, 2, 3, 4, 5, ... This means that you don't have to worry about getting 10,000 entries, which probably isn't really useful for the user anyway, and you can still break things into 40 entry pages, just 200 entries at a time. John =:-> >>John >>=:-> >> >> >> --------------enigE590B28BECAE19068497DD61 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 iD8DBQFCJ4ORJdeBCYSNAAMRAhn+AKC5RPu0LT6SN3ICzLkad5ycwmgCzgCfSg/Y DmmFtAMtK+kDBc6RIdwFbnY= =ghHh -----END PGP SIGNATURE----- --------------enigE590B28BECAE19068497DD61--