public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint
Date: Sat, 11 May 2024 00:05:22 -0400
Message-ID: <CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw@mail.gmail.com> (raw)
In-Reply-To: <CAAdwFAxBjyrYUkH7u+EceTaztd1QxBtBY1Teux8K=vcGKe==-A@mail.gmail.com>
References: <CAAdwFAxBjyrYUkH7u+EceTaztd1QxBtBY1Teux8K=vcGKe==-A@mail.gmail.com>

On Fri, May 10, 2024 at 11:28 PM WU Yan <[email protected]> wrote:

> Hi everyone, first time here. Please kindly let me know if this is not the
> right place to ask.
>
> I notice a simple query can read a lot of buffer blocks in a meaningless
> way, when
> 1. there is an index scan on a multicolumn index
> 2. there is row constructor comparison in the Index Cond
> 3. there is also an equality constraint on the leftmost column of the
> multicolumn index
>
>
> ## How to reproduce
>
> I initially noticed it on AWS Aurora RDS, but it can be reproduced in
> docker container as well.
> ```bash
> docker run --name test-postgres -e POSTGRES_PASSWORD=mysecretpassword -d
> -p 5432:5432 postgres:16.3
> ```
>
> Create a table with a multicolumn index. Populate 12 million rows with
> random integers.
> ```sql
> CREATE TABLE t(a int, b int);
> CREATE INDEX my_idx ON t USING BTREE (a, b);
>
> INSERT INTO t(a, b)
> SELECT
>     (random() * 123456)::int AS a,
>     (random() * 123456)::int AS b
> FROM
>     generate_series(1, 12345678);
>
> ANALYZE t;
> ```
>
> Simple query that uses the multicolumn index.
> ```
> postgres=# explain (analyze, buffers) select * from t where row(a, b) >
> row(123450, 123450) and a = 0 order by a, b;
>

Out of curiosity, why "where row(a, b) > row(123450, 123450)" instead of "where
a > 123450 and b > 123450"?

view thread (2+ 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]
  Subject: Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint
  In-Reply-To: <CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw@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