Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 788F12E00E1 for ; Sat, 23 Feb 2008 03:58:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 53382-09 for ; Sat, 23 Feb 2008 03:58:49 -0400 (AST) X-Greylist: delayed 00:05:02.318985 by SQLgrey-1.7.5 Received: from ipmail01.adl6.internode.on.net (ipmail01.adl6.internode.on.net [203.16.214.146]) by postgresql.org (Postfix) with ESMTP id 85FC72E00C1 for ; Sat, 23 Feb 2008 03:58:52 -0400 (AST) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ah4FAH5ev0fLevdH/2dsb2JhbAAsgSmsAg X-IronPort-AV: E=Sophos;i="4.25,394,1199626200"; d="scan'208";a="47723702" Received: from ppp247-71.static.internode.on.net (HELO DevBox.local) ([203.122.247.71]) by ipmail01.adl6.internode.on.net with ESMTP; 23 Feb 2008 18:23:45 +1030 Message-ID: <47BFD107.8050007@Sheeky.Biz> Date: Sat, 23 Feb 2008 18:23:43 +1030 From: Shane Ambler User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728) MIME-Version: 1.0 To: Matthew CC: pgsql-performance@postgresql.org Subject: Re: store A LOT of 3-tuples for comparisons References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/290 X-Sequence-Number: 28832 Matthew wrote: > On Fri, 22 Feb 2008, Moritz Onken wrote: >> I thought of doing all the inserts without having an index and without >> doing the check whether the row is already there. After that I'd do a >> "group by" and count(*) on that table. Is this a good idea? > > That sounds like the fastest way to do it, certainly. Yeah I would load the data into a temp 3-column table and then INSERT INTO mydatatable SELECT w1,w2,w3,count(*) GROUP BY w1,w2,w3 then CREATE UNIQUE INDEX idx_unique_data ON mydatatable (w1,w2,w3) if you plan to continue adding to and using the data. If this is to be an ongoing data collection (with data being added slowly from here) I would probably setup a trigger to update the count column. I am also wondering about the ordering and whether that matters. Can you have "he", "can", "drink" as well as "drink", "he", "can" and should they be considered the same? If so you will need a different tactic. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz