Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.7\))
Subject: Re: Major performance degradation with joins in 15.8 or 15.7?
From: Ed Sabol <edwardjsabol@gmail.com>
In-Reply-To: <b98a67c1-93db-40d7-a3bb-61ec51a80f55@gmail.com>
Date: Fri, 8 Nov 2024 15:32:54 -0500
Content-Transfer-Encoding: quoted-printable
Message-Id: <4190FC09-590B-443F-9064-88B6F7C1EBE2@gmail.com>
References: <197E1EF0-BEDF-42C0-8508-56BC6E88AA35@gmail.com>
 <CAApHDvr8c4-unAVysqMgz0mWO=PZ_WAGprq-jwND5+C_OO8SYg@mail.gmail.com>
 <7A0AB841-65F3-4317-905C-E98D45953E3E@gmail.com>
 <2afa93b9-17d9-4bdd-bee2-f33dc12383cc@gmail.com>
 <2F4F210E-3BEA-4365-AB19-AC7917EF9F49@gmail.com>
 <b98a67c1-93db-40d7-a3bb-61ec51a80f55@gmail.com>
To: pgsql-performance@lists.postgresql.org
Archived-At: 
 <https://www.postgresql.org/message-id/4190FC09-590B-443F-9064-88B6F7C1EBE2%40gmail.com>
Precedence: bulk

On Nov 7, 2024, at 9:54 PM, Andrei Lepikhov <lepihov@gmail.com> wrote:
> On 11/8/24 09:45, Ed Sabol wrote:
>> On Nov 7, 2024, at 9:27 PM, Andrei Lepikhov <lepihov@gmail.com> =
wrote:
>>> Postgres didn't want Materialize in this example because of the low =
estimation on its outer subquery. AFAIC, by increasing the *_page_cost's =
value, you added extra weight to the inner subquery=20
>> What kind of extended statistics do you suggest for this? ndistinct, =
dependencies, or mcv?
>> CREATE STATISTICS tablename_stats (<statistics type?>) ON relation, =
type FROM tablename;
>> ANALYZE tablename;
> I'd recommend to use all of them - MCV is helpful in most of the cases =
(and relatively cheap), distinct is actually used in Postgres now to =
calculate number of groups (GROUP-BY, Sort, Memoize, etc.); dependencies =
- to find correlations between columns - usually in scan filters.

OK, I've executed the following:

CREATE STATISTICS tablename_stats_rt_nd  (ndistinct) ON relation, type =
FROM tablename;
CREATE STATISTICS tablename_stats_rt_mcv (mcv) ON relation, type FROM =
tablename;
CREATE STATISTICS tablename_stats_rt_dep (dependencies) ON relation, =
type FROM tablename;

CREATE STATISTICS tablename_stats_rv_nd  (ndistinct) ON relation, value =
FROM tablename;
CREATE STATISTICS tablename_stats_rv_mcv (mcv) ON relation, value FROM =
tablename;
CREATE STATISTICS tablename_stats_rv_dep (dependencies) ON relation, =
value FROM tablename;

CREATE STATISTICS tablename_stats_nr_nd  (ndistinct) ON name, relation =
FROM tablename;
CREATE STATISTICS tablename_stats_nr_mcv (mcv) ON name, relation FROM =
tablename;
CREATE STATISTICS tablename_stats_nr_dep (dependencies) ON name, =
relation FROM tablename;

CREATE STATISTICS tablename_stats_nt_nd  (ndistinct) ON name, type FROM =
tablename;
CREATE STATISTICS tablename_stats_nt_mcv (mcv) ON name, type FROM =
tablename;
CREATE STATISTICS tablename_stats_nt_dep (dependencies) ON name, type =
FROM tablename;

CREATE STATISTICS tablename_stats_nv_nd  (ndistinct) ON name, value FROM =
tablename;
CREATE STATISTICS tablename_stats_nv_mcv (mcv) ON name, value FROM =
tablename;
CREATE STATISTICS tablename_stats_nv_dep (dependencies) ON name, value =
FROM tablename;

ANALYZE tablename;

Now with random_page_cost =3D 4.0, the optimizer materializes, and it's =
fast again:

 Nested Loop  (cost=3D1226.12..11129.87 rows=3D1 width=3D112) (actual =
time=3D30.965..31.333 rows=3D1 loops=3D1)
   Join Filter: (a.name =3D d.name)
   Buffers: shared hit=3D7447
   ->  Nested Loop  (cost=3D1225.70..11112.51 rows=3D1 width=3D108) =
