Re: Slow response to my query - Mailing list pgsql-novice

On Fri, 29 Nov 2019 12:09:04 +0100
Goke Aruna <goksie@gmail.com> wrote:

> The* drive is SSD* and the command that is mostly used is

Ok.

>    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"*
>
>
> 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

Why are regular [lower case] columns identifiers in between double-quotes
that are normally used to process weird column names such as :
"CamelColName"?
(takes time to process.)

Why are call_type & og_carrier in the plain instead of being foreign
keys?
(integer are usually processed faster, not to mention carriers names
have good chances to be used elsewhere in the DB.)

Why do you force an implicite cast of a double precision float
(pg_typeof(EXTRACT(month FROM callday))) to a string ('11') in your
comparison??
(cast is a very costly operation)

As the EXTRACT/2 Fn is very much used & you have 500M rows (wild
guess as it is a phone carrier app: still growing), you logically have
created all corresponding indexes on your table??
(if you where using Pg V.12, you would be able to create auto-generated
columns auto-calculating these values, which wouldn't spare you the
indexes, but would definitely spare the EXTRACT/2 calculation time
which is very costly in this context.)

>    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" *
>
>
> 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

Same questions as above.

> *PGBENCH*
[…]
Seems quite correct for a SSD.

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

Steve's point is the next step.

Jean-Yves