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 1rnSJa-00DMrG-Oy for pgsql-bugs@arkaria.postgresql.org; Thu, 21 Mar 2024 23:59:19 +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 1rnSIa-00HAYC-Vo for pgsql-bugs@arkaria.postgresql.org; Thu, 21 Mar 2024 23:58:17 +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 1rnSIa-00HAY3-MQ for pgsql-bugs@lists.postgresql.org; Thu, 21 Mar 2024 23:58:17 +0000 Received: from mail-wm1-x32a.google.com ([2a00:1450:4864:20::32a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rnSIW-005kuF-6E for pgsql-bugs@lists.postgresql.org; Thu, 21 Mar 2024 23:58:15 +0000 Received: by mail-wm1-x32a.google.com with SMTP id 5b1f17b1804b1-4146e5c719bso11990785e9.2 for ; Thu, 21 Mar 2024 16:58:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1711065492; x=1711670292; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=+uZFwVRCZxqoBXcp9WjhLvHS25QKKi1dinsxaqGOBeo=; b=OOvG7uv1IEcdKTPLYSF6B8hyGWj1TG47DIIokLWNPx6Ia+QodNKYqc7sDarJvZmXZD s0u2hIFeEaWEde7AhhjAe7QZ6iaodC6BLaHJ+w6NVu4UzctiWTAMkKiGSQTTxlLIoEWG mkzPmSrQtTEvtAH1j3qAEmgjsnZfXn7BcOCLy0bZdIMqEFHmhpLxqDMkMeCe/G+evTmG tyi2e03h6sNA2uqophgLPINCoSd3VSd06POLEtY/uqszxB7afVwQtsAjpNrfGBYLMjsC Cz6XAggbFs1nrM39UTecR8iOXxEhtmByte22huAmh8VNWl8zPjtorU6ju7y58k379Wna K4GA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711065492; x=1711670292; h=content-transfer-encoding:in-reply-to:from:references:cc: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=+uZFwVRCZxqoBXcp9WjhLvHS25QKKi1dinsxaqGOBeo=; b=Q/5yHWJg/ROPrFLnGNr1rQOEgnaf/YNF+BIJlBHGsYlcyA+z59E0D80/W6Mabe0o8R wv7+GRvNhbjVNjvBqWbt5Y530+wZAt0flBwU1NuHyViH7ueIoWkqJXo8mrvZd4u5YkIh y6Fgg/7CrAheECE/tvDOm6mtO/bGGfnwtM6iF30M8cM89NYulxHiVAEFd/i6TzCcxi31 JYn28mOeIE8ftt0Cg07oHkQfZqtrta7dc3QfLWEoOtj99LE3xqkJ5T6O5CzqW1z0pqUm iRkcZ7//VmLU5olUxH4dTE4Wu1ZqwxgwoaHViTM2JoZgNy3fb2nBpUK0hctv8QEZJQkX GyzA== X-Gm-Message-State: AOJu0Yw1QjzA+FOberIHcEmyPn5EabZP/dDMx/OlgmtDiixx0eoxY447 3swdIfKhVeZ9gVSEeytECo9XFqiY8HSfvX4HOAGPfh4dVA4NuX7bwRcbsjneOg== X-Google-Smtp-Source: AGHT+IE0gqpBJOx2HhYF/jm/1A1EiWS5XZqM/7qC2SDwXyZ/Kgl6kk/94e6PpeUjmQOtZ0Dt46CzyA== X-Received: by 2002:a05:600c:3b83:b0:414:204:df50 with SMTP id n3-20020a05600c3b8300b004140204df50mr398453wms.4.1711065491727; Thu, 21 Mar 2024 16:58:11 -0700 (PDT) Received: from [10.137.0.18] (ip-86-49-229-30.bb.vodafone.cz. [86.49.229.30]) by smtp.gmail.com with ESMTPSA id v10-20020a05600c470a00b004128fa77216sm7074073wmo.1.2024.03.21.16.58.11 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 21 Mar 2024 16:58:11 -0700 (PDT) Message-ID: <27496161-e7ec-47bd-b559-3571fde4dcb4@enterprisedb.com> Date: Fri, 22 Mar 2024 00:58:09 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Index plan returns different results to sequential scan Content-Language: en-US To: John Burns Cc: pgsql-bugs@lists.postgresql.org References: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com> <72600f8a-4231-45b7-a116-8ceec5ccf66e@enterprisedb.com> <881DF028-5222-486C-A13B-451239C1316F@impactdatametrics.com> From: Tomas Vondra In-Reply-To: <881DF028-5222-486C-A13B-451239C1316F@impactdatametrics.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/21/24 21:08, John Burns wrote: > > >> On 21 Mar 2024, at 20:27, Tomas Vondra wrote: >> >> >> Sorry about that … the patched version of the package, plus a sample data set is in the attached zip file, >> >> -John >> Thanks, I can reproduce the issue. I don't know why is it happening, but the behavior I see is that the (postcode % 'NW10') query somehow misses rows with postcodes 'NW10 0AA' - 'NW10 3NL' when executed using index. I don't see anything obviously wrong in the extension / index pages. You suggested it used to work OK and then broke. Do you perhaps know at which version it broke? Or did you use 9.x until now, upgrades to 16 and realized it's broken? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company