(actual time=3D30.921..31.208 rows=3D1 loops=3D1)
         Buffers: shared hit=3D7418
         ->  Hash Join  (cost=3D1225.27..11093.62 rows=3D1 width=3D86) =
(actual time=3D30.862..31.078 rows=3D1 loops=3D1)
               Hash Cond: ((a.name || '.doc'::text) =3D b_1.name)
               Buffers: shared hit=3D7389
               ->  Nested Loop  (cost=3D1167.53..11019.89 rows=3D11 =
width=3D70) (actual time=3D27.143..27.347 rows=3D1 loops=3D1)
                     Join Filter: (CASE WHEN ("position"(a.name, =
'zz'::text) =3D 1) THEN a.name ELSE ('h_'::text || a.name) END =3D =
"*SELECT* 1".table_name)
                     Rows Removed by Join Filter: 1021
                     Buffers: shared hit=3D6268
                     ->  Bitmap Heap Scan on tablename a  =
(cost=3D456.55..5407.28 rows=3D1077 width=3D38) (actual =
time=3D2.986..15.865 rows=3D1022 loops=3D1)
                           Recheck Cond: (relation =3D =
'description'::text)
                           Filter: (type =3D 'table'::text)
                           Rows Removed by Filter: 37044
                           Heap Blocks: exact=3D4024
                           Buffers: shared hit=3D4065
                           ->  Bitmap Index Scan on tablename_r  =
(cost=3D0.00..456.29 rows=3D38915 width=3D0) (actual time=3D2.336..2.336 =
rows=3D44980 loops=3D1)
                                 Index Cond: (relation =3D =
'description'::text)
                                 Buffers: shared hit=3D41
                     ->  Materialize  (cost=3D710.98..5564.15 rows=3D2 =
width=3D64) (actual time=3D0.008..0.009 rows=3D1 loops=3D1022)
                           Buffers: shared hit=3D2203
                           ->  Append  (cost=3D710.98..5564.14 rows=3D2 =
width=3D64) (actual time=3D7.519..7.548 rows=3D1 loops=3D1)
                                 Buffers: shared hit=3D2203
                                 ->  Subquery Scan on "*SELECT* 1"  =
