Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f83am-0007Y7-RB for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Apr 2018 12:50:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f83al-0003OB-FY for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Apr 2018 12:50:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f83al-0003O2-34 for pgsql-hackers@lists.postgresql.org; Mon, 16 Apr 2018 12:50:43 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f83ah-0004jy-EQ for pgsql-hackers@lists.postgresql.org; Mon, 16 Apr 2018 12:50:42 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id AA32121C1E3D; Mon, 16 Apr 2018 15:50:37 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from wp.localdomain (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 7816221C1E35; Mon, 16 Apr 2018 15:50:37 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1523883037; bh=1PkDtI1Vb99NLNJbHRzKtidvyw72Bcb/5mfNsN5b0w0=; h=Date:From:To:Cc:Subject:In-Reply-To:References; b=Ii6IjVWmJLrBtVFaWTGlyh0jNOmJCVAIsFVFh+aWAaBUREy12ikR/NtFh9S/XCL3e SK+MsLoJPkbODkHTgtzDsBxx6bKcx0ZH8NJ2X47JubKtqieOoSeCTnyaV70i62I8lq zvctneOBaLj3edCz0HIiNWPm73YW/mGnTga6S+TQ= Date: Mon, 16 Apr 2018 15:50:36 +0300 From: Ildus Kurbangaliev To: Emre Hasegeli Cc: Teodor Sigaev , Alexander Korotkov , Arthur Zakirov , PostgreSQL Hackers Subject: Re: Prefix operator for text and spgist support Message-ID: <20180416155036.36070396@wp.localdomain> In-Reply-To: References: <20180202180327.222b04b3@wp.localdomain> <20180219120650.GA2142@zakirov.localdomain> <20180219171915.65821d58@wp.localdomain> <20180306162720.GA21305@zakirov.localdomain> <20180312143611.67be88bc@wp.localdomain> <20180329143634.33c53348@wp.localdomain> <03300255-cff2-b508-50f4-f00cca0a57a1@sigaev.ru> Organization: Postgres Professional X-Mailer: Claws Mail 3.16.0 (GTK+ 2.24.32; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Mon, 16 Apr 2018 12:45:23 +0200 Emre Hasegeli wrote: > > Thank you, pushed with some editorization and renaming > > text_startswith to starts_with > > I am sorry for not noticing this before, but what is the point of this > operator? It seems to me we are only making the prefix searching > business, which is already complicated, more complicated. Hi. > > Also, the new operator is not documented on SQL String Functions and > Operators table. It is not supported by btree text_pattern_ops or > btree indexes with COLLATE "C". It is not defined for "citext", so > people would get wrong results. It doesn't use pg_trgm indexes > whereas LIKE can. It is mentioned in documentation, look for "starts_with" function. Currently it's working with spgist indexes which fact is pointed out in the documentation too. I was going to add btree support but it would require a new strategy so it will be matter of another patch. I think this operator could be used in LIKE instead of current weird comparison operators. -- --- Ildus Kurbangaliev Postgres Professional: http://www.postgrespro.com Russian Postgres Company