[Beginners question :-) hopefully on-topic here.]

In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to speed it up. I'm interested in any kind of solution like restructuring the query, suggesting indexes or multicolumn indexes and index methods, replacing comparison operators with other similar functions, setting statistics to tables, etc. I'm not a big DB expert (yet) and I really need to understand where are the "low-hanging fruits" in such a performance tuning issues.

PostgreSQL version: v9.5.9

The problematic query:

  date_trunc('day',begin_time) as begin_day,
  (agent_first_name||' '||agent_last_name) as full_username,
  (user_name||' ( '||agent_first_name||' '||agent_last_name||' )') as full_username_long,

  coalesce(tnumberagloggedon,0) as tnumberagloggedon,
  coalesce(totalnumber,0) as totalnumber,
  coalesce(tnumberinbound,0) as tnumberinbound,
  coalesce(tnumberoutbound,0) as tnumberoutbound,
  coalesce(tnumberinternal,0) as tnumberinternal,
  coalesce(tnumbertransfersmade,0) as tnumbertransfersmade,
  coalesce(tnumberagworking,0) as tnumberagworking,
  coalesce(tnumberagworkingint,0) as tnumberagworkingint,
  coalesce(tnumberagworkinginb,0) as tnumberagworkinginb,
  coalesce(tnumberagworkingout,0) as tnumberagworkingout,
  coalesce(tnumberagringing,0) as tnumberagringing,
  coalesce(tnumberagringinginc,0) as tnumberagringinginc,
  coalesce(tnumberagringingout,0) as tnumberagringingout,
  coalesce(tnumberagready,0) as tnumberagready,
  coalesce(tnumberagnotready,0) as tnumberagnotready,
  coalesce(ttimeagloggedon,0) as ttimeagloggedon,
  coalesce(ttimeagloggedoff,0) as ttimeagloggedoff,
  coalesce(ttimeagworking,0) as ttimeagworking,
  coalesce(ttimeagworkingint,0) as ttimeagworkingint,
  coalesce(ttimeagworkinginb,0) as ttimeagworkinginb,
  coalesce(ttimeagworkingout,0) as ttimeagworkingout,
  coalesce(ttimeagringing,0) as ttimeagringing,
  coalesce(ttimeagringinginc,0) as ttimeagringinginc,
  coalesce(ttimeagringingout,0) as ttimeagringingout,
  coalesce(ttimeagacw,0) as ttimeagacw,
  coalesce(ttimeagready,0) as ttimeagready,
  coalesce(ttimeagnotready,0) as ttimeagnotready,
  coalesce(avtimeagready,0) as avtimeagready,
  coalesce(avtimeagnotready,0) as avtimeagnotready,
  coalesce(avtimeagacw,0) as avtimeagacw,
  coalesce(avtimeagworking,0) as avtimeagworking,
  coalesce(avtimeagworkingint,0) as avtimeagworkingint,
  coalesce(avtimeagworkinginb,0) as avtimeagworkinginb,
  coalesce(avtimeagworkingout,0) as avtimeagworkingout,
  coalesce(avtimeagringing,0) as avtimeagringing,
  coalesce(avtimeagringinginc,0) as avtimeagringinginc,
  coalesce(avtimeagringingout,0) as avtimeagringingout
 FROM   (((((stdata_user_noagg
        INNER JOIN agent_names
        ON stdata_user_noagg.object_id=agent_names.object_id) 
        INNER JOIN aux_mode_names 
        ON stdata_user_noagg.aux_mode=aux_mode_names.aux_mode)
        INNER JOIN object_groups 
        ON agent_names.object_id=object_groups.agent_id) 
        INNER JOIN object_names 
        ON object_groups.group_id=object_names.object_id) 
        INNER JOIN group_names_view
        ON object_names.object_name=group_names_view.object_name)
        INNER JOIN media_names
        ON stdata_user_noagg.media_type = media_names.media_type
 WHERE  (stdata_user_noagg.begin_time>='2019-01-30T 00:00:00' 
 AND stdata_user_noagg.begin_time<='2019-01-31T 23:59:59') 
 AND stdata_user_noagg.interval_time=60                 --static parameter
 AND stdata_user_noagg.filter=''                        --static parameter
 AND agent_names.is_alive=1                             --static parameter
 AND agent_names.agent_first_name<>'Test'               --static parameter
 AND group_names_view.object_name='GROUP1'

 WINDOW wbegin_time_agent as (partition by begin_time,user_name), 
           wbegin_time_agent_aux as (partition by begin_time, user_name, stdata_user_noagg.aux_mode),
           wbegin_time_agent_media as (partition by begin_time, user_name, stdata_user_noagg.media_type) 
        order by stdata_user_noagg.begin_time, user_name, media_type, stdata_user_noagg.aux_mode

