Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1to9dh-006w1U-Ew for pgsql-performance@arkaria.postgresql.org; Fri, 28 Feb 2025 23:19:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1to9di-000js4-8d for pgsql-performance@arkaria.postgresql.org; Fri, 28 Feb 2025 23:19:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1to9dh-000jru-V3 for pgsql-performance@lists.postgresql.org; Fri, 28 Feb 2025 23:19:28 +0000 Received: from mail-wr1-f49.google.com ([209.85.221.49]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1to9dc-000GEe-37 for pgsql-performance@postgresql.org; Fri, 28 Feb 2025 23:19:28 +0000 Received: by mail-wr1-f49.google.com with SMTP id ffacd0b85a97d-390effd3e85so1188059f8f.0 for ; Fri, 28 Feb 2025 15:19:26 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740784765; x=1741389565; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=PHGDk0i85pCa/zmQ3PNHyqZbN1q6ac3gSGKtLcCoKQA=; b=RCH/1nyb2bwSGMlzEtK4AgM0OKMg3TCAiZtSN58p82P9rLHcSk1XAdlBX2zop85aoD cWXvLBB2Bv6l9D5x/3vmdD6E12O9AHV+OWl6K7L+litna+NzVtSWIxUa2vVu+//jOkGt 1oMrrZ5EYlS/PTAK/oz6rD6lttQBP8oMZcniX6XRQp5bTeN2Uu4TRa0WA6ffaZ3iXgyV PU9VOspQ321gx7aImK6PPqdjUYe7I2P37hX3Isf2MrLBJuwYzVTbNlNsCpUdvH2tta6d m//2g+FYFCm7yzsfRU4EbxBJEh0TbSPItVft1LZYxlIhrQsBa7g3VCX9t7D7Zwjqj+uC BBaA== X-Gm-Message-State: AOJu0YyqDKIk/4KrPrsGm+7kxiDfgywq1LW6ow9ni/pAZM95hezZi1hT 4I74aMs0HZTdH4zYQhvKVrSPNj57AY1sS6dhwRZQuXT3s/G8Ww1iP921klmsxIXX21OMo48gkml 1TmltinR8nyNkFBtyHgmSNrP8y5o= X-Gm-Gg: ASbGncuMPIqrdS+eERAfjOu6bAV2NtrUszw8AZpRsuO4lL1E5mfo8SB5JSl/IpF7Wdo rZpNvfAh5JVPM9a0gD50VLRjyoTzSc6ph9QXy5CKaYYI7WN6AR/4SdWSnjB8ttBEtseU1BJ+jhL bC6ju+mKxxTM7Sx+gCXJZ4Z5s5kcw= X-Google-Smtp-Source: AGHT+IE+GyjL1qKlDkg7D+o8bX9hE6yd2j2qQ98uGX9oJ1V6+dxrCAU3cAMA9lVQZVX46K7XkWrAMsKTFnrgzm/ryPo= X-Received: by 2002:a05:6000:400c:b0:38f:2bd4:4f83 with SMTP id ffacd0b85a97d-390ec7d2ecemr5743970f8f.16.1740784764795; Fri, 28 Feb 2025 15:19:24 -0800 (PST) MIME-Version: 1.0 References: <102.1350578667@sss.pgh.pa.us> <293.1350579133@sss.pgh.pa.us> In-Reply-To: From: Thom Brown Date: Fri, 28 Feb 2025 23:19:12 +0000 X-Gm-Features: AQ5f1JpHxXZ6CDJIBURQQXmmfXzCvnafvjwcSDbYy9xLIabPC72vQ9masX8UsXg Message-ID: Subject: Re: [PERFORM] Unused index influencing sequential scan plan To: Tomas Vondra Cc: pgsql-performance , Tom Lane Content-Type: multipart/alternative; boundary="000000000000894258062f3c099c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000894258062f3c099c Content-Type: text/plain; charset="UTF-8" On Thu, 18 Oct 2012, 18:01 Thom Brown, wrote: > On 18 October 2012 17:52, Tom Lane wrote: > > Thom Brown writes: > >> On 18 October 2012 17:44, Tom Lane wrote: > >>> Thom Brown writes: > >>>> And as a side note, how come it's impossible to get the planner to use > >>>> an index-only scan to satisfy the query (disabling sequential and > >>>> regular index scans)? > > > >>> Implementation restriction - we don't yet have a way to match > index-only > >>> scans to expressions. > > > >> Ah, I suspected it might be, but couldn't find notes on what scenarios > >> it's yet to be able to work in. Thanks. > > > > I forgot to mention that there is a klugy workaround: add the required > > variable(s) as extra index columns. That is, > > > > create index i on t (foo(x), x); > > > > The planner isn't terribly bright about this, but it will use that index > > for a query that only requires foo(x), and it won't re-evaluate foo() > > (though I think it will cost the plan on the assumption it does :-(). > > Ah, yes, I've tested this and got it using an index-only scan, and it > was faster than than the sequential scan (index only scan 5024.545 ms > vs seq scan 6627.072 ms). > > So this is probably a dumb question, but is it possible to achieve the > optimisation provided by index statistics but without the index, and > without a messy workaround using a supplementary column which stores > function-derived values? If not, is that something which can be > introduced? > A very late thanks for extended statistics, Tomas. Thom > --000000000000894258062f3c099c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, 18 Oct 2012, 18:01 Thom Brown, &= lt;thom@linux.com> wrote:
On 18 October 2012 17:52= , Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> On 18 October 2012 17:44, Tom Lane <tgl@sss.pgh.pa.us> wr= ote:
>>> Thom Brown <thom@linux.com> writes:
>>>> And as a side note, how come it's impossible to get th= e planner to use
>>>> an index-only scan to satisfy the query (disabling sequent= ial and
>>>> regular index scans)?
>
>>> Implementation restriction - we don't yet have a way to ma= tch index-only
>>> scans to expressions.
>
>> Ah, I suspected it might be, but couldn't find notes on what s= cenarios
>> it's yet to be able to work in.=C2=A0 Thanks.
>
> I forgot to mention that there is a klugy workaround: add the required=
> variable(s) as extra index columns.=C2=A0 That is,
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0create index i on t (foo(x), x);
>
> The planner isn't terribly bright about this, but it will use that= index
> for a query that only requires foo(x), and it won't re-evaluate fo= o()
> (though I think it will cost the plan on the assumption it does :-().<= br>
Ah, yes, I've tested this and got it using an index-only scan, and it was faster than than the sequential scan (index only scan 5024.545 ms
vs seq scan 6627.072 ms).

So this is probably a dumb question, but is it possible to achieve the
optimisation provided by index statistics but without the index, and
without a messy workaround using a supplementary column which stores
function-derived values?=C2=A0 If not, is that something which can be
introduced?

A very late thanks for extended statistics, Tomas.

Thom
--000000000000894258062f3c099c--