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
