From: | Asya Nevra Buyuksoy <ayisan1096(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | TOAST table performance problem |
Date: | 2020-02-07 13:07:28 |
Message-ID: | CADA48rGrp8exg1tjpen313+cDYsgDfoFyEvZxpEN=BOY3THsyg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2020-02-07 13:12:39 | Sv: TOAST table performance problem |
Previous Message | Ogden Brash | 2020-02-06 18:15:23 | Re: Writing 1100 rows per second |