MIME-Version: 1.0
References: <CAJ+5Ff7s1NxcyZ4x-FdrygRT0KMuyROVPrP+Pm_1Z2DwMdv07Q@mail.gmail.com>
In-Reply-To: <CAJ+5Ff7s1NxcyZ4x-FdrygRT0KMuyROVPrP+Pm_1Z2DwMdv07Q@mail.gmail.com>
From: Artur Zakirov <zaartur@gmail.com>
Date: Mon, 7 Apr 2025 16:05:59 +0200
Message-ID: <CAKNkYnwL1uWBtjDgktU4BcvcZScgXyF_yme=+DfVjox6preqkA@mail.gmail.com>
Subject: Re: Performance regression when adding LIMIT 1 to a query
To: Costa Alexoglou <costa@dbtune.com>
Cc: 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/CAKNkYnwL1uWBtjDgktU4BcvcZScgXyF_yme%3D%2BDfVjox6preqkA%40mail.gmail.com>
Precedence: bulk

On Mon, 7 Apr 2025 at 14:45, Costa Alexoglou <costa@dbtune.com> wrote:
> ...
> with a plan:
> ```
> Gather Merge  (cost=3D115584.47..118515.35 rows=3D25120 width=3D824) (act=
ual time=3D46.004..74.267 rows=3D29653 loops=3D1)
>   Workers Planned: 2
>   Workers Launched: 2
>   ->  Sort  (cost=3D114584.45..114615.85 rows=3D12560 width=3D824) (actua=
l time=3D41.200..47.322 rows=3D9884 loops=3D3)
>         Sort Key: id
>         Sort Method: external merge  Disk: 16360kB
>         Worker 0:  Sort Method: external merge  Disk: 15552kB
>         Worker 1:  Sort Method: external merge  Disk: 14536kB
>         ->  Parallel Bitmap Heap Scan on databases_metrics  (cost=3D990.7=
7..109175.83 rows=3D12560 width=3D824) (actual time=3D3.326..14.295 rows=3D=
9884 loops=3D3)
>               Recheck Cond: ((db_instance_id =3D 'c4c97a60-b88e-4cd3-a2f1=
-random-uuid'::uuid) AND (created_at >=3D '2023-03-15 10:00:00+00'::timesta=
mp with time zone) AND (created_at <=3D '2025-04-03 10:00:00+00'::timestamp=
 with time zone))"},
>               Filter: ((metrics -> 'perf_average_query_runtime'::text) IS=
 NOT NULL)
>               Rows Removed by Filter: 68
>               Heap Blocks: exact=3D4272
>               ->  Bitmap Index Scan on idx_databases_metrics_instance_dat=
e_custom_created_debugging  (cost=3D0.00..983.24 rows=3D30294 width=3D0) (a=
ctual time=3D3.786.786 rows=3D29856 loops=3D1)"},
>                     Index Cond: ((db_instance_id =3D 'c4c97a60-b88e-4cd3-=
a2f1-random-uuid'::uuid) AND (created_at >=3D '2023-03-15 10:00:00+00'::tim=
estamp with time zone) AND (created_at <=3D '2025-04-03 10:00:00+00'::times=
tamp with time zone))"},
> ...
> With a plan:
> ```
> Limit  (cost=3D0.43..229.66 rows=3D1 width=3D824) (actual time=3D7538.004=
..7538.005 rows=3D1 loops=3D1)
>   ->  Index Scan using databases_metrics_pkey on databases_metrics  (cost=
=3D0.43..6909156.38 rows=3D30142 width=3D824) (actual time=3D7538.002..7538=
.003 rows=3D1 loops=3D1)
>         Filter: ((created_at >=3D '2023-03-15 10:00:00+00'::timestamp wit=
h time zone) AND (created_at <=3D '2025-04-03 10:00:00+00'::timestamp with =
time zone) A((metrics -> 'perf_average_query_runtime'::text) IS NOT NULL) A=
ND (db_instance_id =3D 'c4c97a60-b88e-4cd3-a2f1-random-uuid'::uuid))"},
>         Rows Removed by Filter: 10244795
> Planning Time: 0.128 ms
> Execution Time: 7538.032 ms
> ```

On your second query Postgres uses the index "databases_metrics_pkey".
I assume that it is built using the "id" column. It could be very fast
with the statement "ORDER BY ... LIMIT", but due to the additional
filter Postgres firstly has to remove 10mln rows, which doesn't
satisfy the filter, only to reach one single row.

On the first query Postgres has to read and sort only 29k rows using
the index "idx_databases_metrics_instance_date_custom_created_debugging",
which is better suited for the used filter if it includes the columns
used in the filter.

I'm not sure why Postgres chooses the index "databases_metrics_pkey".
Maybe you have outdated statistics. Did you try to run VACUUM ANALYZE
on the table?

--=20
Kind regards,
Artur