X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 29738D1E136 for ; Wed, 25 Feb 2004 21:32:15 +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 44104-02 for ; Wed, 25 Feb 2004 17:32:14 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 2FECBD1E0DD for ; Wed, 25 Feb 2004 17:32:11 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1PLW8a3006804; Wed, 25 Feb 2004 16:32:08 -0500 (EST) To: Richard Huxton Cc: pgsql-sql@postgresql.org Subject: Re: Scalar in a range (but textual not numeric) In-reply-to: <200402251918.20289.dev@archonet.com> References: <200402251918.20289.dev@archonet.com> Comments: In-reply-to Richard Huxton message dated "Wed, 25 Feb 2004 19:18:20 +0000" Date: Wed, 25 Feb 2004 16:32:08 -0500 Message-ID: <6803.1077744728@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/349 X-Sequence-Number: 17039 Richard Huxton writes: > Large table representing non-overlapping blocks: > blocks(id int4, min varchar, max varchar) > SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max; > The estimator gets the wrong plan because it doesn't realise there's > (at most) only one block that can match. Even if it did realize that, it couldn't do much, because this query isn't indexable as it stands. I wonder whether you could adapt the "line segment" datatype (see contrib/seg/) into a sort of "text segment" thingy and use the GiST indexing support on that. You'd have a query like WHERE min_max_object overlaps-operator 'ABCDE' and the overlaps operator would be a GiST-indexable one. regards, tom lane