1

I have a table with 7 million tuples which looks like this:

                                                          Table "public.tbl_db_hashcode"
  Column      |           Type           | Collation | Nullable |                   Default                   | Storage  | Stats target | Description 
 ------------------+--------------------------+-----------+----------+---------------------------------------------+----------+--------------+-------------
 id               | integer                  |           | not null | nextval('tbl_db_hashcode_id_seq'::regclass) | plain    |              | 
 inserttime       | timestamp with time zone |           | not null | now()                                       | plain    |              | 
 buildid          | text                     |           | not null |                                             | extended |              | 
 app_version      | text                     |           |          |                                             | extended |              | 
 device           | text                     |           |          |                                             | extended |              | 
 device_api       | text                     |           |          |                                             | extended |              | 
 userid           | text                     |           |          |                                             | extended |              | 
 request_name     | text                     |           |          |                                             | extended |              | 
 hash_code        | bigint                   |           | not null |                                             | plain    |              | 
 request_duration | numeric                  |           |          |                                             | main     |              | 
 count_number     | integer                  |           | not null | 1                                           | plain    |              | 
 foreground       | integer                  |           | not null | 0                                           | plain    |              | 
 background       | integer                  |           | not null | 0                                           | plain    |              | 
 worker_thread    | integer                  |           | not null | 0                                           | plain    |              | 
 main_thread      | integer                  |           | not null | 0                                           | plain    |              | 
 status           | integer                  |           | not null | 0                                           | plain    |              | 
Partition key: RANGE (inserttime)
Partitions: tbl_db_hashcode_20180729 FOR VALUES FROM ('2018-07-29 00:00:00+07') TO ('2018-07-30 00:00:00+07'),
            tbl_db_hashcode_20180730 FOR VALUES FROM ('2018-07-30 00:00:00+07') TO ('2018-07-31 00:00:00+07'),
            tbl_db_hashcode_20180731 FOR VALUES FROM ('2018-07-31 00:00:00+07') TO ('2018-08-01 00:00:00+07'),
            tbl_db_hashcode_20180801 FOR VALUES FROM ('2018-08-01 00:00:00+07') TO ('2018-08-02 00:00:00+07'),
            tbl_db_hashcode_20180802 FOR VALUES FROM ('2018-08-02 00:00:00+07') TO ('2018-08-03 00:00:00+07'),
            tbl_db_hashcode_20180803 FOR VALUES FROM ('2018-08-03 00:00:00+07') TO ('2018-08-04 00:00:00+07'),
            tbl_db_hashcode_20180804 FOR VALUES FROM ('2018-08-04 00:00:00+07') TO ('2018-08-05 00:00:00+07'),
            tbl_db_hashcode_20180805 FOR VALUES FROM ('2018-08-05 00:00:00+07') TO ('2018-08-06 00:00:00+07'),
            tbl_db_hashcode_20180806 FOR VALUES FROM ('2018-08-06 00:00:00+07') TO ('2018-08-07 00:00:00+07'),
            tbl_db_hashcode_20180807 FOR VALUES FROM ('2018-08-07 00:00:00+07') TO ('2018-08-08 00:00:00+07'),
            tbl_db_hashcode_20180808 FOR VALUES FROM ('2018-08-08 00:00:00+07') TO ('2018-08-09 00:00:00+07'),
            tbl_db_hashcode_20180809 FOR VALUES FROM ('2018-08-09 00:00:00+07') TO ('2018-08-10 00:00:00+07'),
            tbl_db_hashcode_20180810 FOR VALUES FROM ('2018-08-10 00:00:00+07') TO ('2018-08-11 00:00:00+07'),
            tbl_db_hashcode_20180811 FOR VALUES FROM ('2018-08-11 00:00:00+07') TO ('2018-08-12 00:00:00+07'),
            tbl_db_hashcode_20180812 FOR VALUES FROM ('2018-08-12 00:00:00+07') TO ('2018-08-13 00:00:00+07'),
            tbl_db_hashcode_20180813 FOR VALUES FROM ('2018-08-13 00:00:00+07') TO ('2018-08-14 00:00:00+07'),
            tbl_db_hashcode_20180814 FOR VALUES FROM ('2018-08-14 00:00:00+07') TO ('2018-08-15 00:00:00+07'),
            tbl_db_hashcode_20180815 FOR VALUES FROM ('2018-08-15 00:00:00+07') TO ('2018-08-16 00:00:00+07'),
            tbl_db_hashcode_20180816 FOR VALUES FROM ('2018-08-16 00:00:00+07') TO ('2018-08-17 00:00:00+07'),
            tbl_db_hashcode_20180817 FOR VALUES FROM ('2018-08-17 00:00:00+07') TO ('2018-08-18 00:00:00+07'),
            tbl_db_hashcode_20180818 FOR VALUES FROM ('2018-08-18 00:00:00+07') TO ('2018-08-19 00:00:00+07'),
            tbl_db_hashcode_20180819 FOR VALUES FROM ('2018-08-19 00:00:00+07') TO ('2018-08-20 00:00:00+07'),
            tbl_db_hashcode_20180820 FOR VALUES FROM ('2018-08-20 00:00:00+07') TO ('2018-08-21 00:00:00+07'),
            tbl_db_hashcode_20180821 FOR VALUES FROM ('2018-08-21 00:00:00+07') TO ('2018-08-22 00:00:00+07'),
            tbl_db_hashcode_20180822 FOR VALUES FROM ('2018-08-22 00:00:00+07') TO ('2018-08-23 00:00:00+07'),
            tbl_db_hashcode_20180823 FOR VALUES FROM ('2018-08-23 00:00:00+07') TO ('2018-08-24 00:00:00+07'),
            tbl_db_hashcode_20180824 FOR VALUES FROM ('2018-08-24 00:00:00+07') TO ('2018-08-25 00:00:00+07'),
            tbl_db_hashcode_20180825 FOR VALUES FROM ('2018-08-25 00:00:00+07') TO ('2018-08-26 00:00:00+07'),

