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 1thT2C-002nEN-PI for pgsql-performance@arkaria.postgresql.org; Mon, 10 Feb 2025 12:37:09 +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 1thT1D-000tmC-5a for pgsql-performance@arkaria.postgresql.org; Mon, 10 Feb 2025 12:36: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 1thT1C-000tm3-MB for pgsql-performance@lists.postgresql.org; Mon, 10 Feb 2025 12:36:06 +0000 Received: from mail-wm1-x32d.google.com ([2a00:1450:4864:20::32d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1thT19-0001YJ-0Q for pgsql-performance@lists.postgresql.org; Mon, 10 Feb 2025 12:36:05 +0000 Received: by mail-wm1-x32d.google.com with SMTP id 5b1f17b1804b1-4394036c0efso6631325e9.2 for ; Mon, 10 Feb 2025 04:36:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1739190961; x=1739795761; darn=lists.postgresql.org; 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=zh6l9Vxg4B83PnaG1LlxlV8tH2Zb+aUT6pc+j6zYsfE=; b=lxMiijp2ZSWsF7Mv2DrfAvscwYLgUM1185blrXf7DLyrN90J2ZjjfwQUhcz7yTvTTW X7xKohKDTAcNYaDUcCwHsvBDrcsWMnv3/O2ZfmUWUfxFh/WbOzo+EntVrPyhDZV7+L16 LXJX2gRYZzj3dp/a6MBM3VQH1XqiB1FtrqAiyM97TA9LP/6Lb/r/p8FuDb7myi8WpK0P zQmEDipt2VCcE5dkdKKpiUvhIuXVuHRSdyDu2DzmmZNb+q7M/FQnSssRczz8B8K0t7bR LQbK8rY96tsz6HR46cLUJs2XBF5lGxjjSNS1vbIhYrgMq7rJ79hKLBRtBEKVRbipoqwH Caaw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1739190961; x=1739795761; 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=zh6l9Vxg4B83PnaG1LlxlV8tH2Zb+aUT6pc+j6zYsfE=; b=YCJvpxIlEjmnSimqZTDD2MzcyWOrg+dO/tJDGYetlasibcQ55byJzt5S1XXpMsoVIy Tc3OQYwq47+ls/DuxAJjqhp2bAyQfxwXGAI83RU677EJFc7Sv76PwUKpSIEZKaIpIXo/ dju4jRGD1mjMxloJrTjnU5waYVjmC6CncFgdNpE2LYj15UyrME0PDeo5qnK5TN8Xw+PG AYn4FSuVpjuRQ3K15Cm5AFzPPk0RY7y15xPMiWzXWTN7CFKlfWc5k4OedWDjKdtgPfJv d7kWTWMFdkmZxmHPcJp1wg2Z9MmruvV/N6PXtmOzzf1WxbASob1b2lKnVOxvCA6pp/TP 0grw== X-Gm-Message-State: AOJu0YwiKxPES+taP2clpyf2MmZWmKWsOqLOCvYw75Tjku31VmOKPypI OdyoWdAnPvwqkjpN8whAGdxPWfdUj16RMGZ2tLQ3JLZ5R5mAcmaGMDD5DcNIYgo= X-Gm-Gg: ASbGnctz+Jz0mOG6fZECLPNmyvDdWK8xR5e5U6v3h3jhlukglthhv1MGh7XJnGWCgPY zpP7KE8K4g8I++WerZ8Y30+XQVzbJgl0BteTrdWUvNnHswx3EDb/0oJImLkEkeGbKKddg3tl1lu 16pLl3dEr5/GGqXTmkyWa7iqpg6Fyfe/sD/BIGQ7tk14KEpvD3eQgNjz7ATL/1/KdV1EreMMCs9 7YtpDX5QWhBk6ELyweQ1SxJ72Hx4ubZdvIHDUNMUbY1sdyzm/teztezzeLu+YuXzywGb0VxGrDI YHbpYaEjzoltlIWx8x3iLRxOhkz/rfBcmyU= X-Google-Smtp-Source: AGHT+IFoIpTTuEEKjZwe6gQtDq1XkPst80fXeCVTndJ/H0TSeJaf2kcPDxc//3OvRMIyYWTQajtAeQ== X-Received: by 2002:a5d:64eb:0:b0:38d:b2bd:ae9 with SMTP id ffacd0b85a97d-38dc935febemr8944013f8f.49.1739190961561; Mon, 10 Feb 2025 04:36:01 -0800 (PST) Received: from localhost.localdomain ([2001:871:5e:99f3:4e2f:cbcb:7a29:4748]) by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-4391dcae129sm143237995e9.17.2025.02.10.04.36.00 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 10 Feb 2025 04:36:01 -0800 (PST) Message-ID: Subject: Re: Poor performance with row wise comparisons From: Laurenz Albe To: Jon Emord , Greg Sabino Mullane Cc: "pgsql-performance@lists.postgresql.org" Date: Mon, 10 Feb 2025 13:36:00 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2025-02-07 at 16:16 +0000, Jon Emord wrote: > explain (analyze, buffers) > select data_model_id, primary_key > from entity > WHERE (data_model_id, primary_key) BETWEEN (123, =E2=80=98ABC=E2=80=99) A= ND (123, =E2=80=98DEF=E2=80=99)=C2=A0 > limit 101; > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------- > =C2=A0Limit=C2=A0 (cost=3D0.70..6.37 rows=3D101 width=3D31) (actual time= =3D0.094..2712.844 rows=3D100 loops=3D1) > =C2=A0=C2=A0 Buffers: shared hit=3D97259 > =C2=A0=C2=A0 ->=C2=A0 Index Only Scan using entity_data_model_id_primary_= key_uniq on entity=C2=A0 (cost=3D0.70..873753.60 rows=3D15581254 width=3D31= ) (actual time=3D0.093..2712.836 rows=3D100 loops=3D1) > =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: ((ROW(data_model_id, primar= y_key) >=3D ROW(123, 'ABC'::text)) AND (ROW(data_model_id, primary_key) <= =3D ROW(123, 'DEF'::text))) > =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Heap Fetches: 4 > =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D97259 > =C2=A0Planning: > =C2=A0=C2=A0 Buffers: shared hit=3D104 > =C2=A0Planning Time: 0.204 ms > =C2=A0Execution Time: 2712.873 ms How is the index "entity_data_model_id_primary_key_uniq" defined? Yours, Laurenz Albe