Re: Slow response to my query
От | Bzzzz |
---|---|
Тема | Re: Slow response to my query |
Дата | |
Msg-id | 20191129150944.43000ccf@msi.defcon1.lan обсуждение исходный текст |
Ответ на | Re: Slow response to my query (Goke Aruna <goksie@gmail.com>) |
Список | 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
В списке pgsql-novice по дате отправления: