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 1qNxxa-006SKW-J1 for pgsql-general@arkaria.postgresql.org; Mon, 24 Jul 2023 15:58:58 +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 1qNxxZ-00BeiM-0m for pgsql-general@arkaria.postgresql.org; Mon, 24 Jul 2023 15:58:57 +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 1qNxxY-00BeiD-IS for pgsql-general@lists.postgresql.org; Mon, 24 Jul 2023 15:58:56 +0000 Received: from mail-wm1-x32f.google.com ([2a00:1450:4864:20::32f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qNxxW-001L3N-0s for pgsql-general@lists.postgresql.org; Mon, 24 Jul 2023 15:58:55 +0000 Received: by mail-wm1-x32f.google.com with SMTP id 5b1f17b1804b1-3fbc244d384so36341075e9.0 for ; Mon, 24 Jul 2023 08:58:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1690214332; x=1690819132; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:from:to:cc:subject :date:message-id:reply-to; bh=OneNvMtMK9mQt32+G4vHEwj3eY78NMfWVruC0UyRcBg=; b=0DiCq2GSoAw6xgEZCmH5qKyqvu6xKGLAP1iIDaS1EzFHXbgm8icnq1oz/elfECagnH xzws8dwtuagO35t/mboTLZHV0zb4cNdnQFPMn/ekDThe5s6GT2viQDJEdDwHpLlOI/C7 0XI3HV3j/0A/A4pyX+9W+0vzWAEvbDKGpfgkF/DMJGTmObJCq6cQqKyNpcDiNiAHnJ8k u6C+EzlNYDYcHt2Kyc3ylOeFz6e5+UQztPPUu/l5x1DJX3UcfJNJdNauZXIbh78dTXK3 mVRtXsfHn02bg6np3zcgfmU0hP7RKUK+GOkVny3YOm5GW9pGVn9jZtjW4hTVDLJJCK6S nD3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1690214332; x=1690819132; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=OneNvMtMK9mQt32+G4vHEwj3eY78NMfWVruC0UyRcBg=; b=GL0lQKpydjteTtUHXYK4EJvypDhsoWk8iIlhnQV7/hE6OukN4iVU7J/zmKntvSGQIp KttH7hEr03YWy2tkGi0D5OJoYlAFdTR5+JbLeUiLOT/z5wCMJQUhBlHTwerFalmGLKYk rAaGQvCzADIw3QOvkbS35sNstaaNRhdi/B5x1q9iG4n9T9qGaKHzYgoL0Qhy7e4Y7EzC 5CXH4SNUttDB63qzU2+E/MfG2PrROoxC/y4LeHShFA3ReXY/cVULBu3/hZ18WtQ5i0Yv obHPeN/sSY6ZrIVx+iIohyJK4XLOm6CXcC/mWesm1C/Czh1z7FrUJkGHjpMqrf/8kOXX Ia9w== X-Gm-Message-State: ABy/qLZqxv+yHlHOpvVpHBMcZN6edy03PselEZqEEhVCENiwd4QvV+DY 35lz2MTxeR4bShDiit/HSjkxng== X-Google-Smtp-Source: APBJJlF7D/zgJT80t4gcyJ6DNqVjr/LQBk3hjxQqgGpE4kXd+RPMdiG8RSsYFOv12qy7xlEEN8ktrw== X-Received: by 2002:a05:600c:2214:b0:3fb:b3aa:1c89 with SMTP id z20-20020a05600c221400b003fbb3aa1c89mr6887984wml.19.1690214331724; Mon, 24 Jul 2023 08:58:51 -0700 (PDT) Received: from localhost.localdomain ([213.208.157.40]) by smtp.gmail.com with ESMTPSA id w3-20020a1cf603000000b003fd2eba5a9esm6167772wmc.5.2023.07.24.08.58.50 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 24 Jul 2023 08:58:51 -0700 (PDT) Message-ID: Subject: Re: How to improve the performance of my SQL =?UTF-8?Q?query=EF=BC=9F?= From: Laurenz Albe To: gzh Cc: pgsql-general@lists.postgresql.org Date: Mon, 24 Jul 2023 17:58:50 +0200 In-Reply-To: <66883f7d.6d50.189875236b2.Coremail.gzhcoder@126.com> References: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com> <369ee851ef59bc84190f1631219b5a0c8f7907e9.camel@cybertec.at> <1937d056.71e9.18973a752d0.Coremail.gzhcoder@126.com> <6f3fd2f9.12b3.189761e1ccc.Coremail.gzhcoder@126.com> <4ae89a642b8ee57b8fdcd2f6e106aadc1eb60a4b.camel@cybertec.at> <66883f7d.6d50.189875236b2.Coremail.gzhcoder@126.com> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2023-07-24 at 17:54 +0800, gzh wrote: > EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON)=C2=A0 I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ... Or do you have a version that is too old for SETTINGS? One other idea: check if the index is INVALID (this will be visible if you run "\d tablenane" in "psql"). Invalid indexes won't be used. Yours, Laurenz Albe