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 1rnO4B-00D1JB-Kr for pgsql-bugs@arkaria.postgresql.org; Thu, 21 Mar 2024 19:27:08 +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 1rnO4A-00FrxO-5m for pgsql-bugs@arkaria.postgresql.org; Thu, 21 Mar 2024 19:27:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnO49-00Frx8-Sg for pgsql-bugs@lists.postgresql.org; Thu, 21 Mar 2024 19:27:06 +0000 Received: from mail-wr1-x430.google.com ([2a00:1450:4864:20::430]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rnO47-005mTn-Gv for pgsql-bugs@lists.postgresql.org; Thu, 21 Mar 2024 19:27:05 +0000 Received: by mail-wr1-x430.google.com with SMTP id ffacd0b85a97d-33ececeb19eso702112f8f.3 for ; Thu, 21 Mar 2024 12:27:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1711049221; x=1711654021; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id:from :to:cc:subject:date:message-id:reply-to; bh=HSPYnvtHWOyDaEaVwBoS5gQ3e9atuocHOmKS/2jKxXY=; b=H6pflwAiOPt7MsyvEr/EDFMz50vscqqDgVX/izfGamYWnJhNW0sCS5QXBtJXQpl/wl jAjCj+ZoqL7cisIMVIaVbAzmHCBd2yNAj5X/N5yOxaelhxQ2YAlHWaXSa5ihQ0DbRuI8 6GUkUZEmALkmjvLVP6YKyXnTtIkQvW+sYaYy9foxHpIdNdH+cAEN6iEtB7KBbQiFsmR2 8kIRgyVfJ5C4fmr84aUX7tIzK3n4c1fswLAmhR+i+osx6dEen9f2DxtJkyy5tyKgqIXb 0OSR5d3om4gksJkoNNCqE32aNDxywzXuREkMWYHnrACEvEWexsAdV2zsUFfUUN7bvUUP x+BQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711049221; x=1711654021; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=HSPYnvtHWOyDaEaVwBoS5gQ3e9atuocHOmKS/2jKxXY=; b=D0lWBxyWQvkGrxhHHjvq1fkaK46Xaq2FPhCdHtpfO5ucpuypK6grijkKLxicMyM/sQ zO3OLRvK7DnPs44A+cYA1Lzv20CDcZh1baK7kVvCqM8qujopylJobp2+bq1iNjpfINdT 2HYPiy0fL+U69zc1AMDkmWzFr15Ra0MulxWhkPIPUmxA1ZuJmOPDGuM7R3aG2FCYuYcg slTWMn3A/T7ELNPOkygbfilkQqrq+6cfQJSdX2RZXnrwMBlAx94LVc3QuBhHOXmNilGV Dhg5vYBHoRyh/uPT2yOh7GNT/OqLEEvKbO2GBDxftCaAg8IZBxP70DZqvWyirJ8Lb9At U4tw== X-Forwarded-Encrypted: i=1; AJvYcCV9PK7QfNfKOdv1dkUKtA3HiOz1I+6gBEttJIV+j3E2FZTvVsUmu7Q7V/PTDQEwWHcUwD69xuIbChBZa5KffpPoF+6r/XcvzXwZMwe6tOPO X-Gm-Message-State: AOJu0YxTfxpW1B3gZnQ3tcfBZ+7xkKtjX7pHs8e5V0xtW/dXmOwQVDG8 BnpXEx+mz2uYIE8+HVj3vQcE1uODoUoF9+HXW+EMfrDFpOSGBMG03wietJiG/Wga53awMVjtoiE = X-Google-Smtp-Source: AGHT+IFhlAVOZjbetwxdiudq4B4dHPT6mqtW0yRJv/vYvt3H68xy06mDJFbYe+LZbtGTx3el4Cld7g== X-Received: by 2002:a05:6000:1087:b0:33e:67c7:e2c0 with SMTP id y7-20020a056000108700b0033e67c7e2c0mr77807wrw.38.1711049221550; Thu, 21 Mar 2024 12:27:01 -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 v19-20020adfa1d3000000b0033dec836ea6sm296577wrv.99.2024.03.21.12.27.01 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 21 Mar 2024 12:27:01 -0700 (PDT) Message-ID: <72600f8a-4231-45b7-a116-8ceec5ccf66e@enterprisedb.com> Date: Thu, 21 Mar 2024 20:27:00 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Index plan returns different results to sequential scan To: John Burns , pgsql-bugs@lists.postgresql.org References: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com> Content-Language: en-US From: Tomas Vondra In-Reply-To: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@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 18:25, John Burns wrote: > Hi… > > ... > > Although the postcode package is probably not especially significant > in the scheme of things , the behaviour of indexed versus non-indexed > queries is worrisome. > Seems like a thinko in the indexing, but that's just a guess. > I’ve had to make a couple of minor changes to the postcode package > as the Postgres versions have changed, i.e. define TRUE and FALSE in > the c header files and change the location of the include files when > Postgres 16 arrived, but nothing else. Can you share a patch with the tweaks? > > I’ve attached a sample data file to populate a table that exhibits > the behaviour, and the tweaked version of the Postcode package. > I don't see any attachments :-( regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company