MIME-Version: 1.0
References: <1203098.1743640224@sss.pgh.pa.us> <2BC8AB39-16D7-4423-BE0A-F0F4EA432E2E@gmail.com>
In-Reply-To: <2BC8AB39-16D7-4423-BE0A-F0F4EA432E2E@gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 3 Apr 2025 17:41:43 +1300
Message-ID: <CAApHDvou3ZhHmQ6Qx9O9HbXigTu9oHdTFKibv96TyAbJ8WGYNw@mail.gmail.com>
Subject: Re: Postgres Query Plan using wrong index
To: Manikandan Swaminathan <maniswami23@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAApHDvou3ZhHmQ6Qx9O9HbXigTu9oHdTFKibv96TyAbJ8WGYNw%40mail.gmail.com>
Precedence: bulk

On Thu, 3 Apr 2025 at 16:24, Manikandan Swaminathan
<maniswami23@gmail.com> wrote:
> Since you mentioned the planner not knowing about the correlation between=
 the columns, I=E2=80=99m curious, why doesn=E2=80=99t making a multivariat=
e statistic make a difference?
>
>
> CREATE STATISTICS col_a_col_b_stats (dependencies) ON col_a, col_b FROM t=
est_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