public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Manikandan Swaminathan <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Postgres Query Plan using wrong index
Date: Thu, 3 Apr 2025 17:41:43 +1300
Message-ID: <CAApHDvou3ZhHmQ6Qx9O9HbXigTu9oHdTFKibv96TyAbJ8WGYNw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Thu, 3 Apr 2025 at 16:24, Manikandan Swaminathan
<[email protected]> wrote:
> Since you mentioned the planner not knowing about the correlation between the columns, I’m curious, why doesn’t making a multivariate statistic make a difference?
>
>
> CREATE STATISTICS col_a_col_b_stats (dependencies) ON col_a, col_b FROM test_table;
> ANALYZE test_table;

Extended statistics won't help you here. "dependencies" just estimates
functional dependencies between the columns mentioned in the ON
clause.  What we'd need to store to do better in your example query is
positional information of where certain values are within indexes
according to an ordered scan of the index. I don't quite know how we'd
represent that exactly, but if we knew that a row matching col_a >
4996 wasn't until somewhere near the end of idx_col_a_btree index,
then we'd likely not want to use that index for this query.

David

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Postgres Query Plan using wrong index
  In-Reply-To: <CAApHDvou3ZhHmQ6Qx9O9HbXigTu9oHdTFKibv96TyAbJ8WGYNw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox