X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 235E2D1B53C for ; Wed, 11 Feb 2004 22:38:50 +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 40871-07 for ; Wed, 11 Feb 2004 18:38:44 -0400 (AST) Received: from busybox.pixar.com (busybox.pixar.com [138.72.18.213]) by svr1.postgresql.org (Postfix) with ESMTP id 98F67D1B4D2 for ; Wed, 11 Feb 2004 18:38:43 -0400 (AST) Received: from pixar.com (dreadnok.pixar.com [138.72.16.110]) by busybox.pixar.com (8.12.8/8.12.8) with ESMTP id i1BMcfHw002912 for ; Wed, 11 Feb 2004 14:38:41 -0800 Message-ID: <402AAEF1.2050703@pixar.com> Date: Wed, 11 Feb 2004 14:38:41 -0800 From: Mark Harrison User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: coercing int to bigint for indexing purposes Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/141 X-Sequence-Number: 5665 Is there a way to automatically coerce an int into a bigint for indexing purposes? We have a table with a bigint column that is an index. For mi, there's no problem, since I now know to say select * from foo where id = 123::bigint but our casual users still say select * from foo where id = 123 causing a sequential scan because the type of 123 is not a bigint. As you can see, there's nearly 4 orders of magnitude difference in time, and we anticipate this will only get worse as our tables increase in size: LOG: duration: 0.861 ms statement: select * from big where id = 123123123123123; LOG: duration: 6376.917 ms statement: select * from big where id = 123; One thing I have considered is starting our id sequence at 5000000000 so that "real" queries will always be bigint-sized, but this seems to me a bit of a hack. Many TIA, Mark -- Mark Harrison Pixar Animation Studios