I marked the static filter parameters. All other parameters are dynamically set by the users.


Sort  (cost=7230.63..7283.00 rows=20948 width=204) (actual time=19437.442..26832.238 rows=2430448 loops=1)
  Sort Key: stdata_user_noagg.begin_time, agent_names.user_name, stdata_user_noagg.media_type, stdata_user_noagg.aux_mode
  Sort Method: external merge  Disk: 639872kB
  ->  Hash Join  (cost=3.67..5727.14 rows=20948 width=204) (actual time=0.182..11645.153 rows=2430448 loops=1)
        Hash Cond: (stdata_user_noagg.media_type = media_names.media_type)
        ->  Hash Join  (cost=2.43..5018.90 rows=20948 width=196) (actual time=0.130..4807.762 rows=2430448 loops=1)
              Hash Cond: (stdata_user_noagg.aux_mode = aux_mode_names.aux_mode)
              ->  Nested Loop  (cost=1.27..4729.70 rows=20948 width=188) (actual time=0.108..3697.551 rows=2430448 loops=1)
                    ->  Nested Loop  (cost=0.83..7.84 rows=1 width=32) (actual time=0.087..0.884 rows=30 loops=1)
                          ->  Nested Loop  (cost=0.56..5.54 rows=1 width=39) (actual time=0.080..0.444 rows=30 loops=1)
                                ->  Nested Loop  (cost=0.42..5.34 rows=1 width=11) (actual time=0.073..0.131 rows=30 loops=1)
                                      ->  Index Scan using idx_obj_names_object_name on object_names  (cost=0.27..2.29 rows=1 width=11) (actual time=0.066..0.066 rows=1 loops=1)
                                            Index Cond: ((object_name)::text = 'CVM'::text)
                                      ->  Index Scan using idx_object_groups_group_id on object_groups  (cost=0.14..2.89 rows=16 width=8) (actual time=0.004..0.045 rows=30 loops=1)
                                            Index Cond: (group_id = object_names.object_id)
                                ->  Index Scan using idx_agent_names_object_id on agent_names  (cost=0.14..0.20 rows=1 width=28) (actual time=0.007..0.008 rows=1 loops=30)
                                      Index Cond: (object_id = object_groups.agent_id)
                                      Filter: (((agent_first_name)::text <> 'Test'::text) AND (is_alive = 1))
                          ->  Index Scan using idx_obj_names_object_name on object_names object_names_1  (cost=0.27..2.29 rows=1 width=7) (actual time=0.013..0.014 rows=1 loops=30)
                                Index Cond: ((object_name)::text = 'CVM'::text)
                                Filter: (type_id = 16)
                    ->  Index Scan using idx_stadata_usernoagg_objid on stdata_user_noagg  (cost=0.44..3833.87 rows=88799 width=168) (actual time=0.025..72.454 rows=81015 loops=30)
                          Index Cond: (object_id = agent_names.object_id)
                          Filter: ((begin_time >= '2018-01-01 00:00:00'::timestamp without time zone) AND (begin_time <= '2019-01-31 23:59:59'::timestamp without time zone) AND (interval_time = 60) AND ((filter)::text = ''::text))
              ->  Hash  (cost=1.07..1.07 rows=7 width=12) (actual time=0.008..0.008 rows=7 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                    ->  Seq Scan on aux_mode_names  (cost=0.00..1.07 rows=7 width=12) (actual time=0.001..0.003 rows=7 loops=1)
        ->  Hash  (cost=1.11..1.11 rows=11 width=12) (actual time=0.028..0.028 rows=11 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Seq Scan on media_names  (cost=0.00..1.11 rows=11 width=12) (actual time=0.017..0.020 rows=11 loops=1)
Planning time: 2.940 ms
Execution time: 28909.760 ms

It seems that the nested loops are the main problem-source but I don't know how to avoid them.

The table structure and indexes:

CREATE TABLE public.stdata_user_noagg
  id integer NOT NULL DEFAULT nextval('stdata_user_noagg_id_seq'::regclass),
  begin_time timestamp without time zone NOT NULL,
  interval_time integer NOT NULL,
  object_id integer NOT NULL,
  media_type integer,
  filter character varying(128),
  aux_mode integer,
  stat_time integer,
  tnumberinbound integer,
  tnumberinboundacd integer,
  tnumberoutbound integer,
  tnumberinternal integer,
  totalnumber integer,
  tnumbertransfersmade integer,
  tnumberconsults integer,
  tnumberconference integer,
  tnumberagworking integer,
  tnumberagworkingint integer,
  tnumberagworkinginb integer,
  tnumberagworkingout integer,
  tnumberagringing integer,
  tnumberagringinginc integer,
  tnumberagringingout integer,
  tnumberagready integer,
  tnumberagnotready integer,
  tnumberagacw integer,
  tnumberagloggedon integer,
  tnumberagloggedoff integer,
  tnumberdistributedtoagent integer,
  tnumberdistributedtoagintime integer,
  tnumberansweredbyagent integer,
  tnumberansweredbyagintime integer,
  tnumberabandonedintime integer,
  tnumberabandonedfromringing integer,
  tnumberconsultsmade integer,
  tnumberconsultsreceived integer,
  tnumbercallonhold integer,
  ttimeagloggedon integer,
  ttimeagloggedoff integer,
  ttimeagworking integer,
  ttimeagworkingint integer,
  ttimeagworkinginb integer,
  ttimeagworkingout integer,
  ttimeagringing integer,
  ttimeagringinginc integer,
  ttimeagringingout integer,
  ttimeagacw integer,
  ttimeagready integer,
  ttimeagnotready integer,
  ttimeinconsultation integer,
  ttimeinconference integer,
  ttimecallonhold integer,
  mtimeagready integer,
  mtimeagnotready integer,
  mtimeagacw integer,
  mtimeagworking integer,
  mtimeagworkingint integer,
  mtimeagworkinginb integer,
  mtimeagworkingout integer,
  mtimeagringing integer,
  mtimeagringinginc integer,
  mtimeagringingout integer,
  mtimeagloggedon integer,
  mtimeagloggedoff integer,
  maxtimeinconsultation integer,
  maxtimeinconference integer,
  maxtimecallonhold integer,
  avtimeagready integer,
  avtimeagnotready integer,
  avtimeagacw integer,
  avtimeagworking integer,
  avtimeagworkingint integer,
  avtimeagworkinginb integer,
  avtimeagworkingout integer,
  avtimeagringing integer,
  avtimeagringinginc integer,
  avtimeagringingout integer,
  avtimeagloggedon integer,
  avtimeagloggedoff integer,
  avtimeinconsultation integer,
  avtimeinconference integer,
  avtimecallonhold integer,
  CONSTRAINT stdata_user_noagg_pkey PRIMARY KEY (id)

CREATE INDEX idx_stadata_usernoagg_begintime
  ON public.stdata_user_noagg
  USING btree

CREATE INDEX idx_stadata_usernoagg_objid
  ON public.stdata_user_noagg
  USING btree

CREATE INDEX idx_stdata_user_noagg_aux_mode
  ON public.stdata_user_noagg
  USING btree

CREATE TABLE public.agent_names
  object_id integer NOT NULL,
  user_name character varying(128) NOT NULL,
  agent_first_name character varying(128),
  agent_last_name character varying(128),
  login_id integer,
  is_alive integer NOT NULL,
  last_update_time timestamp without time zone,
  CONSTRAINT agent_names_pkey PRIMARY KEY (object_id)

CREATE INDEX idx_agent_names_object_id
  ON public.agent_names
  USING btree
CREATE TABLE public.aux_mode_names
  aux_mode integer NOT NULL,
  aux_mode_name character varying(128) NOT NULL,
  is_alive integer NOT NULL,
  deleted_time timestamp without time zone

CREATE INDEX idx_aux_mode_names_aux_mode
  ON public.aux_mode_names
  USING btree
CREATE TABLE public.object_groups
  group_id integer NOT NULL,
  group_type integer NOT NULL,
  agent_id integer NOT NULL

CREATE INDEX idx_object_groups_agent_id
  ON public.object_groups
  USING btree

CREATE INDEX idx_object_groups_group_id
  ON public.object_groups
  USING btree

CREATE TABLE public.object_names
  object_id integer NOT NULL,
  type_id integer NOT NULL,
  object_name character varying(128) NOT NULL,
  is_alive integer NOT NULL,
  last_update_time timestamp without time zone

CREATE INDEX idx_obj_names_object_name
  ON public.object_names
  USING btree
  (object_name COLLATE pg_catalog."default");

CREATE INDEX idx_object_names_obj_id
  ON public.object_names
  USING btree
CREATE OR REPLACE VIEW public.group_names_view AS 
 SELECT object_names.object_name
   FROM object_names
  WHERE object_names.type_id = 16;
CREATE TABLE public.media_names
  media_type integer NOT NULL,
  media_name character varying(128) NOT NULL,
  is_alive integer NOT NULL,
  deleted_time timestamp without time zone

The real mass of data is in stdata_user_noagg (might be millions of rows).

If necessary I can include the postgresql.conf as well but I don't think the problem is on that level.

  • 1
    Well, difficult to say but it seems that the planner did a wrong estimation of number of rows (Nested Loop (cost=1.27..4729.70 rows=20948 width=188) (actual time=0.108..3697.551 rows=2430448 loops=1) means it estimated 20948 rows and actually got 2430448 rows). Could you perform an analyze on your tables involved in the query ?
    – Arkhena
    Jan 21, 2019 at 16:58
  • 1
    And of course (and i'm sure you know that), you should consider upgrading to postgres 11 to have better performances (and partitioning)
    – Arkhena
    Jan 21, 2019 at 16:59
  • Upgrade is not possible at the moment unfortunately. I made an analyze and I'll repeat the query tomorrow (today the customer closed the remote access).
    – wjozsi
    Jan 21, 2019 at 19:50
  • The ANALYZE did not help. It seems that adding COLLATE "C" to the user_name at ORDER BY makes it about 16% quicker but a running time of ~5 minutes is still not acceptable for a three month query. Looking closer at the sort I see that it needed ~640MB for the external merge on disk. That's awful lot of space and for sure not normal (and not possible to add as working_mem). The query has to be optimized somehow.
    – wjozsi
    Jan 24, 2019 at 14:05
  • 1
    This parameters seem to be set too low. So I think you really need to tune your postgres.You can read the excellent annotated.conf project from Josh Berkus here : github.com/jberkus/annotated.conf It should explain to you everything you need to know.
    – Arkhena
    Jan 25, 2019 at 8:46


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.