I have a table like this which stores file operation logs on a computer:
CREATE TABLE tbl_logs_file_ops(
log_id bigserial primary key,
username VARCHAR(255),
computer VARCHAR(255),
activity VARCHAR(255),
is_directory bool,
event_type VARCHAR(255),
src text,
dst text,
event_time timestamp(6));
And I run the following query on it to retrieve the names of the files that are the top 10 most used:
SELECT src, COUNT(src)
FROM tbl_logs_file_ops
GROUP BY src
ORDER BY COUNT(src) DESC
NULLS LAST
LIMIT 10
The table has ~3 million rows and it currently takes about 40 seconds to finish this query. But I need it to be much faster on a much larger dataset.
I've learned about indexing and added the following index to the table:
CREATE INDEX top_file_desc_index ON tbl_logs_file_ops (src DESC NULLS LAST);
But that has not improved the query speed any bit. What am I missing? How can I improve the speed of such queries?
Output of explain(analyze, verbose, buffers):
"QUERY PLAN"
"Limit (cost=10000876798.05..10000876798.08 rows=10 width=139) (actual time=125352.270..125352.274 rows=10 loops=1)"
" Output: src, (count(src))"
" Buffers: shared hit=2974 read=79483 dirtied=597, temp read=156882 written=157095"
" -> Sort (cost=10000876798.05..10000877028.85 rows=92318 width=139) (actual time=125097.633..125097.635 rows=10 loops=1)"
" Output: src, (count(src))"
" Sort Key: (count(tbl_logs_file_ops.src)) DESC NULLS LAST"
" Sort Method: top-N heapsort Memory: 26kB"
" Buffers: shared hit=2974 read=79483 dirtied=597, temp read=156882 written=157095"
" -> GroupAggregate (cost=10000851212.26..10000874803.09 rows=92318 width=139) (actual time=113240.333..124896.852 rows=903094 loops=1)"
" Output: src, count(src)"
" Group Key: tbl_logs_file_ops.src"
" Buffers: shared hit=2974 read=79483 dirtied=597, temp read=156882 written=157095"
" -> Sort (cost=10000851212.26..10000858768.14 rows=3022354 width=131) (actual time=113240.278..124149.192 rows=3018673 loops=1)"
" Output: src"
" Sort Key: tbl_logs_file_ops.src"
" Sort Method: external merge Disk: 422672kB"
" Buffers: shared hit=2974 read=79483 dirtied=597, temp read=156882 written=157095"
" -> Seq Scan on public.tbl_logs_file_ops (cost=10000000000.00..10000112680.54 rows=3022354 width=131) (actual time=0.024..795.934 rows=3018673 loops=1)"
" Output: src"
" Buffers: shared hit=2974 read=79483 dirtied=597"
"Planning Time: 0.209 ms"
"JIT:"
" Functions: 8"
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
" Timing: Generation 2.582 ms, Inlining 14.640 ms, Optimization 169.266 ms, Emission 70.453 ms, Total 256.941 ms"
"Execution Time: 125436.075 ms"
work_mem is 4MB. Below is the output of explain(analyze, verbose, buffers) after setting the work_mem to 2GB:
"QUERY PLAN"
"Limit (cost=10000465717.01..10000465717.03 rows=10 width=141) (actual time=114351.538..114351.547 rows=10 loops=1)"
" Output: src, (count(src))"
" Buffers: shared hit=5143 read=77806"
" -> Sort (cost=10000465717.01..10000465964.26 rows=98901 width=141) (actual time=114133.937..114133.944 rows=10 loops=1)"
" Output: src, (count(src))"
" Sort Key: (count(tbl_logs_file_ops.src)) DESC NULLS LAST"
" Sort Method: top-N heapsort Memory: 26kB"
" Buffers: shared hit=5143 read=77806"
" -> GroupAggregate (cost=10000439841.77..10000463579.79 rows=98901 width=141) (actual time=112704.879..113965.053 rows=911901 loops=1)"
" Output: src, count(src)"
" Group Key: tbl_logs_file_ops.src"
" Buffers: shared hit=5143 read=77806"
" -> Sort (cost=10000439841.77..10000447424.77 rows=3033202 width=133) (actual time=112704.830..113141.553 rows=3036619 loops=1)"
" Output: src"
" Sort Key: tbl_logs_file_ops.src"
" Sort Method: quicksort Memory: 746279kB"
" Buffers: shared hit=5143 read=77806"
" -> Seq Scan on public.tbl_logs_file_ops (cost=10000000000.00..10000113281.02 rows=3033202 width=133) (actual time=0.029..948.635 rows=3036619 loops=1)"
" Output: src"
" Buffers: shared hit=5143 read=77806"
"Planning Time: 0.223 ms"
"JIT:"
" Functions: 8"
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
" Timing: Generation 3.429 ms, Inlining 16.132 ms, Optimization 147.881 ms, Emission 53.287 ms, Total 220.729 ms"
"Execution Time: 114371.484 ms"