Re: Slow response to my query
Goke Aruna <[email protected]> Fri, 29 Nov 2019 12:09:04 +0100
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Message-ID | <CAE=DitryXyiMZh1q=QmsoFR7S6buHH4btdVaZRz_L0nZ8SQR4w@mail.gmail.com> |
--000000000000d8cc6f05987a429a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
The output of the pgbench on the database is as contained below:
The* drive is SSD* and the command that is mostly used is
1. *select in_carrier, og_carrier, sum(ceil_duration) as
ceil_duration_aggr from "allcalls" where "call_type" =3D 'INTL' and
"og_carrier" in ('9MOBILE', 'AIRTEL', 'GLO', 'MTN') and extract(month fr=
om
"callday") =3D '11' and extract(year from "callday") =3D 2019 group by
"in_carrier", "og_carrier"*
2. * select in_carrier, og_carrier, sum(ceil_duration) as
ceil_duration_aggr from "allcalls" where extract(month from "callday") =
=3D
'11' and extract(year from "callday") =3D 2019 group by "in_carrier",
"og_carrier" *
*PGBENCH*
bash-4.2$ pgbench -c 5 -j 2 -t 20000 mybill
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 2
number of transactions per client: 20000
number of transactions actually processed: 100000/100000
latency average =3D 0.796 ms
tps =3D *6281*.067340 (including connections establishing)
tps =3D *6282*.242375 (excluding connections establishing)
bash-4.2$ pgbench -c 5 -j 2 -t 200000 mybill
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 2
number of transactions per client: 200000
number of transactions actually processed: 1000000/1000000
latency average =3D 0.846 ms
tps =3D *5911*.740108 (including connections establishing)
tps =3D* 5911*.854614 (excluding connections establishing)
On Thu, Nov 28, 2019 at 5:12 PM Steven Pousty <[email protected]>
wrote:
> Sounds like you should do an
> EXPLAIN
> in front of your query and see what the query planner is thinking. If you
> can spare the 1.5 hours do an EXPLAIN ANALYZE. It should be safe as long =
as
> you are only doing a select query.
> Thanks
> Steve
>
> On Thu, Nov 28, 2019 at 7:13 AM Bzzzz <[email protected]> wrote:
>
>> On Thu, 28 Nov 2019 10:38:22 +0100
>> Goke Aruna <[email protected]> wrote:
>>
>> > however, do you have ant specific test to run with the pgbench?
>>
>> Nope, I don't have the man in mind - read it and adapt your test to your
>> problem.
>>
>> BTW, you did not say it, but I suppose you're using rust not SSDz?
>>
>> > I have about 6 of my columns indexed.
>>
>> Are they involved in your query?
>>
>> > I will share the test query once i got the queries from the UI man.
>>
>> ? Without the table and it's indexes structures plus the query, it's lik=
e
>> a car without an engine (or a chauffeur)=E2=80=A6
>>
>> Also, read this:
>> https://hakibenita.com/be-careful-with-cte-in-postgre-sql
>> and that:
>> https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
>>
>> Jean-Yves
>>
>>
>>
--000000000000d8cc6f05987a429a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div dir=3D"ltr">The output of the pgbench on the database=
is as contained below:</div><div dir=3D"ltr"><br></div><div>The<b> drive i=
s SSD</b> and the command that is mostly used is <br></div><ol><li>
<b><font color=3D"#274e13">select in_carrier, og_carrier,=20
sum(ceil_duration) as ceil_duration_aggr from "allcalls" where=20
"call_type" =3D 'INTL' and "og_carrier" in (=
9;9MOBILE', 'AIRTEL', 'GLO',=20
'MTN') and extract(month from "callday") =3D '11'=
and extract(year from=20
"callday") =3D 2019 group by "in_carrier", "og_car=
rier"</font></b>
</li><li><b>
<i><font color=3D"#274e13">select in_carrier, og_carrier,=20
sum(ceil_duration) as ceil_duration_aggr from "allcalls" where=20
extract(month from "callday") =3D '11' and extract(year f=
rom "callday") =3D=20
2019 group by "in_carrier", "og_carrier"</font></i> </b=
></li></ol><div dir=3D"ltr"><br></div><div><b>PGBENCH</b><br></div><div dir=
=3D"ltr">bash-4.2$ =C2=A0pgbench -c 5 -j 2 -t 20000 mybill<br>starting vacu=
um...end.<br>transaction type: <builtin: TPC-B (sort of)><br>scaling =
factor: 1<br>query mode: simple<br>number of clients: 5<br>number of thread=
s: 2<br>number of transactions per client: 20000<br>number of transactions =
actually processed: 100000/100000<br>latency average =3D 0.796 ms<br>tps =
=3D <b>6281</b>.067340 (including connections establishing)<br>tps =3D <b>6=
282</b>.242375 (excluding connections establishing)<br><br>bash-4.2$ =C2=A0=
pgbench -c 5 -j 2 -t 200000 mybill<br>starting vacuum...end.<br>transaction=
type: <builtin: TPC-B (sort of)><br>scaling factor: 1<br>query mode:=
simple<br>number of clients: 5<br>number of threads: 2<br>number of transa=
ctions per client: 200000<br>number of transactions actually processed: 100=
0000/1000000<br>latency average =3D 0.846 ms<br>tps =3D <b>5911</b>.740108 =
(including connections establishing)<br>tps =3D<b> 5911</b>.854614 (excludi=
ng connections establishing)<br></div><div><br></div><div><br></div><div><b=
r></div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On=
Thu, Nov 28, 2019 at 5:12 PM Steven Pousty <<a href=3D"mailto:steve.pou=
[email protected]" target=3D"_blank">[email protected]</a>> wrote:<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><di=
v>Sounds like you should do an <br></div><div>EXPLAIN <br></div><div>in fro=
nt of your query and see what the query planner is thinking. If you can spa=
re the 1.5 hours do an EXPLAIN ANALYZE. It should be safe as long as you ar=
e only doing a select query.</div><div>Thanks</div><div>Steve<br></div></di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On T=
hu, Nov 28, 2019 at 7:13 AM Bzzzz <<a href=3D"mailto:[email protected]" =
target=3D"_blank">[email protected]</a>> wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex">On Thu, 28 Nov 2019 10:38:22 +0100<br>
Goke Aruna <<a href=3D"mailto:[email protected]" target=3D"_blank">goksie=
@gmail.com</a>> wrote:<br>
<br>
> however, do you have ant specific test to run with the pgbench?<br>
<br>
Nope, I don't have the man in mind - read it and adapt your test to you=
r<br>
problem.<br>
<br>
BTW, you did not say it, but I suppose you're using rust not SSDz?<br>
<br>
> I have about 6 of my columns indexed.<br>
<br>
Are they involved in your query?<br>
<br>
> I will share the test query once i got the queries from the UI man.<br=
>
<br>
? Without the table and it's indexes structures plus the query, it'=
s like<br>
a car without an engine (or a chauffeur)=E2=80=A6<br>
<br>
Also, read this:<br>
<a href=3D"https://hakibenita.com/be-careful-with-cte-in-postgre-sql" rel=
=3D"noreferrer" target=3D"_blank">https://hakibenita.com/be-careful-with-ct=
e-in-postgre-sql</a><br>
and that:<br>
<a href=3D"https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/" rel=3D"n=
oreferrer" target=3D"_blank">https://www.2ndquadrant.com/en/blog/on-rocks-a=
nd-sand/</a><br>
<br>
Jean-Yves<br>
<br>
<br>
</blockquote></div>
</blockquote></div></div>
--000000000000d8cc6f05987a429a--