Re: [PERFORM] Need Some Suggestions

2005-10-07 Thread Richard Huxton
Lane Van Ingen wrote: I have an application that is prone to sudden, unscheduled high bursts of activity, and I am finding that the application design permits me to detect the activity bursts within an existing function. The bursts only affect 3 tables, but degradation becomes apparent after

[PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread Cestmir Hybl
Hello all First of all, I do understand why pgsql with it's MVCC design has to examine tuples to evaluate "count(*)"and "count(*) where (...)" queries in environment with heavy concurrent updates. This kind of usage IMHO isn't the average one. There are many circumstances with rather

Re: [PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread hubert depesz lubaczewski
On 10/7/05, Cestmir Hybl [EMAIL PROTECTED] wrote: Isn't it possible (and reasonable) for these environments to keep track of whether there is a transaction in progress with update to given table and if not, use an index scan (count(*) where) or cached value (count(*)) to perform this kind of

Re: [PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread Cestmir Hybl
Yes, I can possibly use triggers to maintanin counts of several fixed groups of records or total recordcount (but it's unpractical). No, I can't speed-up evaluation of generic "count(*) where ()" queries this way. My question was rather about general performance of count() queries in

Re: [PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread Steinar H. Gunderson
On Fri, Oct 07, 2005 at 11:24:05AM +0200, Cestmir Hybl wrote: Isn't it possible (and reasonable) for these environments to keep track of whether there is a transaction in progress with update to given table and if not, use an index scan (count(*) where) or cached value (count(*)) to perform

Re: [PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread Cestmir Hybl
collision: it's possible to either block updating transaction until index scan ends or discard index scan imediately and finish query using MVCC compliant scan dead rows: this sounds like more serious counter-argument, I don't know much about dead records management and whether it would be

Re: [PERFORM] Status of Opteron vs Xeon

2005-10-07 Thread Merlin Moncure
What's the current status of how much faster the Opteron is compared to the Xeons? I know the Opterons used to be close to 2x faster, but is that still the case? I understand much work has been done to reduce the contect switching storms on the Xeon architecture, is this correct? Up until

Re: [PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread Alvaro Herrera
On Fri, Oct 07, 2005 at 01:14:20PM +0200, Cestmir Hybl wrote: collision: it's possible to either block updating transaction until index scan ends or discard index scan imediately and finish query using MVCC compliant scan You can't change from one scan method to a different one on the fly.

Re: [PERFORM] Need Some Suggestions

2005-10-07 Thread Lane Van Ingen
You are correct, in that these tables are not large (50,000 records), but their effect on performance is noticeable. Plain VACUUM (no freeze, full, etc) does the trick well, but I am unable to figure a way to call the 'plain vanilla version' of VACUUM via a PostgreSQL trigger function (does not

Re: [PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread Tom Lane
Cestmir Hybl [EMAIL PROTECTED] writes: Isn't it possible (and reasonable) for these environments to keep track = of whether there is a transaction in progress with update to given table = and if not, use an index scan (count(*) where) or cached value = (count(*)) to perform this kind of query?

Re: [PERFORM] count(*) using index scan in query often, update rarely environment

2005-10-07 Thread Merlin Moncure
On 10/7/05, Cestmir Hybl [EMAIL PROTECTED] wrote: Isn't it possible (and reasonable) for these environments to keep track of whether there is a transaction in progress with update to given table and if not, use an index scan (count(*) where) or cached value (count(*)) to perform this kind of

Re: [PERFORM] count(*) using index scan in query often, update rarely

2005-10-07 Thread Richard Huxton
Tom Lane wrote: There's a workable-looking design in the archives (pghackers probably) for maintaining overall table counts in a separate table, with each transaction adding one row of delta information just before it commits. I haven't seen anything else that looks remotely attractive. It

Re: [PERFORM] Status of Opteron vs Xeon

2005-10-07 Thread Chris Browne
[EMAIL PROTECTED] (Jeff Frost) writes: What's the current status of how much faster the Opteron is compared to the Xeons? I know the Opterons used to be close to 2x faster, but is that still the case? I understand much work has been done to reduce the contect switching storms on the Xeon

Re: [PERFORM] Status of Opteron vs Xeon

2005-10-07 Thread Tom Lane
Chris Browne [EMAIL PROTECTED] writes: [EMAIL PROTECTED] (Jeff Frost) writes: What's the current status of how much faster the Opteron is compared to the Xeons? I know the Opterons used to be close to 2x faster, but is that still the case? I understand much work has been done to reduce the

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-07 Thread Luke Lonergan
Steinar, On 10/5/05 5:12 PM, Steinar H. Gunderson [EMAIL PROTECTED] wrote: What? strlen is definitely not in the kernel, and thus won't count as system time. System time on Linux includes time spent in glibc routines. - Luke ---(end of

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-07 Thread mark
On Fri, Oct 07, 2005 at 04:55:28PM -0700, Luke Lonergan wrote: On 10/5/05 5:12 PM, Steinar H. Gunderson [EMAIL PROTECTED] wrote: What? strlen is definitely not in the kernel, and thus won't count as system time. System time on Linux includes time spent in glibc routines. Do you have a

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-07 Thread Luke Lonergan
Mark, On 10/7/05 5:17 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Fri, Oct 07, 2005 at 04:55:28PM -0700, Luke Lonergan wrote: On 10/5/05 5:12 PM, Steinar H. Gunderson [EMAIL PROTECTED] wrote: What? strlen is definitely not in the kernel, and thus won't count as system time. System