MIME-Version: 1.0
From: sud <suds1434@gmail.com>
Date: Sat, 7 Sep 2024 14:59:37 +0530
Message-ID: <CAD=mzVXjzrtMQVVucgWa-+W76trmvdAzqAFdA=v5QFnHk53Pqg@mail.gmail.com>
Subject: How effectively do the indexing in postgres in such cases
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000041a2240621842a92"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVXjzrtMQVVucgWa-%2BW76trmvdAzqAFdA%3Dv5QFnHk53Pqg%40mail.gmail.com>
Precedence: bulk

--00000000000041a2240621842a92
Content-Type: text/plain; charset="UTF-8"

Hello Experts,
We have a requirement in which the query will be formed like below. We will
have two partitioned tables joined and there may be filters used on both of
the tables columns or it may be one of those.

These types of queries are very frequently used queries and critical to
customers as these are part of search screens , so we want to have the
indexing happen effectively to satisfy these types of queries to return
rows in not more than ~1 seconds. The both tables are daily range
partitions on column "part_date" and the volume of data per day/partitions
will be ~700mllion in both the tables.

The customer can go searching for a duration starting from one days till
max ~1 month of data i.e. part_date spanning for ~1 month duration. And the
search should provide the latest transaction on the screen which is why
"order by ..limit clause is used". "Offset" is used there because the
customer can scroll through the next page on the UI where he has the
capability to see the next 100 rows and so on. In the first screen it is
also expected to see the count of the results , so that the customer can
get an immediate idea about the total count of transactions he has matching
his search criteria.

So ,
1)In the query below , if the optimizer chooses tab1 as the driving table,
the index on just col1 should be enough or it should be (col1, tab1_id)?
Similarly if it chooses the tab2 be the driving table then , index on
(col2,tab2_id). Or just indexing the filtered column should be enough like
individual indexes on COL1 and COL2 of table tab1 and tab2 respectively?

2)In scenarios where the customer has a lot of matching transactions (say
in millions) post all the filters applied , and as the customer has to just
see the latest 100 rows transaction data, the sorting will be a bottleneck.
So what can be done to make such types of queries to return the latest
transactions in quick time on the search screen?

3)As here also the count has to happen in the first step to make the
customer know the total number of rows(which may be in millions), so what
additional index will support this requirement?

Or if any other optimization strategy we can follow for catering to such
queries?

select * from tab1, tab2
where tab1.part_date between '1-jan-2024' and '31-jan-2024'
and tab1.part_date=tab2.part_date
and tab1.tab1_id=tab2.tab2_id
and tab1.col1=<:input_col1>
and tab2.col2=<:input_col2>
order by tab1.create_timestamp desc
limit 100 offset 100;

Regards
Sud

--00000000000041a2240621842a92
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello Experts,<div>We have a requirement in which the quer=
y will be formed like below. We will have two partitioned tables joined and=
 there may be filters used on both of the tables columns or it may be one o=
f those. <br><br>These types of queries are very frequently used queries an=
d critical to customers as these are part of search screens , so we want to=
 have the indexing happen effectively to satisfy these types of queries to =
return rows in not more than ~1 seconds. The both tables are daily range pa=
rtitions on column &quot;part_date&quot; and the volume of data per day/par=
titions will be ~700mllion in both the tables.<br><br>The customer can go s=
earching for a duration starting from one days till max ~1 month of data i.=
e. part_date spanning for ~1 month duration. And the search should provide =
the latest transaction on the screen which is why &quot;order by ..limit cl=
ause is used&quot;. &quot;Offset&quot; is used there because the customer c=
an scroll through the next page on the UI where he has the capability to se=
e the next 100 rows and so on. In the first screen it is also expected to s=
ee the count of the results , so that the customer can get an immediate ide=
a about the total count of transactions he has matching his search criteria=
.<br><br>So ,<br>1)In the query below , if the optimizer chooses tab1 as th=
e driving table, the index on just col1 should be enough or it should be (c=
ol1, tab1_id)? Similarly if it chooses the tab2 be the driving table then ,=
 index on (col2,tab2_id). Or just indexing the filtered column should be en=
ough like individual indexes on COL1 and COL2 of table tab1 and tab2 respec=
tively?<br><br>2)In scenarios where the customer has a lot of matching tran=
sactions (say in millions) post all the filters applied , and as the custom=
er has to just see the latest 100 rows transaction data, the sorting will b=
e a bottleneck. So what can be done to make such types of queries to return=
 the latest transactions in quick time on the search screen?<br><br>3)As he=
re also the count has to happen in the first step to make the customer know=
 the total number of rows(which may be in millions), so what additional ind=
ex will support this requirement?<br><br>Or if any other optimization strat=
egy we can follow for catering to such queries?<br><br>select * from tab1, =
tab2<br>where tab1.part_date between &#39;1-jan-2024&#39; and &#39;31-jan-2=
024&#39;<br>and tab1.part_date=3Dtab2.part_date <br>and tab1.tab1_id=3Dtab2=
.tab2_id<br>and tab1.col1=3D&lt;:input_col1&gt;<br>and tab2.col2=3D&lt;:inp=
ut_col2&gt;<br>order by tab1.create_timestamp desc <br>limit 100 offset 100=
;<div><br></div><div>Regards</div><div>Sud<br><br></div></div></div>

--00000000000041a2240621842a92--