Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t9DhO-008Utx-EV for pgsql-performance@arkaria.postgresql.org; Fri, 08 Nov 2024 01:22:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t9DhL-004ccN-Fk for pgsql-performance@arkaria.postgresql.org; Fri, 08 Nov 2024 01:22:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t9DhK-004ccE-Pf for pgsql-performance@lists.postgresql.org; Fri, 08 Nov 2024 01:22:03 +0000 Received: from mail-qv1-xf35.google.com ([2607:f8b0:4864:20::f35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t9DhE-000iqy-8X for pgsql-performance@lists.postgresql.org; Fri, 08 Nov 2024 01:22:02 +0000 Received: by mail-qv1-xf35.google.com with SMTP id 6a1803df08f44-6cc250bbc9eso10044706d6.2 for ; Thu, 07 Nov 2024 17:21:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731028915; x=1731633715; darn=lists.postgresql.org; h=to:references:message-id:content-transfer-encoding:date:in-reply-to :from:subject:mime-version:from:to:cc:subject:date:message-id :reply-to; bh=X+5HFH+yGrCmp0hmezbirKICgV/9z++cG8Fk/RwXE6k=; b=f56Esmsj6rVLNpUqWw/g6+WNi11PTp6/80I7DOSrGL2l4IreO9fJTeHvdjNDs9tWjF MkLLXFsgNlxYTJXKTvI+yqGIochtZ/VCS2grOiPt94Aw6T9rzQaAvcxNyQlx215lratV 0D1eIRqKcKRUM9pr2fY+MnbR7AFkPz0RGrVv4Tdn/ArPkJKN8blY+D+pXvywoKEeidB+ VTykh02oVx3k7kEKBNZfLdwqBqhRQz6KQH5o3c0bPg2ZvrMp1L2G+2pbq125KkBII3q1 tb8fg2vNNs/WEgS9++5B7E3FcLCB2p3qo7HkU+f5bFoilMQliD8GGJn0WwADubB/lfg/ VnOw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731028915; x=1731633715; h=to:references:message-id:content-transfer-encoding:date:in-reply-to :from:subject:mime-version:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=X+5HFH+yGrCmp0hmezbirKICgV/9z++cG8Fk/RwXE6k=; b=C0VFI0jNIKIEyUiGJTc+36A8erjeK1+tejqw0C/qE+nh3dbiJDH9pUwso4crM8TpNJ uBUHjUKN8PJZIfbLWKlYW3DYoMeuuxIiK4H63cBE8ZvJHPps7DrtKW3c40aOB9sPkjuu h6aTjuHj2Ken2jVyz6EV0q/99wCCLp7MYdwN8YvGrNVysnO7DIOLWwX1BBMJXt/yN5IO 7vFUXOOX80PgFny9VbUTOnCU/S1sEBxSxCIYwqUPo90iTOg9m0pqxLR61pRyPebTPIfO Ezfo8xClAxrkp1Kf9W+gLcODZWS/zPoqB77qLyZbHf7SfLTxo2qRdU2hA3mB3Y0vHOgm o+MQ== X-Gm-Message-State: AOJu0Yzkfs+cgdCCZKs6v2vqr/wm5fZVohf64YDqYz3HFAujtvuz8uvi fzcEalUoI7K5qRfo9TGFerRroQvQ4to3PBLXjXCgLdP6TFdO4Y+MhCmoxz7h X-Google-Smtp-Source: AGHT+IE3vVOeL8NK3He1PtI6bpyN4UQ0wpfMcPeiOO9AYgQHcXwykukP+/nnKf7XGJDpS3E1icqRdg== X-Received: by 2002:a05:6214:2d45:b0:6d3:89d5:fadb with SMTP id 6a1803df08f44-6d39e2004b0mr11337136d6.44.1731028914897; Thu, 07 Nov 2024 17:21:54 -0800 (PST) Received: from ?IPv6:2601:14b:4100:214f:1dae:51df:a274:fb9e? ([2601:14b:4100:214f:1dae:51df:a274:fb9e]) by smtp.gmail.com with ESMTPSA id 6a1803df08f44-6d3966310b7sm13288996d6.112.2024.11.07.17.21.54 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 07 Nov 2024 17:21:54 -0800 (PST) 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 In-Reply-To: Date: Thu, 7 Nov 2024 20:21:52 -0500 Content-Transfer-Encoding: quoted-printable Message-Id: <7A0AB841-65F3-4317-905C-E98D45953E3E@gmail.com> References: <197E1EF0-BEDF-42C0-8508-56BC6E88AA35@gmail.com> To: pgsql-performance@lists.postgresql.org X-Mailer: Apple Mail (2.3445.9.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Nov 7, 2024, at 5:18 PM, David Rowley wrote: > It's impossible to say with the given information. You didn't mention > which version you upgraded from to start with. Sorry, 15.6 to 15.7 to 15.8, but we weren't on 15.7 for very long before = 15.8. > You can set random_page_cost for just the session you're connected to > and try it. SET random_page_cost =3D ; before running > EXPLAIN (ANALYZE, BUFFERS). Oh, I didn't think of that. Duh. Here it is with random_page_cost =3D = 4.0: Nested Loop (cost=3D1216.49..11246.62 rows=3D1 width=3D112) (actual = time=3D6374.844..6387.845 rows=3D1 loops=3D1) Join Filter: (CASE WHEN ("position"(a.name, 'zz'::text) =3D 1) THEN = a.name ELSE ('foo_'::text || a.name) END =3D "*SELECT* 1".table_name) Rows Removed by Join Filter: 1009 Buffers: shared hit=3D2227268 read=3D426 dirtied=3D1 -> Nested Loop (cost=3D497.27..5499.27 rows=3D1 width=3D54) (actual = time=3D23.852..84.444 rows=3D1010 loops=3D1) Buffers: shared hit=3D24458 read=3D426 dirtied=3D1 -> Nested Loop (cost=3D496.85..5495.04 rows=3D1 width=3D76) = (actual time=3D23.842..64.212 rows=3D1010 loops=3D1) Buffers: shared hit=3D14591 read=3D426 -> Hash Join (cost=3D496.43..5490.81 rows=3D1 width=3D54)= (actual time=3D23.823..40.587 rows=3D1010 loops=3D1) Hash Cond: ((a.name || '.doc'::text) =3D b_1.name) Buffers: shared hit=3D4724 read=3D426 -> Bitmap Heap Scan on metainfo a = (cost=3D460.62..5400.28 rows=3D5471 width=3D38) (actual = time=3D18.288..32.125 rows=3D1022 loops=3D1) Recheck Cond: (relation =3D = 'description'::text) Filter: (type =3D 'table'::text) Rows Removed by Filter: 37044 Heap Blocks: exact=3D3999 Buffers: shared hit=3D3614 read=3D425 -> Bitmap Index Scan on metainfo_r = (cost=3D0.00..459.25 rows=3D39844 width=3D0) (actual time=3D17.623..17.624= rows=3D44615 loops=3D1) Index Cond: (relation =3D = 'description'::text) Buffers: shared hit=3D3 read=3D37 -> Hash (cost=3D35.77..35.77 rows=3D3 width=3D38) = (actual time=3D5.511..5.518 rows=3D1025 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: = 116kB Buffers: shared hit=3D1110 read=3D1 -> Bitmap Heap Scan on metainfo b_1 = (cost=3D23.96..35.77 rows=3D3 width=3D38) (actual time=3D1.225..4.206 = rows=3D1025 loops=3D1) Recheck Cond: ((relation =3D = 'located'::text) AND (type =3D 'document'::text)) Heap Blocks: exact=3D1103 Buffers: shared hit=3D1110 read=3D1 -> BitmapAnd (cost=3D23.96..23.96 = rows=3D3 width=3D0) (actual time=3D0.851..0.856 rows=3D0 loops=3D1) Buffers: shared hit=3D7 read=3D1 -> Bitmap Index Scan on = metainfo_r (cost=3D0.00..11.86 rows=3D991 width=3D0) (actual = time=3D0.402..0.403 rows=3D1214 loops=3D1) Index Cond: (relation =3D = 'located'::text) Buffers: shared hit=3D3 = read=3D1 -> Bitmap Index Scan on = metainfo_t (cost=3D0.00..11.86 rows=3D991 width=3D0) (actual = time=3D0.343..0.344 rows=3D1214 loops=3D1) Index Cond: (type =3D = 'document'::text) Buffers: shared hit=3D4 -> Index Scan using metainfo_n on metainfo c = (cost=3D0.42..4.22 rows=3D1 width=3D22) (actual time=3D0.016..0.020 = rows=3D1 loops=3D1010) Index Cond: (name =3D a.name) Filter: (relation =3D 'lastUpdated'::text) Rows Removed by Filter: 48 Buffers: shared hit=3D9867 -> Index Scan using metainfo_n on metainfo d (cost=3D0.42..4.22= rows=3D1 width=3D22) (actual time=3D0.012..0.017 rows=3D1 loops=3D1010) Index Cond: (name =3D a.name) Filter: (relation =3D 'rowcount'::text) Rows Removed by Filter: 48 Buffers: shared hit=3D9867 dirtied=3D1 -> Append (cost=3D719.22..5747.30 rows=3D2 width=3D64) (actual = time=3D6.222..6.228 rows=3D1 loops=3D1010) Buffers: shared hit=3D2202810 -> Subquery Scan on "*SELECT* 1" (cost=3D719.22..3653.48 = rows=3D1 width=3D64) (actual time=3D5.358..5.358 rows=3D0 loops=3D1010) Buffers: shared hit=3D1381680 -> Bitmap Heap Scan on metainfo (cost=3D719.22..3653.47 = rows=3D1 width=3D96) (actual time=3D5.356..5.356 rows=3D0 loops=3D1010) 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= ,include sTypes,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=3D1277650 Buffers: shared hit=3D1381680 -> BitmapAnd (cost=3D719.18..719.18 rows=3D1361 = width=3D0) (actual time=3D2.744..2.744 rows=3D0 loops=3D1010) Buffers: shared hit=3D104030 -> Bitmap Index Scan on metainfo_r = (cost=3D0.00..140.70 rows=3D9910 width=3D0) (actual time=3D0.495..0.495 = rows=3D9896 loops=3D1010) 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=3D49490 -> Bitmap Index Scan on metainfo_t = (cost=3D0.00..578.23 rows=3D50374 width=3D0) (actual time=3D2.198..2.198 = rows=3D58833 loops=3D1010) Index Cond: (type =3D 'table'::text) Buffers: shared hit=3D54540 -> Subquery Scan on "*SELECT* 5" (cost=3D10.57..2093.81 = rows=3D1 width=3D64) (actual time=3D0.862..0.867 rows=3D1 loops=3D1010) Buffers: shared hit=3D821130 -> Bitmap Heap Scan on metainfo metainfo_1 = (cost=3D10.57..2093.80 rows=3D1 width=3D96) (actual time=3D0.861..0.864 = rows=3D1 loops=3D1010) 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=3D818100 Buffers: shared hit=3D821130 -> Bitmap Index Scan on metainfo_r = (cost=3D0.00..10.56 rows=3D819 width=3D0) (actual time=3D0.076..0.076 = rows=3D895 loops=3D1010) Index Cond: (relation =3D = 'containedBy'::text) Buffers: shared hit=3D3030 Planning: Buffers: shared hit=3D64 Planning Time: 4.449 ms Execution Time: 6388.155 ms (78 rows) And here it is with random_page_cost =3D 2.0: Nested Loop (cost=3D3695.45..7241.35 rows=3D1 width=3D112) (actual = time=3D35.046..49.115 rows=3D1 loops=3D1) Buffers: shared hit=3D14243 -> Nested Loop (cost=3D3695.03..7238.89 rows=3D1 width=3D70) = (actual time=3D35.029..49.093 rows=3D1 loops=3D1) Join Filter: (CASE WHEN ("position"(a.name, 'zz'::text) =3D 1) = THEN a.name ELSE ('foo_'::text || a.name) END =3D "*SELECT* = 1".table_name) Rows Removed by Join Filter: 1009 Buffers: shared hit=3D14239 -> Nested Loop (cost=3D3105.81..3627.24 rows=3D13 width=3D38) = (actual time=3D6.688..37.136 rows=3D1010 loops=3D1) Join Filter: (c.name =3D a.name) Buffers: shared hit=3D12058 -> Merge Join (cost=3D3105.39..3116.74 rows=3D53 = width=3D44) (actual time=3D6.648..10.798 rows=3D1010 loops=3D1) Merge Cond: (c.name =3D d.name) Buffers: shared hit=3D2200 -> Sort (cost=3D1546.41..1549.10 rows=3D1076 = width=3D22) (actual time=3D3.618..4.357 rows=3D1010 loops=3D1) Sort Key: c.name Sort Method: quicksort Memory: 78kB Buffers: shared hit=3D1101 -> Bitmap Heap Scan on metainfo c = (cost=3D12.76..1492.22 rows=3D1076 width=3D22) (actual time=3D0.413..2.446= rows=3D1010 loops=3D1) Recheck Cond: (relation =3D = 'lastUpdated'::text) Heap Blocks: exact=3D1097 Buffers: shared hit=3D1101 -> Bitmap Index Scan on metainfo_r = (cost=3D0.00..12.49 rows=3D1076 width=3D0) (actual time=3D0.250..0.251 = rows=3D1200 loops=3D1) Index Cond: (relation =3D = 'lastUpdated'::text) Buffers: shared hit=3D4 -> Sort (cost=3D1558.98..1561.70 rows=3D1088 = width=3D22) (actual time=3D3.022..3.820 rows=3D1010 loops=3D1) Sort Key: d.name Sort Method: quicksort Memory: 78kB Buffers: shared hit=3D1099 -> Bitmap Heap Scan on metainfo d = (cost=3D12.85..1504.11 rows=3D1088 width=3D22) (actual time=3D0.384..1.886= rows=3D1010 loops=3D1) Recheck Cond: (relation =3D = 'rowcount'::text) Heap Blocks: exact=3D1095 Buffers: shared hit=3D1099 -> Bitmap Index Scan on metainfo_r = (cost=3D0.00..12.58 rows=3D1088 width=3D0) (actual time=3D0.236..0.237 = rows=3D1200 loops=3D1) Index Cond: (relation =3D = 'rowcount'::text) Buffers: shared hit=3D4 -> Index Scan using metainfo_n on metainfo a = (cost=3D0.42..9.62 rows=3D1 width=3D38) (actual time=3D0.018..0.023 = rows=3D1 loops=3D1010) Index Cond: (name =3D d.name) Filter: ((relation =3D 'description'::text) AND = (type =3D 'table'::text)) Rows Removed by Filter: 48 Buffers: shared hit=3D9858 -> Materialize (cost=3D589.22..3611.08 rows=3D2 width=3D64) = (actual time=3D0.009..0.009 rows=3D1 loops=3D1010) Buffers: shared hit=3D2181 -> Append (cost=3D589.22..3611.07 rows=3D2 width=3D64) = (actual time=3D7.847..7.870 rows=3D1 loops=3D1) Buffers: shared hit=3D2181 -> Subquery Scan on "*SELECT* 1" = (cost=3D589.22..2395.08 rows=3D1 width=3D64) (actual time=3D6.935..6.944 = rows=3D0 loops=3D1) Buffers: shared hit=3D1368 -> Bitmap Heap Scan on metainfo = (cost=3D589.22..2395.07 rows=3D1 width=3D96) (actual time=3D6.933..6.940 = 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=3D1265 Buffers: shared hit=3D1368 -> BitmapAnd (cost=3D589.18..589.18 = rows=3D1361 width=3D0) (actual time=3D3.406..3.412 rows=3D0 loops=3D1) Buffers: shared hit=3D103 -> Bitmap Index Scan on = metainfo_r (cost=3D0.00..110.70 rows=3D9910 width=3D0) (actual = time=3D0.638..0.638 rows=3D9897 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 = metainfo_t (cost=3D0.00..478.23 rows=3D50374 width=3D0) (actual = time=3D2.688..2.690 rows=3D58842 loops=3D1) Index Cond: (type =3D = 'table'::text) Buffers: shared hit=3D54 -> Subquery Scan on "*SELECT* 5" = (cost=3D8.57..1215.97 rows=3D1 width=3D64) (actual time=3D0.909..0.919 = rows=3D1 loops=3D1) Buffers: shared hit=3D813 -> Bitmap Heap Scan on metainfo metainfo_1 = (cost=3D8.57..1215.96 rows=3D1 width=3D96) (actual time=3D0.907..0.914 = 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=3D810 Buffers: shared hit=3D813 -> Bitmap Index Scan on metainfo_r = (cost=3D0.00..8.56 rows=3D819 width=3D0) (actual time=3D0.085..0.085 = rows=3D895 loops=3D1) Index Cond: (relation =3D = 'containedBy'::text) Buffers: shared hit=3D3 -> Index Scan using metainfo_n on metainfo b_1 (cost=3D0.42..2.44 = rows=3D1 width=3D38) (actual time=3D0.011..0.012 rows=3D1 loops=3D1) Index Cond: (name =3D (a.name || '.doc'::text)) Filter: ((relation =3D 'located'::text) AND (type =3D = 'document'::text)) Buffers: shared hit=3D4 Planning: Buffers: shared hit=3D367 Planning Time: 5.391 ms Execution Time: 49.412 ms (78 rows) Thanks, Ed