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 1pSgvM-0006Cb-7Q for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 16:15:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSgvL-0004kt-3j for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 16:15:55 +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 1pSgvK-0004hk-MI for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 16:15:54 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pSgvI-0008Q7-Ec for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 16:15:53 +0000 Received: by mail-ot1-x32a.google.com with SMTP id e12-20020a0568301e4c00b0068bc93e7e34so747120otj.4 for ; Thu, 16 Feb 2023 08:15:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=FiHKMBpqz/T5Un8/hhOHN5i4Tnj6o7e0/QqvSPukay4=; b=Ol1hEBzUluHC2SD1eQkzl3sOoQhWQlGMXsS94rZ4gQgv5GCIEzxvNeWU4NRu1/QrxV Z6DdXPfBO1+GkDlwT6053uwd3bL2B4r9UYEfLbuQ6qYfarJBMN6iVidpcsbfJRaGT7Ws 3gbcYRcvf+eXOVJ7p8ibllyq9/CwLcSdmGAgmhGZd5pZQJ81klcAb+dvi4VA5VsKvVTn 0fcTzBU4E3PT99EGXuA2p4n/A0L76PwMo1Y7wBS4UeZhs30WuoUux+PIIctuIuScYlRO A5I5AMSiHEnsX5olnSQrA6uurg8YXISfz03geuhoyql0E8qj+RRnfhXktp5Qz21McBZS hbAA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=FiHKMBpqz/T5Un8/hhOHN5i4Tnj6o7e0/QqvSPukay4=; b=uqcgCuCpTo4V+uFNCCKXsxQhN7fbO9h13Mlsim3cc9e7+AsM9TEVB+SSso+d3jasMM mIxpksXNEqFcbUi0MAqpRGqm9b3gyA/qFYsbZjx5hqYvNHNWwUc10ZxxC0q2jv1gJa7H C3ZyyHK6EfhyMjmb00Tis3zmNf1vfA0rEVErgRXg4Tbx7sDW+qCA1LT03hSxQy2kIbnQ PjLmGar6tKKA8YJei9pfe/nYgcfzi1DruA42mRt+ZGwuUjEUtJ5uQFHEPk4zylyyhbtt /1NVeearn3huY1cUUvxe794oQ6d7pHoXB13AYltOs5JB4f5flxG9WHZpXVO1mowLn/Yp f1fg== X-Gm-Message-State: AO0yUKWftWpopAO9wLo47hW+K07frxSK0xm30BZYMuZwnZAurJhtnGov HZO8gek5LV7U18YUFvrJfFWqdr3zFUY= X-Google-Smtp-Source: AK7set8Mj8GcsvhxeQ8nPMiWHi2DQZ+8m/ri3eC4qqosBFZ64drf9ra98tL3+lilC8G9gG+7Hpo7JA== X-Received: by 2002:a05:6830:1105:b0:68b:c06f:5e67 with SMTP id w5-20020a056830110500b0068bc06f5e67mr2791294otq.37.1676564151201; Thu, 16 Feb 2023 08:15:51 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id c8-20020a9d67c8000000b0068657984c22sm804925otn.32.2023.02.16.08.15.50 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 16 Feb 2023 08:15:50 -0800 (PST) Message-ID: <9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com> Date: Thu, 16 Feb 2023 10:15:50 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Postgres undeterministically uses a bad plan, how to convince it otherwise? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com> From: Ron In-Reply-To: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com> 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 On 2/16/23 09:47, cen wrote: > 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). What does EXPLAIN ANALYZE say? > 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. Since you've run ANALYZE, when were the tables last vacuumed? -- Born in Arizona, moved to Babylonia.