Re: [PERFORM] How to reduce IOWAIT and CPU idle time?

uum may not now be enough to recover from that and you would need a vacuum full. In general, it's not a good idea to turn autovacuum off. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [PERFORM] The same query - much different runtimes

the shared buffers or the OS cache. That could drastically improve the performance. I can see a large number of shared buffer hits in the explain analyze output of the query ran through psql session. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee

Re: [PERFORM] Fillfactor in postgresql 9.2

stop HOT's ability to aggressively clean up dead space and stop the bloat. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee

Re: [PERFORM] pgbench intriguing results: better tps figures for larger scale factor

will still get the same or similar tps. As the scale factor is increased, the contention on the smaller tables reduces and you will start seeing an increase in the tps as you increase the number of clients. Of course, beyond a point either it will flatten out or even go down. While

Re: [PERFORM] Slow Query Help

ry on the same machine as the > server? If not, what's the ping time between them? > I don't think the network latency can cause that. The "Total runtime" is calculated on the server side itself - see ExplainOnePlan(). Thanks, Pavan -- Pavan Deolasee http://www.linke

Re: [PERFORM] Slow Query Help

hould take so long. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] postgresql.conf recommendations

On Wed, Feb 6, 2013 at 3:32 AM, Johnny Tan wrote: > > maintenance_work_mem = 24GB # min 1MB I'm quite astonished by this setting. Not that it explains the problem at hand, but I wonder if this is a plain mistake in configuration. Thanks, Pavan -- Pavan Deolasee http://www.link

Re: [PERFORM] Limit & offset effect on query plans

AFAICS the OFFSET 0 and LIMIT ALL cases are optimized to a good extent. So the overhead of having them will not be significant. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make ch

Re: [PERFORM] 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL

On Thu, Nov 29, 2012 at 6:06 PM, Andres Freund wrote: > On 2012-11-29 17:59:39 +0530, Pavan Deolasee wrote: > > > > > > > Yeah, that looks fairly easy to have. Thinking about it more, now that we > > have ability to skip WAL for the case when a table is created a

Re: [PERFORM] 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL

On Thu, Nov 29, 2012 at 5:42 PM, Andres Freund wrote: > On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote: > > > Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits > > set to visible, thats an entirely different question. I don't think it > c

Re: [PERFORM] 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL

On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau wrote: > Hello, > > I am toying around with 9.2.1, trying to measure/determine how > index-only scans can improve our performance. > > A small script which is attached to this mail, shows that as long > as the table has been VACUUM FULL'd, the

Re: [PERFORM] Execution from java - slow

On Mon, Aug 27, 2012 at 6:07 PM, Jayadevan M wrote: > Hello all, > > I have a plpgsql function that takes a few seconds (less than 5) when > executed from psql. The same function, when invoked from java via a > prepared statement takes a few minutes. There are a few queries in the > function. Out

Re: [PERFORM] Vacuum problems with 9.1

On Tue, Aug 28, 2012 at 10:03 AM, Nimesh Satam wrote: > Hi, > > We have been using the current version of postgres i.e. 9.1.4 with > streaming replication on. While vacuuming we noticed that certain dead rows > are not getting removed and following debug information is printed: > > "DETAIL: 12560

Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database

hmarking tests while HOT development, but the tuning mechanism still may not be perfect for all kinds of work loads and it would probably never be. Thanks, Pavan -- Pavan Deolasee EnterpriseDB     http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] caching written values?

> different pages needs to be retrieved. Is this approximately correct? > Yes. That's how it works. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to

[PERFORM] Re: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow

2008/04/postgresql-in-place-update.html Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] I/O on select count(*)

f the same page. I guess the idea got rejected because of lack of benchmarks to prove the benefit. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: htt

Re: [PERFORM] Replication Syatem

On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > > That's weird. With that fillfactor, you should have a very high > > percentage of HOT update ratio. It could be a very special case t

Re: [PERFORM] Replication Syatem

's no help for HOT. You need to recreate the TABLEs with a fill factor. And as Heikki pointed out, you need to dump and reload, just altering the table won't affect the current data. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-per

Re: [PERFORM] Replication Syatem

I haven't been able to reproduce this at my end. With the given indexes and kind of updates, I get very high percentage of HOT updates. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.

Re: [PERFORM] Replication Syatem

tes until you fill up the free space should be HOT. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Replication Syatem

relname = 'table1'; Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Replication Syatem

you are anyways have a update intensive setup, leaving free space in the heap won't harm you much in the long term. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to you

Re: [PERFORM] Replication Syatem

rows, the dead space created in the first step is the blocks which are not touched in the subsequent updates. Is this a real scenario or are you just testing ? If its just for testing, I would suggest updating different sets of rows in each step and then check. Thanks, Pavan -- Pavan Deola

Re: [PERFORM] Replication Syatem

first UPDATE be an HOT update. Subsequent UPDATEs in the page may reuse the dead row created by earlier UPDATEs. 3. Avoid any long running transactions. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql

Re: [PERFORM] Replication Syatem

are not gaining much by regaining space. The subsequent UPDATEs/INSERTs will quickly extend the relation and you loose all the work done by VACUUM FULL. Plain VACUUM will update FSM to track the free space scattered across the relation which is later reused by updates/inserts. Thanks, Pavan -- Pav

Re: [PERFORM] Replication Syatem

g updates/inserts will fill in the fragmented free space. You may want to check your FSM settings as well to make sure that you are tracking free space properly. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@p

Re: [PERFORM] Exact index overhead

d suggest you to upgrade to 8.3 (if you are not using it already) and then you can create the index without bothering much about overheads. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)

Re: [PERFORM] EXPLAIN detail

are selecting all rows from the table and that seq scan is the right thing for that. Without knowing your hardware its difficult to judge if the time taken is more or not. Anyways, I don't think there is much tweaking you can do for such a query except making sure that your table is not bloated

Re: [PERFORM] increasing shared buffer slow downs query performance.

second time it runs fast. May be you want to run the query few times in both the settings and then compare. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

a linked list, and binary search. Assuming that in most of the cases, there will be many committed and few aborted subtransactions, how about storing the list of *aborted* subtransactions ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-perfor

Re: [PERFORM] 7 hrs for a pg_restore?

does the seq scan). Other option is to make pg_restore multi-threaded/processed. The synchronized_scans facility would then synchronize the multiple heap scans. ISTM that if we can make pg_restore mult-processed, then we can possibly add more parallelism to the restore process. My two cents. T

Re: [PERFORM] select count(*) performance (vacuum did not help)

pages are entirely empty. > CPU 5.53s/1.71u sec elapsed 227.35 sec. > INFO: analyzing "public.main_activity" > INFO: "main_activity": 160888 pages, 4500 rows sampled, 4594 estimated > total rows > > Looking at the number of rows vs number of pages, ISTM that V