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 1pSgUN-0004Hn-Tc for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 15:48:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSgUM-0008Mb-RI for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 15:48:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSgUM-0008MS-FD for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 15:48:02 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pSgUG-0002PJ-3z for pgsql-general@postgresql.org; Thu, 16 Feb 2023 15:48:02 +0000 Received: by mail-ed1-x52a.google.com with SMTP id u21so4480473edv.3 for ; Thu, 16 Feb 2023 07:47:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:subject:from:to:user-agent:mime-version :date:message-id:from:to:cc:subject:date:message-id:reply-to; bh=xup/f0MQMdKHYZSXrvh7Mxk6b60g58AQ6KE4k2kK+bY=; b=V33u1dnXV1JI/0DjXjBrI71agr4Dg5DGuPc0KHuk5S5ri/kvdOREfqRhWE0jvNyAN5 2GAM8yxEZC6Pm5Ei9gzC1MCqhW9X6HsbSFZyyHDQ9g7SoIEe/WGX4fFqks2N7Suizj+f ppbuUG0ZKTE9LdX0ryw8TeIQkVgaE9GqaNxOQrU1OChq7gZXrQx/ssEdsW6j5gbnQPGx 3dtd4ycdcjzw6qkRDl93b0oWq8J/41iS2x857Bf3lLJY5w7UJM/EkMjfXcWZpQKXpZ0F KjWxeULQHsemCXR9TD3kMkxfMBGhYdyRSO0H+WWmzy0GZ8HofdQeePJHTell1rqsOHST Ybrw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:subject:from:to:user-agent:mime-version :date:message-id:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=xup/f0MQMdKHYZSXrvh7Mxk6b60g58AQ6KE4k2kK+bY=; b=4fj1YycMEDh7c6YzHmGguTmCmQmVXMD5fNhOX1yRhlwYFMeu2cgp4FP7r3AdvMgQpV 99bd1KNT4xaODuJhzDiLtEHK7e/P62pEQY7pNt0ONI/F21sgPRKqjFyAgPuk4QGAXCxh wyCqTr243t4eH+v24RhRZ+cBX6FMFeNiaeJKNmYciFORA//Po4hXIJn28KlSQL4kia4T jt1rUqAAHD1Q/Aw2YQKa/vNXo3k4G8k4Im8X49/PCpWrhPtyxi3t29NM2V7pGzCqgfsy UYU8Va4N9D09cQiU37lVet1aPuXAvMS8VLwYzKWAp826D3/opEVE9Q9rUV356lV31LqS ouEg== X-Gm-Message-State: AO0yUKXIM4YMmH8odcY8o2MQIQbDtHX+ZIvTI2gzo0P1iTRlTnum1AMZ MLjfJo/ST5+BXopC3RR+WNepg3Q+ok8= X-Google-Smtp-Source: AK7set+8oo70GIbZdwBjoicH11hZh3pgGREYPyQjGj0Jvizmu+TWQFcWDkF18rVZR1d+P/D1WLPYfA== X-Received: by 2002:a17:906:e09a:b0:891:a330:c890 with SMTP id gh26-20020a170906e09a00b00891a330c890mr7869882ejb.0.1676562474905; Thu, 16 Feb 2023 07:47:54 -0800 (PST) Received: from [10.1.100.12] (n60h206.net.telprom.si. [178.216.60.206]) by smtp.gmail.com with ESMTPSA id ov11-20020a170906fc0b00b0088e5f3e1faesm962480ejb.36.2023.02.16.07.47.53 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 16 Feb 2023 07:47:54 -0800 (PST) Message-ID: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com> Date: Thu, 16 Feb 2023 16:47:52 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.6.1 To: pgsql-general From: cen Subject: Postgres undeterministically uses a bad plan, how to convince it otherwise? Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, I am running the same application (identical codebase) as two separate instances to index (save) different sets of data. Both run PostgreSQL 13. The queries are the same but the content in actual databases is different. One database is around 1TB and the other around 300GB. There is a problem with a paginated select query with a join and an order. Depending on which column you order by (primary or FK) the query is either instant or takes minutes. So on one database, ordering by primary is instant but on the other it is slow and vice-versa. Switching the columns around on the slow case fixes the issue. All relavant colums are indexed. Simplified: Slow: SELECT * from table1 AS t1 LEFT JOIN table2 AS t2 ON t2.t1_id=t1.id ORDER BY t1.id ASC LIMIT 0, 10 Fast: SELECT * from table1 AS t1 LEFT JOIN table2 AS t2 ON t2.t1_id=t1.id ORDER BY t2.t1_id ASC LIMIT 0, 10 (and the opposite, on the other instance the first one is fast and second one is slow). I have run all the statistic recalculations but that doesn't help. As far as I could read the docs, there is no real way to affect the plan other than reworking the query (I've read about fencing?) which can't be done because it seems to be unpredictable and depends on actual data and data quantity. I haven't tried reindexing. Before providing and diving into the specific query plans: - does the planner take previous runs of the same query and it's execution time into account? If not, why? - assuming the query to be immutable, would it be possible for the planner to microbenchmark a few different plans instead of trying to estimate the cost? As in, actually executing the query with different plans and caching the best one. PS: good job to the FOSDEM Devroom organisers, one of the best rooms, was a blast. Best regards, Cen