Re: Slow response to my query

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" = 'INTL' and
   "og_carrier" in ('9MOBILE', 'AIRTEL', 'GLO', 'MTN') and extract(month from
   "callday") = '11' and extract(year from "callday") = 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") =
   '11' and extract(year from "callday") = 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 = 0.796 ms
tps = *6281*.067340 (including connections establishing)
tps = *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 = 0.846 ms
tps = *5911*.740108 (including connections establishing)
tps =* 5911*.854614 (excluding connections establishing)



On Thu, Nov 28, 2019 at 5:12 PM Steven Pousty <steve.pousty@gmail.com>
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 <lazyvirus@gmx.com> wrote:
>
>> On Thu, 28 Nov 2019 10:38:22 +0100
>> Goke Aruna <goksie@gmail.com> 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 like
>> a car without an engine (or a chauffeur)…
>>
>> 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
>>
>>
>>