Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lvpFC-0004qZ-6o for pgsql-docs@arkaria.postgresql.org; Tue, 22 Jun 2021 22:51:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lvpFB-0005V9-62 for pgsql-docs@arkaria.postgresql.org; Tue, 22 Jun 2021 22:51:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lvpFA-0005V0-UC for pgsql-docs@lists.postgresql.org; Tue, 22 Jun 2021 22:51:45 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lvpF8-0000Ov-Ua for pgsql-docs@lists.postgresql.org; Tue, 22 Jun 2021 22:51:43 +0000 Received: by mail-pj1-x1030.google.com with SMTP id 22-20020a17090a0c16b0290164a5354ad0so2626513pjs.2 for ; Tue, 22 Jun 2021 15:51:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=iM9KIZ5uT/Ipe3AleLyrs+RC599IAt2NbLqLOXnKw5I=; b=SnncIDohR5uGSEO4IkmsXnkPhHK/S/2sL69NFaSvzdW/gLZgL4FNtT3VswBNTma9YA 5ELO6RNibIicPVydEOY3tUPPvb+zZvc58DYBw4OK9IGN2gfz544b8x3GAwX6uNGQ7flz /Qv8pTNybGT/5Ry4hY6yOibi73TvwytiFYJGCygyjBxjOkaK/pRqHuCFQSgK+lx/NGV5 9lpy/Dhtdc6ePK2L0vgclkOq+FS+5pQeGApTtw81ynW7m+kqwSGh15r539VLmd+I41qh UM14Xlx4WGSyY1l5XRpmSlrMhB5mS1H5sFEySi6XPL0o7jskqJ0URe1HxfklO/S+9dxP KAVA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=iM9KIZ5uT/Ipe3AleLyrs+RC599IAt2NbLqLOXnKw5I=; b=VXQASX24GdXxoAnYQtvHxpOUjvJ7eVZF6vKxVWEB4YVJfb0+ROcj/VPVcBRhk/Mb6k +L9wt2miDT5fyA0imp+AmIsOU5IECR22iEF63kdIlEtwj25B8R6SlC6YdzyVg9yDiIDe 9pL8eLpx7B2bsbQO+XvdxyidvPItTNfZgG+kCQ2MH2paMJmYrewmiE8eyldSFLFwvXk5 51vJ8MXhVTUlWBJrriXmsbKcItwWYUU3Q9fG6W/Pjshz8u6xDjuBnh+oucy8xEK3bb1t 9VtLyoub/+UmX8ArHTiutBZ74fBrTreGRaRNrk5XzcWmQ0PdHX0O1y5V3KIzTtHk1qDo R5YQ== X-Gm-Message-State: AOAM531c+1C+oJiHKqtVZvsRmShNuZAWNdTihrpiimfHxV7+WqAUpdkS wEq21HiIaJVmehlyiyGCixGKbgKsT+J6zv/HF8U= X-Google-Smtp-Source: ABdhPJwEoonCDTQ2NfPalwyU9Uw9F/N/rFm2iHE5eUMHFhV2/hy00evDvJbctMnLHvBp3rKrNy1y/oaQvSaDwdxHLg0= X-Received: by 2002:a17:90b:3a8f:: with SMTP id om15mr6191383pjb.222.1624402301619; Tue, 22 Jun 2021 15:51:41 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 22 Jun 2021 15:51:23 -0700 Message-ID: Subject: Re: Reverse btree indexes To: Zach Aysan Cc: Pg Docs Content-Type: multipart/alternative; boundary="00000000000029120c05c562a296" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000029120c05c562a296 Content-Type: text/plain; charset="UTF-8" On Tue, Jun 22, 2021 at 2:28 PM Zach Aysan wrote: > *Desired improvement:* > > for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar', >> which would require a reversed index on the field. Postgres will >> automatically use the reverse index for LIKE '%bar' > > > *Or if it doesn't:* > It doesn't, otherwise the documentation wouldn't need to point out: but not col LIKE '%bar' ... > >> for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar', >> which would require a reversed index on the field. To use the reversed >> index, query with reverse(col) like reverse('%bar'). > > > This type of commentary isn't usually something we include in the documentation...and I'm not too keen on "reversed index" as a phrase regardless. David J. --00000000000029120c05c562a296 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Jun 22, 2021 at 2:28 PM Zach Aysan <zachaysan@gmail.com> wrote:
Desired improvement:

=C2=A0for exam= ple, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '= ;%bar', which would require a reversed index on the field. Postgres wil= l automatically use the reverse index for LIKE '%bar'
<= div>
Or if it doesn't:

It doesn't, otherwise the documentation wouldn'= t need to point out: but not col LIKE '%bar' ...
=
=C2=A0
for exam= ple, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '= ;%bar', which would require a reversed index on the field. To use the r= eversed index, query with=C2=A0reverse(col) like reverse('%bar').


This type of commentary isn't usually something we include in the = documentation...and I'm not too keen on "reversed index" as a= phrase regardless.

David J.

<= /div> --00000000000029120c05c562a296--