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 1sGAYO-00A1Kd-SR for pgsql-general@arkaria.postgresql.org; Sun, 09 Jun 2024 04:53:17 +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 1sGAXO-002eQn-GF for pgsql-general@arkaria.postgresql.org; Sun, 09 Jun 2024 04:52:15 +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 1sGAXO-002eQd-0c for pgsql-general@lists.postgresql.org; Sun, 09 Jun 2024 04:52:15 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sGAXL-000Q1p-VA for pgsql-general@lists.postgresql.org; Sun, 09 Jun 2024 04:52:13 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-a6ef8e62935so154008966b.3 for ; Sat, 08 Jun 2024 21:52:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1717908730; x=1718513530; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=CHm34+0ELInCRPCuMLVW26A+TskRV8OKjDkZRiYQcVA=; b=RXb6FYaxDBhiCUPJGJNr8u4kvl5VGhaUuvMmSZ/fbWgmJUKByMFss/JOPayhW0giRe GXRCkerp5YcuHE0s+XzkyIBQPAKzCQlkgFi3idqOB8iCQA0ZcCoMQBAB1Mep3EFhBmbC iHfNnXRtU+5vXgpqGt0l5Ah/KrN52gG4vPZUhBrLOVMKszBsXU0fhZ/HH5RCTFmfcz8R VNXz7LgKmcuaX4FzryKkW7NE7DyojfBnucnEZ8VpzRvZRl4SZDEhqK6OGZY8Txjttuy1 SB6ezGPvZC5VRacfrDdgjWgR4p9uQn3rJGJIvVLZeB7myaxq3Ba7dPsJY1SukgCf6sfs jCKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717908730; x=1718513530; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=CHm34+0ELInCRPCuMLVW26A+TskRV8OKjDkZRiYQcVA=; b=oXjoJdtlUTWGR1BwMZXejF7P6PJFmlqXYA14io3j2/ACt2JjLVd6rp8uLiPUMu6QvW ECUrMRnIhKbxNl+UQ1GXqfhlv3400F5cOwX7U7MH/o/gt8xFLbXDj3XtDUIrLIsR122y fDkOTPYuunnzyuQzC6VwHQ2FNdoFqYBHt1BhkmG/QAz3JPHrhh3hy4Hu/kc+fpZ2hI4H 3AJ1r4ISDcYm0+wkTwTSpOOkdWG96GWo38LO0GqfytBvfIeawT1elj61W1ccZPDlT4bW 7H6eHL9aS6ZloA3Y9u88jt503pUA9inDvy8agaYB/yisqVGnxOfqjv04a1m3kO1rjpJQ LOKA== X-Gm-Message-State: AOJu0YzIkMsY5MVqDtn+w5D9lo4YdntzjyfrHHxkE47IDOoHj4nrEy32 HK9YJQvF60ibvZ5gt9di4Ni1E0Vao2OBNfE0XVpUX/YLodeMjPcDd/9bg5Z6evV9COWgn9V5QZR NnZvIWasWuv6OFpMbkxRgkn287Ok= X-Google-Smtp-Source: AGHT+IFxpNzHQT0OXnFtDjxjao8JssmEYq9MFyMVRA5soZfRb5CYGJr/88kD3VDfA0HOn3HkHVYD+j56/8Er7Q2+BTk= X-Received: by 2002:a17:907:1c05:b0:a6d:de5b:5b1d with SMTP id a640c23a62f3a-a6dde5b5d36mr552871966b.18.1717908730185; Sat, 08 Jun 2024 21:52:10 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: veem v Date: Sun, 9 Jun 2024 10:21:55 +0530 Message-ID: Subject: Re: How to create efficient index in this scenario? To: Lok P Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000009e8f6f061a6dcbc5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009e8f6f061a6dcbc5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, 9 Jun 2024 at 09:45, Lok P wrote: > > On Sat, Jun 8, 2024 at 7:03=E2=80=AFPM veem v wrote= : > >> >> There is a blog below (which is for oracle), showing how the index shoul= d >> be chosen and it states , "*Stick the columns you do range scans on >> last in the index, filters that get equality predicates should come firs= t.* >> ", and in that case we should have the PK created as in the order >> (transaction_id,transaction_timestamp). It's because making the range >> predicate as a leading column won't help use that as an access predicate >> but as an filter predicate thus will read more blocks and thus more IO. >> Does this hold true in postgres too? >> >> >> https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of-indexi= ng/ >> > > I believe the analogy holds true here in postgres too and the index in > this case should be on (transaction_id, transaction_timestamp). > > >> >> >> Additionally there is another scenario in which we have the requirement >> to have another timestamp column (say create_timestamp) to be added as p= art >> of the primary key along with transaction_id and we are going to query t= his >> table frequently by the column create_timestamp as a range predicate. An= d >> ofcourse we will also have the range predicate filter on partition key >> "transaction_timestamp". But we may or may not have join/filter on colum= n >> transaction_id, so in this scenario we should go for >> (create_timestamp,transaction_id,transaction_timestamp). because >> "transaction_timestamp" is set as partition key , so putting it last >> doesn't harm us. Will this be the correct order or any other index order= is >> appropriate? >> >> >> > In this case , the index should be on ( > create_timestamp,transaction_id,transaction_timestamp), considering the > fact that you will always have queries with "create_timestamp" as predica= te > and may not have transaction_id in the query predicate. > So in the second scenario, if we keep the create_timestamp as the leading column ,is it not against the advice which the blog provides i.e. to not have the range predicate as the leading column in the index? --0000000000009e8f6f061a6dcbc5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Sun, 9 Jun 2024 at 09:45, Lok P <loknath.73@gmail.com> wrote:
=

On Sat, Jun 8, 2024 at 7:03=E2=80=AFPM veem v <veema0000@gmail.com&g= t; wrote:

There is a blog below (which is for oracle), showing ho= w the index should be chosen and it states , =C2=A0"Stick the colum= ns you do range scans on last in the index, filters that get equality predi= cates should come first. ", and in that case we should have the PK= created as in the order (transaction_id,transaction_timestamp). It's b= ecause making the range predicate as a leading column won't help use th= at as an access predicate but as an filter predicate thus will read more bl= ocks and thus more IO. Does this hold true in postgres too?

https://ctandrewsayer.wordpress.com/2017/03/24/the-= golden-rule-of-indexing/

I = believe=C2=A0the analogy holds true here in postgres too and the index in t= his case should be on (transaction_id, transaction_timestamp).
=C2=A0


Additionally there is another scenario in which we have = the requirement to have another timestamp column (say create_timestamp) to = be added as part of the primary key along with transaction_id and we are go= ing to query this table frequently by the column create_timestamp as a rang= e predicate. And ofcourse we will also have the range predicate filter on p= artition key "transaction_timestamp". But we may or may not have = join/filter on column transaction_id, so in this scenario we should go for = =C2=A0(create_timestamp,transaction_id,transaction_timestamp). because &quo= t;transaction_timestamp" is set as partition key , so putting it last = doesn't harm us. Will this be the correct order or any other index orde= r is appropriate?



In this case , the index should be on ( create_timesta= mp,transaction_id,transaction_timestamp), considering the fact that you wil= l always=C2=A0have queries with "create_timestamp" as predicate a= nd may not have transaction_id in the query predicate.

So in the second scenario, if we keep the cre= ate_timestamp as the leading column ,is it not against the advice which the= blog provides i.e. to not have the range predicate as the leading column i= n the index?
--0000000000009e8f6f061a6dcbc5--