From: Shane Ambler <pgsql@Sheeky.Biz>
To: Matthew <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: store A LOT of 3-tuples for comparisons
Date: Sat, 23 Feb 2008 18:23:43 +1030
Message-ID: <47BFD107.8050007@Sheeky.Biz> (raw)
In-Reply-To: <Pine.LNX.4.64.0802221546370.20402@aragorn.flymine.org>
References: <D35BC72A-6E10-43C8-A376-BBFD78BE7E4B@houseofdesign.de>
<Pine.LNX.4.64.0802221546370.20402@aragorn.flymine.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql@Sheeky.Biz, matthew@flymine.org
Subject: Re: store A LOT of 3-tuples for comparisons
In-Reply-To: <47BFD107.8050007@Sheeky.Biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox