X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 535F654DA7 for ; Sun, 13 Mar 2005 06:26:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32231-06 for ; Sun, 13 Mar 2005 06:26:12 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8081754E05 for ; Sun, 13 Mar 2005 06:26:11 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2D6QCLg014750; Sun, 13 Mar 2005 01:26:12 -0500 (EST) To: Russell Smith Cc: "Tom Pfeifer" , pgsql-performance@postgresql.org Subject: Re: Index use and slow queries In-reply-to: <200503131707.46690.mr-russ@pws.com.au> References: <20050313051830.M98712@tela.com> <200503131707.46690.mr-russ@pws.com.au> Comments: In-reply-to Russell Smith message dated "Sun, 13 Mar 2005 17:07:46 +1100" Date: Sun, 13 Mar 2005 01:26:12 -0500 Message-ID: <14749.1110695172@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/215 X-Sequence-Number: 10920 Russell Smith writes: > On Sun, 13 Mar 2005 04:40 pm, Tom Pfeifer wrote: >> I get really slow repoonse times when using the following select statement (About 20 seconds). >> maach=# explain select * from tst where tst_id = 639246; > Before 8.0, bigint would not use an index unless you cast it, or quote it. > explain select * from tst where tst_id = 639246::int8; > explain select * from tst where tst_id = '639246'; ... or you compare to a value large enough to be int8 naturally, eg > explain select * from tst where tst_id = 123456639246; The issue here is that (a) 639246 is naturally typed as int4, and (b) before 8.0 we couldn't use cross-type comparisons such as int8 = int4 with an index. You can find a whole lot of angst about this issue and related ones if you care to review the last six or eight years of the pgsql-hackers archives. It was only recently that we found a way to support cross-type index operations without breaking the fundamental type-extensibility features of Postgres. (In hindsight, we spent way too much time fixated on the notion that we needed to find a way to implicitly convert the non-indexed value to match the indexed column's type, rather than biting the bullet and supporting cross-type operations directly with indexes. Oh well, hindsight is always 20/20.) regards, tom lane