Now I want to select some values but the query is incredibly slow:

explain analyze
Select 
a.buildid,
hashcode.request_name , 
a.totalAffectUser,
a.count_number,
a.totalDuration,
a.totalForeGround 
FROM (
    SELECT buildid,
           hash_code,
           array_length(array_agg(distinct userid),1) as totalAffectUser, 
           SUM(count_number) as count_number,
           SUM(request_duration) as totalDuration ,
           sum(foreground) as totalForeGround
    FROM          
    tbl_db_hashcode    
     WHERE inserttime >= '2018-08-26 08:58:08.813' AND inserttime < '2018-08-28 08:58:08.814'  
    AND buildid like '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'  
    GROUP BY hash_code,buildid   
    ORDER BY SUM(request_duration)/SUM(count_number) DESC  LIMIT 10
) a
INNER JOIN
tbl_db_hashcode as hashcode
ON a.hash_code = hashcode.hash_code
WHERE hashcode.inserttime >= '2018-08-26 08:58:08.813' AND 
hashcode.inserttime < '2018-08-28 08:58:08.814' 
AND hashcode.buildid like '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'  ;

Gather  (cost=532532.00..634634.61 rows=100498 width=222) (actual time=24621.422..25594.576 rows=6977 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Hash Join  (cost=531532.00..623584.81 rows=41874 width=222) (actual time=24243.046..25278.780 rows=2326 loops=3)
     Hash Cond: (hashcode.hash_code = a.hash_code)
     ->  Append  (cost=0.00..88493.51 rows=837482 width=141) (actual time=0.095..949.127 rows=670084 loops=3)
           ->  Parallel Seq Scan on tbl_db_hashcode_20180826 hashcode  (cost=0.00..8853.74 rows=79636 width=141) (actual time=0.094..76.940 rows=63694 loops=3)
                 Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
                 Rows Removed by Filter: 3722
           ->  Parallel Seq Scan on tbl_db_hashcode_20180827 hashcode_1  (cost=0.00..79638.77 rows=757845 width=141) (actual time=0.027..748.550 rows=606390 loops=3)
                 Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
           ->  Parallel Seq Scan on tbl_db_hashcode_20180828 hashcode_2  (cost=0.00..1.00 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=3)
                 Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
     ->  Hash  (cost=531531.87..531531.87 rows=10 width=97) (actual time=24240.695..24240.695 rows=10 loops=3)
           Buckets: 1024  Batches: 1  Memory Usage: 10kB
           ->  Subquery Scan on a  (cost=531531.75..531531.87 rows=10 width=97) (actual time=24240.682..24240.688 rows=10 loops=3)
                 ->  Limit  (cost=531531.75..531531.77 rows=10 width=129) (actual time=24240.680..24240.682 rows=10 loops=3)
                       ->  Sort  (cost=531531.75..531631.75 rows=40000 width=129) (actual time=24240.678..24240.679 rows=10 loops=3)
                             Sort Key: ((sum(tbl_db_hashcode_20180826.request_duration) / (sum(tbl_db_hashcode_20180826.count_number))::numeric)) DESC
                             Sort Method: top-N heapsort  Memory: 27kB
                             ->  GroupAggregate  (cost=484443.38..530667.36 rows=40000 width=129) (actual time=12283.461..24235.974 rows=3443 loops=3)
                                   Group Key: tbl_db_hashcode_20180826.hash_code, tbl_db_hashcode_20180826.buildid
                                   ->  Sort  (cost=484443.38..489468.26 rows=2009955 width=66) (actual time=12283.357..13829.061 rows=2010252 loops=3)
                                         Sort Key: tbl_db_hashcode_20180826.hash_code, tbl_db_hashcode_20180826.buildid
                                         Sort Method: external merge  Disk: 167000kB
                                         ->  Append  (cost=0.00..109128.83 rows=2009955 width=66) (actual time=0.017..5353.023 rows=2010252 loops=3)
                                               ->  Seq Scan on tbl_db_hashcode_20180826  (cost=0.00..10918.38 rows=191127 width=66) (actual time=0.017..515.033 rows=191083 loops=3)
                                                     Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
                                                     Rows Removed by Filter: 11167
                                               ->  Seq Scan on tbl_db_hashcode_20180827  (cost=0.00..98209.46 rows=1818827 width=66) (actual time=0.037..4076.622 rows=1819169 loops=3)
                                                     Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
                                               ->  Seq Scan on tbl_db_hashcode_20180828  (cost=0.00..1.00 rows=1 width=112) (actual time=0.105..0.105 rows=0 loops=3)
                                                     Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
  Planning time: 4.971 ms
  Execution time: 25694.155 ms
  (35 rows)

Total time handle this query is 25694.155ms

The hash_code column is the int 64bit hash of request_name.My problem is i have to group buildid and hash_code and it take 12s / 29s . After that I have to sort Average total request duration / the number called , it take 15s / 29s.

I tried to create index for both buildid and hash_code :

CREATE INDEX tbl_db_hash_code_20180826_code_buildid_idex ON tbl_db_hashcode_20180826(hash_code,buildid);
CREATE INDEX tbl_db_hash_code_20180827_code_buildid_idex ON tbl_db_hashcode_20180827(hash_code,buildid);
CREATE INDEX tbl_db_hash_code_20180828_code_buildid_idex ON tbl_db_hashcode_20180828(hash_code,buildid);

but it even made query slower ( 36821.304ms ):

Nested Loop  (cost=475582.93..518822.76 rows=100498 width=222) (actual time=36684.262..36820.406 rows=6977 loops=1)
   ->  Limit  (cost=475573.26..475573.29 rows=10 width=129) (actual time=36684.158..36684.167 rows=10 loops=1)
     ->  Sort  (cost=475573.26..475673.26 rows=40000 width=129) (actual time=36684.156..36684.160 rows=10 loops=1)
           Sort Key: ((sum(tbl_db_hashcode_20180826.request_duration) / (sum(tbl_db_hashcode_20180826.count_number))::numeric)) DESC
           Sort Method: top-N heapsort  Memory: 27kB
           ->  GroupAggregate  (cost=1.12..474708.87 rows=40000 width=129) (actual time=1.251..36676.934 rows=3443 loops=1)
                 Group Key: tbl_db_hashcode_20180826.hash_code, tbl_db_hashcode_20180826.buildid
                 ->  Merge Append  (cost=1.12..433509.77 rows=2009955 width=66) (actual time=0.844..26010.678 rows=2010252 loops=1)
                       Sort Key: tbl_db_hashcode_20180826.hash_code, tbl_db_hashcode_20180826.buildid
                       ->  Index Scan using tbl_db_hash_code_20180826_code_buildid_idex on tbl_db_hashcode_20180826  (cost=0.42..41283.13 rows=191127 width=66) (actual time=0.369..2426.700 rows=191083 loops=1)
                             Index Cond: (buildid = '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text)
                             Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
                             Rows Removed by Filter: 11167
                       ->  Index Scan using tbl_db_hash_code_20180827_code_buildid_idex on tbl_db_hashcode_20180827  (cost=0.55..371265.07 rows=1818827 width=66) (actual time=0.466..22373.155 rows=1819169 loops=1)
                             Index Cond: (buildid = '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text)
                             Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
                       ->  Index Scan using tbl_db_hash_code_20180828_code_buildid_idex on tbl_db_hashcode_20180828  (cost=0.12..8.15 rows=1 width=112) (actual time=0.005..0.005 rows=0 loops=1)
                             Index Cond: (buildid = '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text)
                             Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
   ->  Append  (cost=9.67..4312.35 rows=1259 width=141) (actual time=0.077..13.431 rows=698 loops=10)
     ->  Bitmap Heap Scan on tbl_db_hashcode_20180826 hashcode  (cost=9.67..422.16 rows=115 width=141) (actual time=0.062..0.657 rows=70 loops=10)
           Recheck Cond: (hash_code = tbl_db_hashcode_20180826.hash_code)
           Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
           Rows Removed by Filter: 6
           Heap Blocks: exact=766
           ->  Bitmap Index Scan on tbl_db_hash_code_20180826_code_buildid_idex  (cost=0.00..9.64 rows=122 width=0) (actual time=0.041..0.041 rows=77 loops=10)
                 Index Cond: ((hash_code = tbl_db_hashcode_20180826.hash_code) AND (buildid = '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
     ->  Bitmap Heap Scan on tbl_db_hashcode_20180827 hashcode_1  (cost=52.27..3889.24 rows=1143 width=141) (actual time=0.363..10.198 rows=627 loops=10)
           Recheck Cond: (hash_code = tbl_db_hashcode_20180826.hash_code)
           Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
           Heap Blocks: exact=6261
           ->  Bitmap Index Scan on tbl_db_hash_code_20180827_code_buildid_idex  (cost=0.00..51.98 rows=1143 width=0) (actual time=0.203..0.203 rows=627 loops=10)
                 Index Cond: ((hash_code = tbl_db_hashcode_20180826.hash_code) AND (buildid = '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
     ->  Index Scan using tbl_db_hash_code_20180828_code_buildid_idex on tbl_db_hashcode_20180828 hashcode_2  (cost=0.12..0.95 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=10)
           Index Cond: ((hash_code = tbl_db_hashcode_20180826.hash_code) AND (buildid = '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
           Filter: ((inserttime >= '2018-08-26 08:58:08.813+07'::timestamp with time zone) AND (inserttime < '2018-08-28 08:58:08.814+07'::timestamp with time zone) AND (buildid ~~ '260c9262-7aaf-4de5-97ae-b7bfdc0761bd'::text))
  Planning time: 4.230 ms
  Execution time: 36821.304 ms
  (38 rows)

I dont have any idea to optimize my query , if you have any suggest please tell me know. Thank you.

-------- Add more information ------

Environment : Intel Core i5 3570 * 4 , RAM 16gb , DISK 256 SSD , UBUNTU 16.04 PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit

Data : I have 6500 different hash_code ( same with request_name ).

  • One obvious bottleneck is the sorting that is done on disk (Sort Method: external merge Disk: 167000kB). You can try to increase work_mem until that goes away, e.g. set work_mem='250MB' before running the query – a_horse_with_no_name Aug 28 '18 at 10:05
  • I tried to increase work_mem by command : SET work_mem = '10000MB'; but it's not affect , still need to 25s to execution the query. – lethanh Aug 28 '18 at 10:12
  • And Sort Method changed from external merge disk to quicksort – lethanh Aug 28 '18 at 10:30
  • Everything about this seems slower than it should be. The scan, the sort, the group aggregate. Are you running this on a AWS nano instance or something? Can you explain the hardware, and monitor it with a tool like "top"? – jjanes Aug 29 '18 at 13:48
  • Is this 7 million rows per partition, or in total? – jjanes Aug 29 '18 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.