DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=AkwqVIQAKVQb/YWv3VfrEbz9ahaGXpQ/D5Ez0cR3gPBYJDjUfH5iCGuHneLntKGYVZpdKMThGVMZtTCYSbPD6yBwSKnTD0VKvk1+jIkdJZuJ2bcdh4rqbsPOL0y6Zwm639Hq5yBknzT83Mh7lF2x5oudG+GjOkJBq2KEjEOhgRU=
Message-ID: <452E83D0.5060309@gmail.com>
Date: Thu, 12 Oct 2006 12:05:04 -0600
From: Brendan Curran <brendan.curran@gmail.com>
Reply-To: brendan.curran@gmail.com
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: "Jim C. Nasby" <jim@nasby.net>
CC: g@nasby.net, Tom Lane <tgl@sss.pgh.pa.us>,
	Pg Performance list <pgsql-performance@postgresql.org>
Subject: Re: Scrub one large table against another
References: <452C1437.9040709@gmail.com> <13451.1160518445@sss.pgh.pa.us>
	<452C208C.5010603@gmail.com> <13912.1160520427@sss.pgh.pa.us>
	<452C30CA.2070801@gmail.com> <15751.1160535019@sss.pgh.pa.us>
	<452D2195.8010907@gmail.com> <20061011205251.GR13487@nasby.net>
In-Reply-To: <20061011205251.GR13487@nasby.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> Well, IN != EXISTS != JOIN. Exists just stops as soon as it finds a
> record. For some cases, it's equivalent to IN, but not all. IN has to
> de-duplicate it's list in some fashion. For small IN lists, you can do
> this with an OR, but at some point you need to switch to an actual
> unique (actually, I suspect the difference in PostgreSQL just depends on
> if you passed values into IN or a subquery). A join on the other hand
> doesn't worry about duplicates at all. There may be some brains in the
> planner that realize if a subquery will return a unique set (ie: you're
> querying on a primary key).
> 

I agree, and it makes sense now that I consider it that IN would force the planner to implement some 
form of unique check - possibly leveraging a PK or unique index if one is already available. Maybe 
I'll tack up a note to the online documentation letting people know so that it's a little more 
explicitly clear that when you choose IN on data that isn't explicitly unique (to the planner i.e. 
post-analyze) you get the baggage of a forced unique whether you need it or not. Or perhaps someone 
that knows the internals of the planner a little better than me should put some info up regarding that?

> 
>> Just one more thing... I have found that maintaining a btree index on a 
>> varchar(255) value is extremely expensive on insert/update/delete. It is 
>> unfortunately necessary for me to maintain this index for queries and 
>> reports so I am transitioning to using an unindexed staging table to 
>> import data into before merging it with the larger table. All the docs 
>> and posts recommend is to drop the index, import your data, and then 
>> create the index again. This is untenable on a daily / bi-weekly basis. 
>> Is there a more elegant solution to this indexing problem?
> 
> You might be happier with tsearch than a regular index.

Thanks, I'll look into using tsearch2 as a possibility. From what I've seen so far it would add 
quite a bit of complexity (necessary updates after inserts, proprietary query syntax that might 
require a large amount of specialization from client apps) but in the end the overhead may be less 
than that of maintaining the btree.

Thanks and Regards,
B