Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EA4029FBEF3 for ; Fri, 16 Mar 2007 12:29:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 84773-06 for ; Fri, 16 Mar 2007 12:28:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id F219F9FBEEC for ; Fri, 16 Mar 2007 12:28:51 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so642936ugf for ; Fri, 16 Mar 2007 08:28:50 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=OMSzK/fc68Drw3AArk1g88jDsqccDOvR18Ewp0SwYdwEp3+BK5TrLisdIco0W+lbNbdAwYKb6XWyvyLDO+BwAyV99KwzFo0r+WD/f3hk2tRuMKYazzeNyI1IBmL/YXJ3MJT1SG7HsTHDCVA8Qy/nL2gCrPKYMEBeYWxbBpzNzrA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=X2CxkNkO6roKR7p/u2wCqdWvtaUYhM5dl+dVbJzBwUYNCby0jFsEQ2YSHv8kYygHIEarFR+2yX1aYRuK1J/ZnQ8wZV6SeGZdsUbiQZxSHd3bBez4XRQjn5y4gYyxF9AtEYxaBPwzWyzAimKtls2ZKjNdYqla1IYyY98xD6NqlHY= Received: by 10.114.113.1 with SMTP id l1mr770024wac.1174058928691; Fri, 16 Mar 2007 08:28:48 -0700 (PDT) Received: by 10.114.171.9 with HTTP; Fri, 16 Mar 2007 08:28:48 -0700 (PDT) Message-ID: Date: Fri, 16 Mar 2007 10:28:48 -0500 From: "Travis Whitton" To: pgsql-sql@postgresql.org Subject: Grabbing Newest Records From Duplicates MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_96925_9649540.1174058928636" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/228 X-Sequence-Number: 27866 ------=_Part_96925_9649540.1174058928636 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Given the following test table, I want to grab only the newest record and disregard any older duplicates based on name. Is this the most efficient way to do it? Will the indicies even make a difference? The table below demonstrates a simple proof of concept. My final table will have millions of records; however, the test I'm doing now does not seem to use the indicies (probably because there are too few records?). optin=# \d test Table "public.test" Column | Type | Modifiers --------+---------+----------- name | text | time | date | id | integer | Indexes: "idx_name" btree (name) "idx_time" btree ("time") optin=# explain select * from test t1 where not exists (select 1 from test t2 where t2.name = t1.name and t2.time > t1.time); Thanks, Travis ------=_Part_96925_9649540.1174058928636 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Given the following test table, I want to grab only the newest record and disregard any older duplicates based on name. Is this the most efficient way to do it? Will the indicies even make a difference? The table below demonstrates a simple proof of concept. My final table will have millions of records; however, the test I'm doing now does not seem to use the indicies (probably because there are too few records?).

optin=# \d test
     Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 name   | text    |
 time   | date    |
 id     | integer |
Indexes:
    "idx_name" btree (name)
    "idx_time" btree ("time")

optin=# explain select * from test t1 where not exists (select 1 from test t2 where t2.name = t1.name and t2.time > t1.time);

Thanks,
Travis

------=_Part_96925_9649540.1174058928636--