Re: Slow response to my query - Mailing list pgsql-novice
| From | Goke Aruna |
|---|---|
| Subject | Re: Slow response to my query |
| Date | |
| Msg-id | CAE=DitryXyiMZh1q=QmsoFR7S6buHH4btdVaZRz_L0nZ8SQR4w@mail.gmail.com Whole thread Raw |
| In response to | Re: Slow response to my query (Steven Pousty <steve.pousty@gmail.com>) |
| Responses |
Re: Slow response to my query
Re: Slow response to my query |
| List | pgsql-novice |
The output of the pgbench on the database is as contained below:
The drive is SSD and the command that is mostly used is
- 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"
- 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)
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 anEXPLAINin 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.ThanksSteveOn 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
pgsql-novice by date: