X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8B5E732A0E4 for ; Fri, 1 Oct 2004 21:48:47 +0100 (BST) 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 53102-07 for ; Fri, 1 Oct 2004 20:48:44 +0000 (GMT) Received: from pop.sanbrunocable.com (pop.sanbrunocable.com [65.172.230.120]) by svr1.postgresql.org (Postfix) with ESMTP id E97B832A0D7 for ; Fri, 1 Oct 2004 21:48:42 +0100 (BST) Received: from oss4u.com (cpe-65-172-234-54.sanbrunocable.com [65.172.234.54]) by pop.sanbrunocable.com (8.11.6/8.11.6) with SMTP id i91KlNG14327 for ; Fri, 1 Oct 2004 13:47:23 -0700 Received: (qmail 5822 invoked from network); 1 Oct 2004 20:43:48 -0000 Received: from tiburon.oss4u.com (192.168.1.4) by gw.oss4u.com (192.168.1.1) with ESMTP; 01 Oct 2004 20:43:48 -0000 From: "Uwe C. Schroeder" Organization: OSS4U, LLC To: "Shane | SkinnyCorp" , "PgSQL ADMIN" Subject: Re: PLEASE GOD HELP US! Date: Fri, 1 Oct 2004 13:48:41 -0700 User-Agent: KMail/1.5.3 References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> In-Reply-To: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200410011348.41695.uwe@oss4u.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/30 X-Sequence-Number: 15024 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Friday 01 October 2004 01:26 pm, Shane | SkinnyCorp wrote: > Okay, just so no one posts about this again... > > the 'ORDER BY t.status=3D5,lastreply' clause is meant to float the threads > with a status of '5' to the top of the list... it is NOT meant to only gr= ab > threads where the status =3D 5. Oh and believe me, when I take this out = of > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > millesecond to the speed of the SELECT statement. > One quick idea that comes to my head without knowing your database structur= e=20 (which would be neccessary to do this properly) is: use a serial field as row key (int4 with a sequence as default) in your query grab the current value from the sequence, subtract 25 and sel= ect=20 all records with a key larger than that number. Et voila: you only select 25 records with a where clause that will use an= =20 index properly. I bet that runs in a couple of milliseconds. BTW: You're asking and complaining about a "lagging database". You think=20 nothing you did is at fault. Why then ask in the first place if you don't= =20 want to hear the answers? I've been working with databases long before postgres was born (over 23=20 years). I still ask questions on this list and usually get good responses.= =20 UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBXcKpjqGXBvRToM4RAv4YAJ9cj/P+vCbbJXNf1G5Dh3dWnZzkDQCgqdfa lxicqf7qRS6hA7Zzv0v94/U=3D =3De94/ -----END PGP SIGNATURE-----