(cost=3D710.98..3537.89 rows=3D1 width=3D64) (actual time=3D6.629..6.636 =
rows=3D0 loops=3D1)
                                       Buffers: shared hit=3D1380
                                       ->  Bitmap Heap Scan on tablename =
 (cost=3D710.98..3537.88 rows=3D1 width=3D96) (actual time=3D6.628..6.633 =
rows=3D0 loops=3D1)
                                             Recheck Cond: ((relation =3D =
ANY =
('{start_time,end_time,dataset_id_column,dataset_id_prefix,original_missio=
n_name,defaultSearchRadius,author,tableType,bibcode,priority,regime,author=
,includesTypes,Mission,subject}'::text[])) AND (type =3D 'table'::text))
                                             Filter: ((CASE relation =
WHEN 'Mission'::text THEN upper(value) ELSE value END =3D 'foo'::text) =
AND (CASE relation WHEN 'defaultSearchRadius'::text THEN =
'default_search_radius'::text WHEN 'Mission'::text THEN 'o_name'::text =
WHEN 'priority'::text THEN 'table_priority'::text WHEN 'bibcode'::text =
THEN 'catalog_bibcode'::text WHEN 'regime'::text THEN =
'frequency_regime'::text WHEN 'author'::text THEN 'table_author'::text =
WHEN 'tableType'::text THEN 'table_type'::text WHEN 'subject'::text THEN =
'row_type'::text ELSE relation END =3D 'o_name'::text))
                                             Rows Removed by Filter: =
8253
                                             Heap Blocks: exact=3D1276
                                             Buffers: shared hit=3D1380
                                             ->  BitmapAnd  =
(cost=3D710.94..710.94 rows=3D1275 width=3D0) (actual time=3D3.346..3.350 =
rows=3D0 loops=3D1)
                                                   Buffers: shared =
hit=3D104
                                                   ->  Bitmap Index Scan =
on tablename_r  (cost=3D0.00..134.96 rows=3D9145 width=3D0) (actual =
time=3D0.573..0.574 rows=3D9998 loops=3D1)
                                                         Index Cond: =
(relation =3D ANY =
('{start_time,end_time,dataset_id_column,dataset_id_prefix,original_missio=
n_name,defaultSearchRadius,author,tableType,bibcode,priority,regime,author=
,includesTypes,Mission,subject}'::text[]))
                                                         Buffers: shared =
hit=3D49
                                                   ->  Bitmap Index Scan =
on tablename_t  (cost=3D0.00..575.73 rows=3D49507 width=3D0) (actual =
time=3D2.693..2.693 rows=3D59373 loops=3D1)
                                                         Index Cond: =
(type =3D 'table'::text)
                                                         Buffers: shared =
hit=3D55
                                 ->  Subquery Scan on "*SELECT* 5"  =
(cost=3D10.28..2026.24 rows=3D1 width=3D64) (actual time=3D0.886..0.904 =
rows=3D1 loops=3D1)
                                       Buffers: shared hit=3D823
                                       ->  Bitmap Heap Scan on tablename =
tablename_1  (cost=3D10.28..2026.23 rows=3D1 width=3D96) (actual =
time=3D0.884..0.899 rows=3D1 loops=3D1)
                                             Recheck Cond: (relation =3D =
'containedBy'::text)
                                             Filter: ((substr(value, 1, =
8) =3D 'mission:'::text) AND (upper("substring"(value, 9)) =3D =
'foo'::text))
                                             Rows Removed by Filter: 721
                                             Heap Blocks: exact=3D820
                                             Buffers: shared hit=3D823
                                             ->  Bitmap Index Scan on =
tablename_r  (cost=3D0.00..10.28 rows=3D781 width=3D0) (actual =
time=3D0.085..0.085 rows=3D905 loops=3D1)
                                                   Index Cond: (relation =
=3D 'containedBy'::text)
                                                   Index Cond: (relation =
=3D 'containedBy'::text)
                                                   Buffers: shared hit=3D3=

               ->  Hash  (cost=3D44.87..44.87 rows=3D1030 width=3D38) =
(actual time=3D5.334..5.342 rows=3D1025 loops=3D1)
                     Buckets: 2048  Batches: 1  Memory Usage: 124kB
                     Buffers: shared hit=3D1121
                     ->  Bitmap Heap Scan on tablename b_1  =
(cost=3D33.06..44.87 rows=3D1030 width=3D38) (actual time=3D1.157..4.018 =
rows=3D1025 loops=3D1)
                           Recheck Cond: ((relation =3D 'located'::text) =
AND (type =3D 'document'::text))
                           Heap Blocks: exact=3D1113
                           Buffers: shared hit=3D1121
                           ->  BitmapAnd  (cost=3D33.06..33.06 rows=3D3 =
width=3D0) (actual time=3D0.765..0.769 rows=3D0 loops=3D1)
                                 Buffers: shared hit=3D8
                                 ->  Bitmap Index Scan on tablename_r  =
(cost=3D0.00..16.15 rows=3D1030 width=3D0) (actual time=3D0.347..0.347 =
rows=3D1227 loops=3D1)
                                       Index Cond: (relation =3D =
'located'::text)
                                       Buffers: shared hit=3D4
                                 ->  Bitmap Index Scan on tablename_t  =
(cost=3D0.00..16.15 rows=3D1030 width=3D0) (actual time=3D0.314..0.315 =
rows=3D1227 loops=3D1)
                                       Index Cond: (type =3D =
'document'::text)
                                       Buffers: shared hit=3D4
         ->  Index Scan using tablename_n on tablename c  =
(cost=3D0.42..18.88 rows=3D1 width=3D22) (actual time=3D0.048..0.115 =
rows=3D1 loops=3D1)
               Index Cond: (name =3D a.name)
               Filter: (relation =3D 'lastUpdated'::text)
               Rows Removed by Filter: 58
               Buffers: shared hit=3D29
   ->  Index Scan using tablename_n on tablename d  (cost=3D0.42..17.33 =
rows=3D1 width=3D22) (actual time=3D0.034..0.104 rows=3D1 loops=3D1)
         Index Cond: (name =3D c.name)
         Filter: (relation =3D 'rowcount'::text)
         Rows Removed by Filter: 58
         Buffers: shared hit=3D29
 Planning:
   Buffers: shared hit=3D64
 Planning Time: 5.086 ms
 Execution Time: 32.226 ms
(81 rows)

This was a nice learning experience and I hope it will help with =
performance going forward, but I still think I'm going to keep =
random_page_cost =3D 2.0.

None of this really explains why this became a problem after ~10 years =
of it not being one, but I think the only likely reason is that the =
table just grew gradually over time and reached some threshold that =
changed the optimizer's plan very adversely.

Thanks,
Ed