TOAST table performance problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux