Hello everybody,
I have trouble with my table that has four columns which their data types are text, JSON, boolean and timestamp.
Also, I have 1K rows, but my JSON column size approximately 110KB and maybe over it.
When I select all the data from my table, it takes 600 seconds.
But I explain my query;
|
Seq Scan on zamazin (cost=0.00..21.77 rows=1077 width=49) (actual time=0.004..0.112 rows=1077 loops=1) Planning time: 0.013 ms Execution time: 0.194 ms |
When I investigated why these execution times are so different, I find a new storage logic like TOAST.
I overlook some details on TOAST logic and increased some config like shared_buffers, work_mem, maintenance_work_mem, max_file_per_process.
But there was no performance improvement on my query.
I do not understand why it happens. My table size is 168 MB, but my TOAST table size that is related to that table, is 123 MB.
My environment is;
|
PostgreSQL 9.4.1 |
|
Windows Server 2012 R2 |
|
16 GB RAM |
|
100 GB HardDisk (Not SSD) |
|
My database size 20 GB. |
My server configuration ;
|
Shared_buffers: 8GB
( If I understand correctly, PostgreSQL says, For 9.4 The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB. Link: https://www.postgresql.org/docs/9.4/runtime-config-resource.html )
|
|
work_mem : 512 MB |
|
maintenance_work_mem: 1GB |
|
max_file_per_process: 10000 |
|
effective_cache_size: 8GB |
How I can achieve good performance?
Regards,
Mustafa BÜYÜKSOY
