Message-ID: <00fec03aaaaa9a18a6d351987f3bb37a6ecce170.camel@cybertec.at>
Subject: Re: Poor performance due to parallel seq scan on indexed date field
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Wells Oliver <wells.oliver@gmail.com>, pgsql-admin
	 <pgsql-admin@postgresql.org>
Date: Wed, 21 Jun 2023 22:03:55 +0200
In-Reply-To: 
 <CAOC+FBVYuQ9gkEYo_ZOioNEi9HwTETCF5bjpgYfmTXq9LpQf8g@mail.gmail.com>
References: 
	<CAOC+FBVYuQ9gkEYo_ZOioNEi9HwTETCF5bjpgYfmTXq9LpQf8g@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/00fec03aaaaa9a18a6d351987f3bb37a6ecce170.camel%40cybertec.at>
Precedence: bulk

On Wed, 2023-06-21 at 11:31 -0700, Wells Oliver wrote:
> Dead simple date scan across a big-ish table (est.=C2=A023,153,666 rows)
>=20
> explain analyze select count(*) from vw_pitches where game_date >=3D '202=
2-06-21' and game_date <=3D '2023-06-21';
>=20
> The view does do some joins but those don't seem to be the issue to me.
>=20
> Planner does:
>=20
> Finalize Aggregate =C2=A0(cost=3D3596993.88..3596993.89 rows=3D1 width=3D=
8) (actual time=3D69980.491..69982.076 rows=3D1 loops=3D1)
> =C2=A0 -> =C2=A0Gather =C2=A0(cost=3D3596993.46..3596993.87 rows=3D4 widt=
h=3D8) (actual time=3D69979.137..69982.071 rows=3D5 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Workers Planned: 4
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Workers Launched: 4
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Partial Aggregate =C2=A0(cost=3D3595=
993.46..3595993.47 rows=3D1 width=3D8) (actual time=3D69975.136..69975.137 =
rows=3D1 loops=3D5)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Nested Loop =C2=
=A0(cost=3D0.44..3591408.37 rows=3D1834034 width=3D0) (actual time=3D0.882.=
.69875.934 rows=3D1458419 loops=3D5)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =
=C2=A0Parallel Seq Scan on pitches p =C2=A0(cost=3D0.00..3537431.89 rows=3D=
1834217 width=3D12) (actual time=3D0.852..68914.256 rows=3D1458419 loops=3D=
5)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Filter: ((game_date >=3D '2022-06-21'::date) AND (game_da=
te <=3D '2023-06-21'::date))
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Rows Removed by Filter: 3212310
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =
=C2=A0Memoize =C2=A0(cost=3D0.44..0.47 rows=3D1 width=3D4) (actual time=3D0=
.000..0.000 rows=3D1 loops=3D7292095)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Cache Key: p.pitcher_identity_id
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Cache Mode: logical
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Hits: 1438004 =C2=A0Misses: 21042 =C2=A0Evictions: 0 =C2=
=A0Overflows: 0 =C2=A0Memory Usage: 2138kB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Worker 0: =C2=A0Hits: 1429638 =C2=A0Misses: 21010 =C2=A0E=
victions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage: 2134kB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Worker 1: =C2=A0Hits: 1456755 =C2=A0Misses: 21435 =C2=A0E=
victions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage: 2177kB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Worker 2: =C2=A0Hits: 1433557 =C2=A0Misses: 21201 =C2=A0E=
victions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage: 2154kB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Worker 3: =C2=A0Hits: 1428727 =C2=A0Misses: 20726 =C2=A0E=
victions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage: 2105kB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -> =C2=A0Index Only Scan using identity_pkey on identity =
idpitcher =C2=A0(cost=3D0.43..0.46 rows=3D1 width=3D4) (actual time=3D0.007=
..0.007 rows=3D1 loops=3D105414)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (identity_id =3D p.pitch=
er_identity_id)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Heap Fetches: 83
> Planning Time: 1.407 ms
> Execution Time: 69982.927 ms
>=20
> Is there something to be done here? Kind of a frequent style of query and=
 quite slow.

First, make sure that PostgreSQL isn't picking the correct plan.
Play with "enable_seqscan" and see if the query becomes slower.

Then, make sure that "random_page_cost" is set correctly.  For SSDs and
other storage where random I/O is not more expensive, 1.1 or 1.0 are better
values than the default 4.

Finally, try disabling or reducing parallel query.  PostgreSQL does not opt=
imize
for low resource usage, but for short execution time.  Perhaps running the =
qurey
with 5 processes actually is faster this way, but it might use way more res=
orces
than a different execution plan.

Yours,
Laurenz Albe