X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5EC36D1B442 for ; Sat, 6 Dec 2003 19:54:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 68107-04 for ; Sat, 6 Dec 2003 15:54:06 -0400 (AST) Received: from bob.samurai.com (bob.samurai.com [205.207.28.75]) by svr1.postgresql.org (Postfix) with ESMTP id 86D96D1B468 for ; Sat, 6 Dec 2003 15:54:04 -0400 (AST) Received: from tokyo.samurai.com (d226-89-59.home.cgocable.net [24.226.89.59]) by bob.samurai.com (Postfix) with ESMTP id 7C0281EBB; Sat, 6 Dec 2003 14:54:04 -0500 (EST) To: Shridhar Daithankar Cc: Ivar Zarans , pgsql-performance@postgresql.org Subject: Re: Slow UPADTE, compared to INSERT From: Neil Conway In-Reply-To: <3FD08D6A.4080207@persistent.co.in> (Shridhar Daithankar's message of "Fri, 05 Dec 2003 19:21:38 +0530") References: <20031204185751.GA15893@alcaron.ee> <20031205014514.GA21079@alcaron.ee> <20031205020728.GA21440@alcaron.ee> <200312051008.20317.dev@archonet.com> <20031205123843.GA27170@alcaron.ee> <3FD07EEA.8030007@persistent.co.in> <20031205131325.GB27524@alcaron.ee> <3FD08D6A.4080207@persistent.co.in> Date: Sat, 06 Dec 2003 14:54:03 -0500 Message-ID: <871xrh90ys.fsf@mailbox.samurai.com> User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/143 X-Sequence-Number: 5003 Shridhar Daithankar writes: > This is not a bug. It is just that people find it confusing when > postgresql planner consider seemingly same type as different. It certainly is a bug, or at least a deficiency: PostgreSQL planner *could* use the index to process the query, but the planner doesn't consider doing so. The fact that it isn't able to do the necessary type coercion is the *cause* of the bug, not a defence for this behavior. > AFAIK, the fix in CVS is to make indexes operatable with seemingly > compatible types. Which does not change the fact that postgresql can > not upgrade data types on it's own. I'm not sure what you mean by that. In any case, I just checked, and it does seem Tom has fixed this in CVS: template1=# create table abc (b int8); CREATE TABLE template1=# set enable_seqscan = false; SET template1=# create index abc_b_idx on abc (b); CREATE INDEX template1=# explain select * from abc where b = 4; QUERY PLAN ---------------------------------------------------------------------- Index Scan using abc_b_idx on abc (cost=0.00..17.07 rows=5 width=8) Index Cond: (b = 4) (2 rows) Cool! -Neil