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 1mDw2g-00012h-F9 for pgsql-performance@arkaria.postgresql.org; Wed, 11 Aug 2021 21:45:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mDw2e-00025M-Hm for pgsql-performance@arkaria.postgresql.org; Wed, 11 Aug 2021 21:45:40 +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 1mDw2e-00025D-86 for pgsql-performance@lists.postgresql.org; Wed, 11 Aug 2021 21:45:40 +0000 Received: from mail-qt1-x831.google.com ([2607:f8b0:4864:20::831]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mDw2b-0001J9-Uj for pgsql-performance@lists.postgresql.org; Wed, 11 Aug 2021 21:45:39 +0000 Received: by mail-qt1-x831.google.com with SMTP id d9so3313813qty.12 for ; Wed, 11 Aug 2021 14:45:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=7MI3YWr6gFdu+eglN94rbnSDVhGjZ02WLxyN7iQWTQw=; b=kszxn5uRD0IhRduVH3GLIJgxrWRTfZn0dUWz4yNKmhVAMGYXcALmH480Q08QRCw5Ce XjwtEM5hpXwkyKB8HPebQUm9BSU0WpycC5oNW6YfvqifMrgCBJmhvBnYuoJmnPVb6WWH P0aUK6x4L98ftxDf9KxpIx3+ObKHvk0TsIEzM8t93g3TsNZsrFBzX7RLY7baXQZTcWaE eNQ08ExVn7xX1y7ElcU+6y4h7d3CCpz1G5vmwMkJH603M0dMF1qIRspGRwei4BQvs9sj pBBj62bmZCxP83DYIp9wy6V910fwyxFUUiY/eao1FeJdjLl19i/ZA12iOz7aQ4e/Hmg/ 5jkw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=7MI3YWr6gFdu+eglN94rbnSDVhGjZ02WLxyN7iQWTQw=; b=ObE4OayJOGLkoAFDG3o99kYPvHadMlnO+gK+0oubkm8izfDW1CU0LOJpUKWYHcydYt fRW0LSao8VGedP0qNtqBKwcfgarI/peotTtqW8inZj7p7nb50KUqfpowVtNNVJLK2jND Whk0nJ3t4rwJ/MeeIY0u+oTuaJ22dWHzw5+fAcHLzqsyqDpMtiq1lCTNLSsMHQOj7mX+ dSLQdgyaAxOs8z1z/qkjlL0fNCRAQsdjwq6SFSQS0bhCE4TFMDCpPtR/lx5sAMfM4vV+ E6XYY0pQ9MM+zrjRd3Jdpoelbwy48jKledKke53LMdMEiTk6c5S46OxoHDXMmPAOJMn0 qoJQ== X-Gm-Message-State: AOAM5303E0I9z6BM9QVPPHc/eX9rDuFRaQsfJ0CErwZN6EcZHJMzqjjm T8+DUln+zMv6F7jPC+05ZXZVqajaLCLE7g== X-Google-Smtp-Source: ABdhPJwXSx+YT1crdgR4OLlFKdBpDqe4uHYvM/gV2lCCKbFB0LouVOhR9M6+nFMtBhYOCtpnBVuyRg== X-Received: by 2002:ac8:7d0c:: with SMTP id g12mr835689qtb.152.1628718335694; Wed, 11 Aug 2021 14:45:35 -0700 (PDT) Received: from [192.168.2.100] (pool-100-8-54-81.nwrknj.fios.verizon.net. [100.8.54.81]) by smtp.gmail.com with ESMTPSA id z6sm224334qkl.72.2021.08.11.14.45.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 11 Aug 2021 14:45:35 -0700 (PDT) Subject: Re: Postgres using the wrong index index To: pgsql-performance@lists.postgresql.org References: <20210811123856.GE10479@telsasoft.com> From: Mladen Gogala Message-ID: <4db19bdf-ab40-ed68-4897-c9b328272421@gmail.com> Date: Wed, 11 Aug 2021 17:45:34 -0400 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6E4E89B131570A912E2669F8" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------6E4E89B131570A912E2669F8 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit You know that you can use pg_hint_plan extension? That way you don't have to disable indexes or set session parameters. Regards On 8/11/21 3:56 PM, Matt Dupree wrote: > Thanks for your response, Justin! > > Here's the plan if we disable the > custom_2 index. It uses the index I expect and it's much faster. > > Here's a plan if we disable index > scans. It uses both indexes and is much faster. > -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------6E4E89B131570A912E2669F8 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit

You know that you can use pg_hint_plan extension? That way you don't have to disable indexes or set session parameters.

Regards

On 8/11/21 3:56 PM, Matt Dupree wrote:
Thanks for your response, Justin!

Here's the plan if we disable the custom_2 index. It uses the index I expect and it's much faster.

Here's a plan if we disable index scans. It uses both indexes and is much faster.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------6E4E89B131570A912E2669F8--