MIME-Version: 1.0
References: <CAD=mzVXjzrtMQVVucgWa-+W76trmvdAzqAFdA=v5QFnHk53Pqg@mail.gmail.com>
In-Reply-To: <CAD=mzVXjzrtMQVVucgWa-+W76trmvdAzqAFdA=v5QFnHk53Pqg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 9 Sep 2024 11:33:21 -0400
Message-ID: <CAKAnmm+gWeaeFs9+bb-KespdByKmJky+=-am2Lfp-RRLuw7AaA@mail.gmail.com>
Subject: Re: How effectively do the indexing in postgres in such cases
To: sud <suds1434@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005c72950621b17c75"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BgWeaeFs9%2Bbb-KespdByKmJky%2B%3D-am2Lfp-RRLuw7AaA%40mail.gmail.com>
Precedence: bulk

--0000000000005c72950621b17c75
Content-Type: text/plain; charset="UTF-8"

Your questions are a little too vague to answer well, but let me try a bit.

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)?
>

No way to tell without trying it yourself. We need information on how the
tables are joined, the cardinality, general distribution, etc. But as a
rough general rule, yes, indexes on the column of interest should be able
to handle the job well by themselves.

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?
>

Sorting can be quick, if you hit an index (b-trees are already sorted)
Postgres can look at only the first X rows returned and does not need to
read the whole thing. So a well-designed index is the key here.


> 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?
>

Again, a very vague question, but for things that are in millions, an
estimate is usually sufficient, so you might be able to do something like
SELECT count(*) FROM mytab WHERE mydate BETWEEN x AND y; and use that as
your answer. Compare to the full query to see how close it is. You might
even have cutoffs, where if the results of that first one is < 10,000,
switch to a more accurate version which has more filtering (i.e. the joins
and more where conditions).


> 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;
>

It probably would help to see exact tables and queries. Why are you joining
on part_date? Is tab_id unique to either table? Running EXPLAIN on these
while you try out indexes and change the joins, etc. is a great exercise to
help you learn how Postgres works. As far as asking on lists for help with
specific queries, there is a range between totally abstract and generic
queries that nobody can help you with, and large, complex specific queries
that nobody wants to unravel and help you with. You are definitely on the
former side: try to create some actually runable sample queries that are
small, self-contained, and generate the problem you are trying to solve.

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr">Your questions are a little too vague to =
answer well, but let me try a bit.</div><div dir=3D"ltr"><br></div><div cla=
ss=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"ltr">1)In the query below , if the optimizer chooses tab1 as the driv=
ing table, the index on just col1 should be enough or it should be (col1, t=
ab1_id)?</div></blockquote><div><br></div><div>No way to tell without tryin=
g it yourself. We need information on how the tables are joined, the cardin=
ality, general distribution, etc. But as a rough general rule, yes, indexes=
 on the column of interest should be able to handle the job well by themsel=
ves.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div dir=3D"ltr">2)In scenarios where the customer has a lot of matching tr=
ansactions (say in millions) post all the filters applied , and as the cust=
omer 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 retu=
rn the latest transactions in quick time on the search screen?<br></div></b=
lockquote><div><br></div><div>Sorting can be quick, if you hit an index (b-=
trees are already sorted) Postgres can look at only the first X rows return=
ed and does not need to read the whole thing. So a well-designed index is t=
he key here.</div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div dir=3D"ltr">3)As here also the count has to happen in th=
e first step to make the customer know the total number of rows(which may b=
e in millions), so what additional index will support this requirement?<br>=
</div></blockquote><div><br></div><div>Again, a very vague question, but fo=
r things that are in millions, an estimate is usually sufficient, so you mi=
ght be able to do something like SELECT count(*) FROM mytab WHERE mydate BE=
TWEEN x AND y; and use that as your answer. Compare to the full query to se=
e how close it is. You might even have cutoffs, where if the results of tha=
t first one is &lt; 10,000, switch to a more accurate version which has mor=
e filtering (i.e. the joins and more where conditions).</div><div>=C2=A0<br=
></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;=
border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">O=
r if any other optimization strategy we can follow for catering to such que=
ries?<br><br>select * from tab1, tab2<br>where tab1.part_date between &#39;=
1-jan-2024&#39; and &#39;31-jan-2024&#39;<br>and tab1.part_date=3Dtab2.part=
_date <br>and tab1.tab1_id=3Dtab2.tab2_id<br>and tab1.col1=3D&lt;:input_col=
1&gt;<br>and tab2.col2=3D&lt;:input_col2&gt;<br>order by tab1.create_timest=
amp desc <br>limit 100 offset 100;</div></blockquote><div><br></div><div>It=
 probably would help to see exact tables and queries. Why are you joining o=
n part_date? Is tab_id unique to either table? Running EXPLAIN on these whi=
le you try out indexes and change the joins, etc. is a great exercise to he=
lp you learn how Postgres works. As far as asking on lists for help=C2=A0wi=
th specific queries, there is a range between totally abstract and generic =
queries that nobody can help you with, and large, complex specific queries =
that nobody wants to unravel and help you with. You are definitely on the f=
ormer side: try to create some actually runable=C2=A0sample queries that ar=
e small, self-contained, and generate the problem you are trying to solve.<=
/div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div><div>=
=C2=A0</div></div></div>

--0000000000005c72950621b17c75--