Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q4NnZ-0004r3-4a for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 15:31:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q4NnX-0006SQ-IN for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 15:31:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q4NnX-0006SH-7w for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 15:31:39 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q4NnP-002uoK-PP for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 15:31:37 +0000 Received: from pro.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 34VFVU8n3016993; Wed, 31 May 2023 11:31:30 -0400 From: Tom Lane To: "Henrik Peinar (nodeSWAT.com)" cc: pgsql-general@lists.postgresql.org Subject: Re: Help needed to understand query planner regression with incremental sort In-reply-to: References: Comments: In-reply-to "Henrik Peinar (nodeSWAT.com)" message dated "Wed, 31 May 2023 13:46:37 +0300" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2808.1685547071.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 31 May 2023 11:31:30 -0400 Message-ID: <2818.1685547090@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "Henrik Peinar (nodeSWAT.com)" writes: > *Quick description: *After upgrading our Aurora PostgreSQL engine from v= 11 > to v15, one of our often run SQL's started taking tens of seconds instea= d > of running sub 100ms. Explain analyze showed that the query planner had > switched to using incremental sort. Running ANALYZE on the table fixed t= he > issue temporarily, but it came back after few days. Hmm. I think it's quite accidental that you get one plan over the other, because it looks like the key difference is something the planner doesn't account for. In the fast case you have -> Index Scan using products_pinned_at_listed_at_ix on products = (cost=3D0.43..3929423.12 rows=3D26523 width=3D1463) (actual time=3D2.325..= 32.872 rows=3D61 loops=3D1) Filter: ((desc_tsv @@ '''nike'':*'::tsquery) AND (status =3D= 'published'::prod_status)) Rows Removed by Filter: 3376 The index is only being used to produce ordered output here: the filter condition isn't related to the index. And what we see is that the query is fast because the desired rows are found in the first 3376+61 rows visited in this direction. Meanwhile in the slow case you have -> Index Scan Backward using products_pinned_at_ix on produ= cts (cost=3D0.43..1172249.47 rows=3D26139 width=3D1460) (actual time=3D5.= 263..5203.180 rows=3D18411 loops=3D1) Filter: ((desc_tsv @@ '''nike'':*'::tsquery) AND (stat= us =3D 'published'::prod_status)) Rows Removed by Filter: 5415895 Again, the filter condition isn't exploiting the index, we're just using the index to (partially) satisfy the ORDER BY. This one takes a long time because it has to trawl through 5415895+61 rows before reaching the LIMIT. So AFAICS, the runtime differential is mostly/entirely because the rows satisfying the filter condition are located near one end of the range of pinned_at. That is not a correlation that the planner knows anything about, so it's unable to see that these two ways of scanning the table will have significantly different costs. Moreover, I think you'd be mistaken to draw any great conclusions from this specific example, because with some other search term(s) the results might be totally different due to the required rows not falling in the same place. What I'd think about if this type of query is important is to set up an index that can actually be used to satisfy the filter condition, so that you're not forcing it into "scan the whole table till you find the rows you want". It looks like you already have such an index, ie a GIN index on the desc_tsv column, although I don't trust that your schema attachment is actually accurate because if it is then you have a bunch of duplicative indexes. You might try dropping the other indexes to see if you can coerce the planner into using that one, and then seeing what the cost estimate is. regards, tom lane