Re: Updating a large table
Hello Tomas! Thank you for the useful answer! 23.12.2017, 23:58, "Tomas Vondra" : > On 12/22/2017 05:46 PM, Timokhin Maxim wrote: >> Hello! We have a large table 11GB ( about 37 million records ) and we >> need to alter a table - add a new column with default values is >> false. Also 'NOT NULL' is required. >> >> So, first I've done: >> >> ALTER TABLE clusters ALTER COLUMN "is_paid"; > > That seems somewhat incomplete ... what exactly did the ALTER do? I'll try to explain what exactly I meant. ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN NOT NULL DEFAULT FALSE; What exactly I need. But that query would lock the whole table for about 40 minutes. I decided to separate it like: 1. ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT FALSE; 2. UPDATE clusters SET is_paid = DEFAULT where ctime <= now() - interval '720h' AND is_paid != FALSE; ( This was needed as soon as possible ) 3. UPDATE another part by chunks 4. set NOT NULL for the table. I was thinking about how to optimize the 3th step. Well, my solution was to write a script which runs two threads. The first one UPDATE "is_paid" by chunks, another one checks my metrics. If something is becoming wrong first thread stops until metrics are good. Thank you, Tomas. > >> after that: >> >> UPDATE clusters SET is_paid = DEFAULT where ctime <= now() - interval >> '720h' AND is_paid != FALSE; >> >> Everything went ok. Then I tried to run it again for an interval of 1 >> years. And I got that no one can't see - the was no available space >> on a disk. The reason was WAL-files ate everything. >> Master-server couldn't send some WAL-file to their replicas. Bandwidth >> wasn't enough. > > Well, then perhaps the best solution is to add more disk space and/or > make sure the network bandwidth is sufficient? > > In any case, don't forget this may also need to update all indexes on > the table, because the new row versions will end up on different pages. > So while the table has 11GB, this update may need much more WAL space > than that. > Got it, thank you! >> Well, I'm searching for a better idea to update the table. >> Solutions I found. >> 1. Separate my UPDATE by chunks. > > If this is a one-time change, this is probably the best option. > Exactly, thank you! >> 2. Alter a table using a new temporary table, but it's not convenient >> for me because there is a lot of foreign keys and indexes. > > Right. > >> 3. Hot-update. This is the most interesting case for me. >> Speaking of HOT-update >> https://www.dbrnd.com/2016/03/postgresql-the-awesome-table-fillfactor-to-speedup-update-and-select-statement/ >> The article says: it might be useful for tables that change often and >> moreover It would be the best way to increase the speed of UPDATE. > > First of all, to make HOT possible there would have to be enough free > space on the pages. As you need to update the whole table, that means > each table would have to be only 50% full. That's unlikely to be true, > and you can't fix that at this point. > >> So, my questions are will it work for all tuples? It says that - no >> https://www.dbrnd.com/2016/03/postgresql-alter-table-to-change- >> fillfactor-value/, but I could not find a confirmation in official >> postresql's documentation. > > Not sure I understand your question, but HOT can only happen when two > conditions are met: > > 1) the update does not change any indexed column > > This is likely met, assuming you don't have an index on is_paid. > > 2) there's enough space on the same page for the new row version > > This is unlikely to be true, because the default fillfactor for tables > is 90%. You may change fillfactor using ALTER TABLE, but that only > applies to new data. > > Moreover, as the article says - this is useful for tables that change > often. Which is not quite what one-time table rewrite does. > > So HOT is not the solution you're looking for. > >> Why do I need to launch vacuum after updating? > > You don't need to launch vacuum - autovacuum will take care of that > eventually. But you may do that, to do the cleanup when it's convenient > for you. > >> How should I reduce the better fillfactor? > > For example to change fillfactor to 75% (i.e. 25% free space): > > ALTER TABLE t SET (fillfactor = 75); > > But as I said, it's not a solution for you. > >> What will be with WAL-files it this case? > > Not sure what you mean. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Updating a large table
Hello > 1. ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT FALSE; this is wrong. To avoid large table lock you need DEFAULT NULL: ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT NULL; Default null changes only system catalog, default with any non-null value will rewrite all rows. After adding column you can set default value - it applied only for future inserts: ALTER TABLE clusters ALTER COLUMN "is_paid" SET DEFAULT FALSE; And then you can update all old rows in table by small chunks. Finally, when here is no NULL values you can set not null: ALTER TABLE clusters ALTER COLUMN "is_paid" SET NOT NULL; But unfortunately this locks table for some time - smaller what rewrite time, but time of full seqscan. I hope my patch [1] will be merged and not null can be set in future by temporary adding check constraint (not valid, then validate) - which not require large table lock [1] https://www.postgresql.org/message-id/flat/[email protected]#[email protected] Regards, Sergei
Need Help on wal_compression
Hi Team, Daily 4000 Archive files are generating and these are occupying more space, we are trying to compress wall files with using wal_compression parameter, but we are not seeing any change in wal files count, could you please help us on this.
Re: Updating a large table
Hello, Sergey! 09.01.2018, 15:53, "Sergei Kornilov" : > Hello > >> 1. ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT FALSE; > > this is wrong. To avoid large table lock you need DEFAULT NULL: > ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT NULL; > Default null changes only system catalog, default with any non-null value > will rewrite all rows. After adding column you can set default value - it > applied only for future inserts: > ALTER TABLE clusters ALTER COLUMN "is_paid" SET DEFAULT FALSE; > > And then you can update all old rows in table by small chunks. Finally, when > here is no NULL values you can set not null: What you wrote are exactly I'm doing. Moreover, I'm checking current metrics to avoid previously problems. > ALTER TABLE clusters ALTER COLUMN "is_paid" SET NOT NULL; > But unfortunately this locks table for some time - smaller what rewrite time, > but time of full seqscan. I hope my patch [1] will be merged and not null can > be set in future by temporary adding check constraint (not valid, then > validate) - which not require large table lock Hope your commit will be merged. It will be realy useful. > > [1] > https://www.postgresql.org/message-id/flat/[email protected]#[email protected] > > Regards, Sergei -- Timokhin 'maf' Maxim
Re: Need Help on wal_compression
On Tue, Jan 9, 2018 at 3:53 AM, Rambabu V wrote:
> Hi Team,
>
> Daily 4000 Archive files are generating and these are occupying more
> space, we are trying to compress wall files with using wal_compression
> parameter, but we are not seeing any change in wal files count, could you
> please help us on this.
>
That's very little information to go on.
You'll probably want to inspect WAL record stats before and after enabling
wal_compression to see whether it makes sense to do so. Take a look at
pg_xlogdump --stats
For example:
$ pg_xlogdump --stats -p /path/to/pg_xlog 00010002C36400F0
00010002C36400FA
Type N (%) Record
size (%) FPI size (%)Combined size (%)
- ---
--- --- ---
- ---
XLOG 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Transaction 11 ( 0.00)
352 ( 0.00)0 ( 0.00) 352 ( 0.00)
Storage0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
CLOG 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Database 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Tablespace 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
MultiXact 4 ( 0.00)
208 ( 0.00)0 ( 0.00) 208 ( 0.00)
RelMap 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Standby2 ( 0.00)
116 ( 0.00)0 ( 0.00) 116 ( 0.00)
Heap2 2504 ( 0.18)
78468 ( 0.20) 1385576 ( 3.55) 1464044 ( 1.89)
Heap 667619 ( 48.23)
19432159 ( 50.47) 28641357 ( 73.35) 48073516 (
61.99)
Btree 712093 ( 51.45)
18643846 ( 48.42) 9021270 ( 23.10) 27665116 (
35.67)
Hash 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Gin0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Gist 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Sequence1918 ( 0.14)
349076 ( 0.91)0 ( 0.00) 349076 ( 0.45)
SPGist 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
BRIN 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
CommitTs 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
ReplicationOrigin 0 (
0.00)0 ( 0.00)0 (
0.00)0 ( 0.00)
Total1384151
38504225 [49.65%] 39048203 [50.35%] 77552428 [100%]
That shows 50% of that are full page writes. This is with compression
enabled. WAL compression will only help FPW, so if you don't have a large
volume of FPW, or they don't compress well, you won't benefit much.
Re: Batch insert heavily affecting query performance.
On Wed, Dec 27, 2017 at 2:10 PM, Mike Sofen wrote: > In my experience, that 77ms will stay quite constant even if your db grew > to > 1TB. Postgres IS amazing. BTW, for a db, you should always have > provisioned IOPS or else your performance can vary wildly, since the SSDs > are shared. > > > > Re Lambda: another team is working on a new web app using Lambda calls > and they were also experiencing horrific performance, just like yours (2 > seconds per call). They discovered it was the Lambda connection/spin-up > time causing the problem. They solved it by keeping several Lambda’s > “hot”, for an instant connection…solved the problem, the last I heard. > Google for that topic, you’ll find solutions. > You should try to implement an internal connection pool in your lambda. Lambda functions are reused. You have no guarantees as to how long these processes will live, but they will live for more than one request. So if you keep a persistent connection in your lambda code, the first invocation may be slow, but further invocations will be fast. Lambda will try to batch several calls at once. In fact, you can usually configure batching in the event source to try to maximize this effect. In my experience, your lambda will be most probably network-bound. Increase the lambda's memory allocation, to get a bigger chunk of the available network bandwidth (why they decided to call that "memory" nobody will ever be able to tell).
Re: Need Help on wal_compression
On Mon, Jan 8, 2018 at 11:53 PM, Rambabu V wrote: > Hi Team, > > Daily 4000 Archive files are generating and these are occupying more space, > we are trying to compress wall files with using wal_compression parameter, > but we are not seeing any change in wal files count, could you please help > us on this. Compression won't change the number of wal files, it will just make the ones created smaller. -- To understand recursion, one must first understand recursion.
Performance of a Query
Hello Gurus, I am struggling to tune a query which is doing join on top of aggregate for around 3 million rows. The plan and SQL is attached to the email. Below is system Details: PGSQL version - 10.1 OS - RHEL 3.10.0-693.5.2.el7.x86_64 Binary - Dowloaded from postgres.org compiled and installed. Hardware - Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem. Please let me know if you need more information. Regards, Virendra This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you. Aggregate (cost=4794108.63..4794108.64 rows=1 width=232) (actual time=54451.640..54451.641 rows=1 loops=1) Buffers: shared hit=3771629 read=46189, temp read=20532 written=20551 -> Merge Join (cost=491054.79..2272373.76 rows=34782550 width=200) (actual time=3278.909..49768.897 rows=3704652 loops=1) Merge Cond: ((se.peril_id = pe.peril_id) AND (se.account_id = pe.account_id)) Buffers: shared hit=3771629 read=46189, temp read=20532 written=20551 -> Finalize GroupAggregate (cost=491054.35..953772.46 rows=407064 width=152) (actual time=3278.847..17842.707 rows=3704652 loops=1) Group Key: se.peril_id, se.portfolio_id, se.account_id Buffers: shared hit=66 read=46189, temp read=20532 written=20551 -> Gather Merge (cost=491054.35..827582.62 rows=2442384 width=152) (actual time=3278.833..10355.885 rows=3731100 loops=1) Workers Planned: 6 Workers Launched: 6 Buffers: shared hit=66 read=46189, temp read=20532 written=20551 -> Partial GroupAggregate (cost=490054.25..529743.04 rows=407064 width=152) (actual time=3264.819..4961.425 rows=533014 loops=7) Group Key: se.peril_id, se.portfolio_id, se.account_id Buffers: shared hit=676 read=306596, temp read=135840 written=135972 -> Sort (cost=490054.25..491750.35 rows=678441 width=148) (actual time=3264.796..3731.927 rows=581538 loops=7) Sort Key: se.peril_id, se.account_id Sort Method: external merge Disk: 97240kB Buffers: shared hit=676 read=306596, temp read=135840 written=135972 -> Result (cost=0.00..322308.92 rows=678441 width=148) (actual time=0.036..2205.533 rows=581538 loops=7) Buffers: shared hit=448 read=306596 -> Append (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.033..1523.998 rows=581538 loops=7) Buffers: shared hit=448 read=306596 -> Parallel Seq Scan on site_exposure_1192662 se (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.030..896.668 rows=581538 loops=7) Filter: (portfolio_id = '-1192662'::integer) Buffers: shared hit=448 read=306596 -> Materialize (cost=0.44..712345.45 rows=3417895 width=80) (actual time=0.055..24504.782 rows=3704652 loops=1) Buffers: shared hit=3771563 -> GroupAggregate (cost=0.44..669621.76 rows=3417895 width=80) (actual time=0.051..20359.291 rows=3704652 loops=1) Group Key: pe.peril_id, pe.portfolio_id, pe.account_id Buffers: shared hit=3771563 -> Result (cost=0.44..532014.98 rows=3761012 width=80) (actual time=0.040..13908.465 rows=3761146 loops=1) Buffers: shared hit=3771563 -> Merge Append (cost=0.44..494404.86 rows=3761012 width=80) (actual time=0.038..10094.668 rows=3761146 loops=1) Sort Key: pe.peril_id, pe.account_id Buffers: shared hit=3771563 -> Index Scan using policy_exposure_1192662_portfolio_id_peril_id_account_id_idx on policy_exposure_1192662 pe (cost=0.43..466197.26 rows=3761012 width=80) (actual time=0.036..6448.117 rows=3761146 loops=1) Index Cond: (portfolio_id = '-1192662'::integer) Buffers: shared hit=3771563 AggQuery.sql Description: AggQuery.sql
Re: Need Help on wal_compression
On Tue, Jan 9, 2018 at 1:53 AM, Rambabu V wrote: > Hi Team, > > Daily 4000 Archive files are generating and these are occupying more > space, we are trying to compress wall files with using wal_compression > parameter, but we are not seeing any change in wal files count, could you > please help us on this. > If the number of files is driven by archive_timeout, then no reduction in the number of them would be expected by turning on wal_compression. If the number of files is driven by the 16MB limit on each file, then it is surprising that wal_compression did not change it. (But the difference might not be all that large, depending on the type of transactions and data you are working with.) I use an external compression program, xz, which compresses very well. But it is slow and has trouble keeping up at times of peak activity (e.g. bulk loads or updates, or reindexing). It reduces the aggregate size, but not the number of files. Cheers, Jeff
Re: Performance of a Query
On Tue, Jan 9, 2018 at 2:18 PM, Kumar, Virendra wrote: > Hello Gurus, > > I am struggling to tune a query which is doing join on top of aggregate for > around 3 million rows. The plan and SQL is attached to the email. > > Below is system Details: > > PGSQL version – 10.1 > > OS – RHEL 3.10.0-693.5.2.el7.x86_64 > > Binary – Dowloaded from postgres.org compiled and installed. > > Hardware – Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem. I uploaded your query plan here: https://explain.depesz.com/s/14r6 The most expensive part is the merge join at the end. Lines like this one: "Buffers: shared hit=676 read=306596, temp read=135840 written=135972" Tell me that your sorts etc are spilling to disk, so the first thing to try is upping work_mem a bit. Don't go crazy, as it can run your machine out of memory if you do. but doubling or tripling it and seeing the effect on the query performance is a good place to start. The good news is that most of your row estimates are about right, so the query planner is doing what it can to make the query fast, but I'm guessing if you get the work_mem high enough it will switch from a merge join to a hash_join or something more efficient for large numbers of rows.
RE: Performance of a Query
Thank you Scott! I have current work_mem set as 4MB, shared_buffers to 8GB, hugepages on. I gradually increased the work_mem to 1GB but it did not help a bit. Am I missing something obvious. Regards, Virendra -Original Message- From: Scott Marlowe [mailto:[email protected]] Sent: Tuesday, January 09, 2018 5:08 PM To: Kumar, Virendra Cc: [email protected] Subject: Re: Performance of a Query On Tue, Jan 9, 2018 at 2:18 PM, Kumar, Virendra wrote: > Hello Gurus, > > I am struggling to tune a query which is doing join on top of > aggregate for around 3 million rows. The plan and SQL is attached to the > email. > > Below is system Details: > > PGSQL version – 10.1 > > OS – RHEL 3.10.0-693.5.2.el7.x86_64 > > Binary – Dowloaded from postgres.org compiled and installed. > > Hardware – Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem. I uploaded your query plan here: https://explain.depesz.com/s/14r6 The most expensive part is the merge join at the end. Lines like this one: "Buffers: shared hit=676 read=306596, temp read=135840 written=135972" Tell me that your sorts etc are spilling to disk, so the first thing to try is upping work_mem a bit. Don't go crazy, as it can run your machine out of memory if you do. but doubling or tripling it and seeing the effect on the query performance is a good place to start. The good news is that most of your row estimates are about right, so the query planner is doing what it can to make the query fast, but I'm guessing if you get the work_mem high enough it will switch from a merge join to a hash_join or something more efficient for large numbers of rows. This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you.
PG 9.5 2 tables same DDL with diff size
HI List I am trying to understand the following : have 2 identical PG cluster on diff hosts, same postgresql.conf, same db schema : same tale DDL and row counts but different size ( 14GB diff ), I run reindex and full vacuum analyze, but I can not decrease the size of larger table(50GB) to match the size in second PG cluster. any tips what can make this 2 tables to have diff size except the host ( same OS and PG version 9.5.3)? Thank you
Re: Performance of a Query
On Tue, Jan 9, 2018 at 3:25 PM, Kumar, Virendra wrote: > Thank you Scott! > I have current work_mem set as 4MB, shared_buffers to 8GB, hugepages on. > I gradually increased the work_mem to 1GB but it did not help a bit. Am I > missing something obvious. > From: Scott Marlowe [mailto:[email protected]] > Sent: Tuesday, January 09, 2018 5:08 PM > To: Kumar, Virendra > Cc: [email protected] > Subject: Re: Performance of a Query Try it with something reasonable like 64MB and then post your query plans to explain.depesz and then here and let's compare. Note that some queries are just slow, and this one is handling a lot of data, so there's only so much to do if an index won't fix it.
RE: Performance of a Query
It did not seem to help. See attachment. Regards, Virendra -Original Message- From: Scott Marlowe [mailto:[email protected]] Sent: Tuesday, January 09, 2018 6:00 PM To: Kumar, Virendra Cc: [email protected] Subject: Re: Performance of a Query On Tue, Jan 9, 2018 at 3:25 PM, Kumar, Virendra wrote: > Thank you Scott! > I have current work_mem set as 4MB, shared_buffers to 8GB, hugepages on. > I gradually increased the work_mem to 1GB but it did not help a bit. Am I > missing something obvious. > From: Scott Marlowe [mailto:[email protected]] > Sent: Tuesday, January 09, 2018 5:08 PM > To: Kumar, Virendra > Cc: [email protected] > Subject: Re: Performance of a Query Try it with something reasonable like 64MB and then post your query plans to explain.depesz and then here and let's compare. Note that some queries are just slow, and this one is handling a lot of data, so there's only so much to do if an index won't fix it. This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you. ap_poc_db=# show work_mem; work_mem -- 64MB (1 row) Time: 0.285 ms ap_poc_db=# explain (analyze,buffers) ap_poc_db-# SELECT SUM se_cov1val + se_cov2val) + se_cov3val) + se_cov4val)) ap_poc_db-#AS "10", ap_poc_db-#SUM (se_site_limit) ap_poc_db-#AS "11", ap_poc_db-#SUM (se_cov1val) ap_poc_db-#AS "12", ap_poc_db-#SUM (se_cov2val) ap_poc_db-#AS "13", ap_poc_db-#SUM (se_cov3val) ap_poc_db-#AS "14", ap_poc_db-#SUM (se_cov4val) ap_poc_db-#AS "15", ap_poc_db-#SUM (se_site_deduct) ap_poc_db-#AS "17", ap_poc_db-#SUM (se_risk_count) ap_poc_db-#AS "21", ap_poc_db-#SUM (se_cov1limit) ap_poc_db-#AS "143", ap_poc_db-#SUM (se_cov1deduct) ap_poc_db-#AS "144", ap_poc_db-#SUM (se_cov2limit) ap_poc_db-#AS "145", ap_poc_db-#SUM (se_cov3limit) ap_poc_db-#AS "147", ap_poc_db-#SUM (se_cov3deduct) ap_poc_db-#AS "148", ap_poc_db-#SUM (se_cov4limit) ap_poc_db-#AS "149", ap_poc_db-#SUM (se_cov4deduct) ap_poc_db-#AS "150", ap_poc_db-#SUM (se_site_bl_deduct) ap_poc_db-#AS "212", ap_poc_db-#SUM (se_agg_limit) ap_poc_db-#AS "213", ap_poc_db-#SUM (se_site_bl_limit) ap_poc_db-#AS "211", ap_poc_db-#SUM (pe_premium) ap_poc_db-#AS "93", ap_poc_db-#SUM (pe_policy_deduct) ap_poc_db-#AS "92", ap_poc_db-#SUM (pe_undercover) ap_poc_db-#AS "127", ap_poc_db-#SUM (pe_prorata) ap_poc_db-#AS "126", ap_poc_db-#SUM (pe_policy_bl_deduct) ap_poc_db-#AS "139", ap_poc_db-#SUM (pe_policy_bl_grosslimit) ap_poc_db-#AS "142", ap_poc_db-#SUM (pe_policy_limit) ap_poc_db-#AS "128", ap_poc_db-#SUM (pe_agg_deduct) ap_poc_db-#AS "155" ap_poc_db-# FROM (SELECT SUM (se.site_limit) AS se_site_limit, ap_poc_db(#SUM (se.cov1val)AS se_cov1val, ap_poc_db(#SUM (se.cov2val)AS se_cov2val, ap_poc_db(#SUM (se.cov3val)AS se_cov3val, ap_poc_db(#SUM (se.cov4val)AS se_cov4val, ap_poc_db(# SUM (se.site_deduct)AS se_site_deduct, ap_poc_db(#SUM (se.risk_count) AS se_risk_count, ap_poc_db(#SUM (se.cov1limit) AS se_cov1limit, ap_poc_db(#SUM (se.cov1deduct) AS se_cov1deduct, ap_poc_db(#SUM (se.cov2limit) AS se_cov2limit, ap_poc_db(#SUM (se.cov3limit) AS se_cov3limit, ap_poc_db(#SUM (se.cov3deduct) AS se_cov3deduct, ap_poc_db(#SUM (se.cov4limit) AS se_cov4limit, ap_poc_db(#SUM (se.cov4deduct) AS se_cov4deduct, ap_poc_db(#SUM (se.site_bl_deduct) AS se_site_bl_deduct, ap_poc_db(#SUM (se.agg_limit) AS se_agg_limit, ap_poc_db(#SUM (se.site_bl_limit) AS se_site_bl_limit, ap_poc_db(#se.peril_id AS se_peril_id, ap_poc_db(#se.portfolio_id AS se_portfolio_id, ap_poc_db(#se.account_id AS se_account_id ap_poc_db(# FROM ap.site_exposure se ap_poc_db(# WHERE se.portfolio_id = -1192662 ap_poc_db(# GROUP BY se.peril_id,se.portfolio_id,se.account_i
Re: Performance of a Query
On Tue, Jan 9, 2018 at 4:09 PM, Kumar, Virendra wrote: > It did not seem to help. > See attachment. Yeah while it's still writing, it's about half as much but most of the time seems to be in merging etc multiple data sets. I'm wondering what non-default values you might have set otherwise. Are you running on SSDs? If so lowering random_page_cost might help, but again, this might just be a very expensive query as well.
Re: Need Help on wal_compression
On Tue, Jan 09, 2018 at 01:53:14PM -0300, Claudio Freire wrote: > That shows 50% of that are full page writes. This is with compression > enabled. WAL compression will only help FPW, so if you don't have a large > volume of FPW, or they don't compress well, you won't benefit much. This highly depends on the data types used as well. You won't get much compressibility with things like UUIDs for example. When we worked on the patch, I recall that FDW compression saved 25% for a relation with a one-column integer, and only 12~15% when using UUIDs. -- Michael signature.asc Description: PGP signature
RE: Performance of a Query
Can you try to extract filter part as CTE? Like with filtered as (select ... where policyid = 123456) select ... (here comes original query but uses filtered table instead) 10 янв. 2018 г. 1:10 пользователь "Kumar, Virendra" < [email protected]> написал: It did not seem to help. See attachment. Regards, Virendra -Original Message- From: Scott Marlowe [mailto:[email protected]] Sent: Tuesday, January 09, 2018 6:00 PM To: Kumar, Virendra Cc: [email protected] Subject: Re: Performance of a Query On Tue, Jan 9, 2018 at 3:25 PM, Kumar, Virendra wrote: > Thank you Scott! > I have current work_mem set as 4MB, shared_buffers to 8GB, hugepages on. > I gradually increased the work_mem to 1GB but it did not help a bit. Am I missing something obvious. > From: Scott Marlowe [mailto:[email protected]] > Sent: Tuesday, January 09, 2018 5:08 PM > To: Kumar, Virendra > Cc: [email protected] > Subject: Re: Performance of a Query Try it with something reasonable like 64MB and then post your query plans to explain.depesz and then here and let's compare. Note that some queries are just slow, and this one is handling a lot of data, so there's only so much to do if an index won't fix it. This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you.
