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 1siaGQ-006bDu-6U for pgsql-hackers@arkaria.postgresql.org; Mon, 26 Aug 2024 14:00:10 +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 1siaGN-00AUmI-8S for pgsql-hackers@arkaria.postgresql.org; Mon, 26 Aug 2024 14:00:07 +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 1siaGM-00AUmA-VO for pgsql-hackers@lists.postgresql.org; Mon, 26 Aug 2024 14:00:07 +0000 Received: from mail-lj1-x22c.google.com ([2a00:1450:4864:20::22c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1siaGK-001Wta-Ts for pgsql-hackers@lists.postgresql.org; Mon, 26 Aug 2024 14:00:06 +0000 Received: by mail-lj1-x22c.google.com with SMTP id 38308e7fff4ca-2f4f2868621so40098151fa.0 for ; Mon, 26 Aug 2024 07:00:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724680803; x=1725285603; 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=cZbX5fqTrrs/J9+uhFS/x7nmmZc9Z/gHevWSYB0bJIc=; b=jPp9/KDttnwaJ/YQpijwJRGvRk1zVdfTCgcALsVTryGiPCkraACiEPO5lI8wtXIKqg 2Db30V2OjmfB607RF0X8J/Izstxm9s1+sbdMQ8I93w4hcWYhI5uOi4jelKIkbdASe+sS g+DNrsr5NDSsczs0MEuVTBk3B2Q8OYRQep2/EYzpSY2OYjgq9YiH2Ai20pk9lS3UCR3R 7cI8ZKbRiYxNqC+TitA6OM6vGnVlBrVqo3AriVIyuOmIV8LsMNgvHFizZYnJk/xSHxI8 niE1oFw0QTPE3G22HPSaccTQaK8U+PUzmjjYuwBS/n37qS9A/oiyE8LCFLXeJ8ytprtM voFA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724680803; x=1725285603; 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=cZbX5fqTrrs/J9+uhFS/x7nmmZc9Z/gHevWSYB0bJIc=; b=W/pPc683f1WmbxLMlOibO2rC6XYZ5p4V7v53irA0TQnuODNKBAoa2WWlFOGgQcEIb4 SGU2TsBM4SjwkbhynFtd4cum55EonTXUpKr9sBKmlgvrEcF2lvszBllPzQJ6meZlKEFJ +I2dglEYPDCJoA5dTEJq4m7B9GqNSwQGJGc8zSbEx2btKHetZxjew7yGdh60ToXz7DK/ tapRHbdVwhqAbvj3yaEFKSaDPnc02vfQUbG+SE2st8RseFMWJ8gNYqpQfWcMqeA0PW9b quxK3kSZR7OirQHANd83n8ELgjQ9tQTfP6QPJBUw+BC/qOoaj9WtMewS4BHv0spn4/mD dlzg== X-Forwarded-Encrypted: i=1; AJvYcCV210xiVP0QRWhYcz7DxO/qSZC4SKYr/xyx7JUN+vMIPUx3JxItavHB04xUrWDFYNZq/8OcHb29hk05FVyd@lists.postgresql.org X-Gm-Message-State: AOJu0YwiYfaAYLuciS2poasykCv5gU5XWLF6UQwF8JdjsGvW0tVDV272 6fXuNDmHAVDyD3YTLJyTNYM2LJ0hiudDw7Gd90kKsGwxvm8gvXGo X-Google-Smtp-Source: AGHT+IEaSmNqAjDMTn3gv3IRO/AIkKISK6yqT48moM0nVN/qkbB7Vz6wqwAKANN0Z4LWYBzlQGwhwQ== X-Received: by 2002:a05:651c:2049:b0:2f3:ec7b:8fe2 with SMTP id 38308e7fff4ca-2f4f57b7cd4mr60521441fa.47.1724680802027; Mon, 26 Aug 2024 07:00:02 -0700 (PDT) Received: from [1.0.0.7] ([91.185.76.193]) by smtp.gmail.com with ESMTPSA id 38308e7fff4ca-2f40487f7d8sm12766081fa.110.2024.08.26.07.00.00 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 26 Aug 2024 07:00:01 -0700 (PDT) Message-ID: <6c68ac42-bbb5-8b24-103e-af0e279c536f@gmail.com> Date: Mon, 26 Aug 2024 17:00:00 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Content-Language: en-US To: Peter Geoghegan Cc: Matthias van de Meent , PostgreSQL Hackers , Tom Lane , Tomas Vondra , Jeff Davis , benoit , Alexander Korotkov , Heikki Linnakangas References: <0539d3d3-a402-0a49-ed5e-26429dffc4bd@gmail.com> From: Alexander Lakhin In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hello Peter, 22.04.2024 20:59, Peter Geoghegan wrote: > > Pushed a fix like that just now. I'm sorry to bother you again, but I've come across another assertion failure. Please try the following query (I use a clean "postgres" database, just after initdb): EXPLAIN SELECT conname   FROM pg_constraint WHERE conname IN ('pkey', 'ID')   ORDER BY conname DESC; SELECT conname   FROM pg_constraint WHERE conname IN ('pkey', 'ID')   ORDER BY conname DESC; It fails for me as below:                                                      QUERY PLAN --------------------------------------------------------------------------------------------------------------------  Index Only Scan Backward using pg_constraint_conname_nsp_index on pg_constraint  (cost=0.14..4.18 rows=2 width=64)    Index Cond: (conname = ANY ('{pkey,ID}'::name[])) (2 rows) server closed the connection unexpectedly ... with the stack trace: ... #5  0x000055a49f81148d in ExceptionalCondition (conditionName=0x55a49f8bb540 "ItemIdHasStorage(itemId)",     fileName=0x55a49f8bb4a8 "../../../../src/include/storage/bufpage.h", lineNumber=355) at assert.c:66 #6  0x000055a49f0f2ddd in PageGetItem (page=0x7f97cbf17000 "", itemId=0x7f97cbf2f064)     at ../../../../src/include/storage/bufpage.h:355 #7  0x000055a49f0f9367 in _bt_checkkeys_look_ahead (scan=0x55a4a0ac4548, pstate=0x7ffd1a103670, tupnatts=2,     tupdesc=0x7f97cb5d7be8) at nbtutils.c:4105 #8  0x000055a49f0f8ac3 in _bt_checkkeys (scan=0x55a4a0ac4548, pstate=0x7ffd1a103670, arrayKeys=true,     tuple=0x7f97cbf18890, tupnatts=2) at nbtutils.c:3612 #9  0x000055a49f0ebb4b in _bt_readpage (scan=0x55a4a0ac4548, dir=BackwardScanDirection, offnum=20, firstPage=true)     at nbtsearch.c:1863 ... (gdb) f 7 #7  0x000055a49f0f9367 in _bt_checkkeys_look_ahead (scan=0x55a4a0ac4548, pstate=0x7ffd1a103670, tupnatts=2,     tupdesc=0x7f97cb5d7be8) at nbtutils.c:4105 4105            ahead = (IndexTuple) PageGetItem(pstate->page, (gdb) p aheadoffnum $1 = 24596 (gdb) p pstate->offnum $2 = 20 (gdb) p pstate->targetdistance $3 = -24576 Best regards, Alexander