Received: from fallon.classyad.com (IDENT:root@classyad.com [152.160.43.1]) by hub.org (8.9.1/8.9.1) with ESMTP id KAA05388 for ; Sat, 14 Nov 1998 10:04:16 -0500 (EST) (envelope-from marc@fallon.classyad.com) Received: from localhost (IDENT:marc@localhost [127.0.0.1] (may be forged)) by fallon.classyad.com (8.9.0/8.9.0) with SMTP id KAA06683; Sat, 14 Nov 1998 10:01:42 -0500 Date: Sat, 14 Nov 1998 10:01:41 -0500 (EST) From: Marc Howard Zuckman To: Thomas Good cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] abusing an aggregate funct In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII On Sat, 14 Nov 1998, Thomas Good wrote: > Due to the sheer girth of the table the following query, called from > a data entry script, takes a bit of time to finish: > > SELECT max(rec_num) FROM crtrd1; > > I use this to setup `rec_num + 1' for my query number (p_key)...any > way I can speed this up a bit? I've run vacuum analyze but the > table size is doing me in... > Use a sequence to generate rec_num. See man create_sequence. You can then use curval('sequence_name') to get the last sequence number generated, or if you are performing an insert: insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata); You could also create the table using nextval as the default value of p_key. You may wish to create a unique_index on p_key, but nextval is guaranteed not to generate duplicates unless you allow it to rollover. Marc Zuckman marc@fallon.classyad.com _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ _ Visit The Home and Condo MarketPlace _ _ http://www.ClassyAd.com _ _ _ _ FREE basic property listings/advertisements and searches. _ _ _ _ Try our premium, yet inexpensive services for a real _ _ selling or buying edge! _ _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_