Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread Chris Huston
Thanks Josh that helped. I had gone looking for some kind of cluster option but was looking under create database, create index and initlocation - didn't see the CLUSTER index ON table. I ran the CLUSTER which took about 2 1/2 hours to complete. That improved the query performance about 6x - wh

Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread Dennis Bjorklund
On Wed, 10 Sep 2003, Chris Huston wrote: > Sometime before then I need to figure out how to cut the fetch times > from the now 200ms to something like 10ms. You didn't say anything about Joshs first point of adjusting postgresql.conf to match your machine. Settings like effective_cache_size you

Re: [PERFORM] Need advice about triggers

2003-09-10 Thread Mindaugas Riauba
> >> Well, try it without the trigger. If performance improves markedly, it > >> might be worth rewriting in C. > > > Nope. Execution time is practically the same without trigger. > > >> If not, you're probably saturating the disk I/O - > > > Bottleneck in this case is CPU. postmaster process

Re: [PERFORM] Hardware recommendations to scale to silly load

2003-09-10 Thread Gregory S. Williamson
Nitpicking -- Perhaps the 4th data line is meant to be: Inserts in separate transactions 2500 inserts/second ^^^ ?? Greg Williamson -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tue 9/9/2003 8:25 PM To:

Re: [PERFORM] Hardware recommendations to scale to silly load

2003-09-10 Thread Bruce Momjian
Gregory S. Williamson wrote: > Nitpicking -- > > Perhaps the 4th data line is meant to be: > Inserts in separate transactions 2500 inserts/second >^^^ Oh, yes, sorry. It is: > Sorry to be replying late. Here is what I found. > > fsync o

Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread Josh Berkus
Chris, > The system is currently running on a single processor 500Mhz G4. We're > likely to move to a two processor 2Ghz G5 in the next few months. Then > each block may take only a 30-60 milliseconds to complete and their can > be two concurrent blocks processing at once. What about explaining y

Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread Magnus Naeslund(w)
Chris Huston said: > Thanks Josh that helped. I had gone looking for some kind of cluster > option but was looking under create database, create index and > initlocation - didn't see the CLUSTER index ON table. > > I ran the CLUSTER which took about 2 1/2 hours to complete. That > improved the quer

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic (continued)

2003-09-10 Thread Bruce Momjian
Tom Lane wrote: > Mary Edie Meredith <[EMAIL PROTECTED]> writes: > > Stephan Szabo kindly responded to our earlier queries suggesting we look > > at default_statistics_target and ALTER TABLE ALTER COLUMN SET > > STATISTICS. > > > These determine the number of bins in the histogram for a given co

Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread Chris Huston
On Wednesday, Sep 10, 2003, at 11:16 America/Denver, Josh Berkus wrote: What about explaining your disk setup? Or mentioning postgresql.conf? For somebody who wants help, you're ignoring a lot of advice and questions. Personally, I'm not going to be of any further help until you report back

Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread William Yu
1) Memory - clumsily adjusted shared_buffer - tried three values: 64, 128, 256 with no discernible change in performance. Also adjusted, clumsily, effective_cache_size to 1000, 2000, 4000 - with no discernible change in performance. I looked at the Admin manual and googled around for how to set

Re: [PERFORM] Query too slow

2003-09-10 Thread Bruce Momjian
Bruce Momjian wrote: > Tom Lane wrote: > > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > On Tue, 26 Aug 2003, Ang Chin Han wrote: > > >> Veering aside a bit, since we usually pinpoint performance problems by > > >> looking at EXPLAIN ANALYZE's differences between the planner's > > >> estimation a

Re: [PERFORM] Upgrade Woes

2003-09-10 Thread Christopher Browne
[EMAIL PROTECTED] writes: > P.S. Sorry this is so long, but I wanted to include as much info as possible. Throw in the non-commented lines in postgresql.conf; that would more than likely make numeric answers possible, for some of it. If the config is "out-of-the-box," then it's pretty likely that

Re: [osdldbt-general] Re: [PERFORM] [GENERAL] how to get accurate

2003-09-10 Thread Mary Edie Meredith
We tried 1000 as the default and found that the plans were good plans and were consistent, but the pg_statistics was not exactly the same. We took Tom's' advice and tried SET SEED=0 (actually select setseed (0) ). We did runs last night on our project machine which produced consistent pg_statisti

[PERFORM] Upgrade Woes

2003-09-10 Thread aturner
Hi, My name is Alex Turner and I work for a small Tech company in Pottstown PA. We run Postgresql on a number of systems for a variety of different applications, and it has been a joy to deal with all around, working fast and reliably for over 2 years. We recently upgraded from RedHat 7.2 to R

[PERFORM] Attempt at work around of int4 query won't touch int8 index ...

2003-09-10 Thread James Robinson
Hello, I'm trying a work-around on the "index on int8 column gets ignored by planner when queried by literal numbers lacking the explicit '::int8'" issue, and had hoped that perhaps I could create a functional index on the result of casting the pk field to int4, and mabye with a little lu

Re: [PERFORM] Need advice about triggers

2003-09-10 Thread Hannu Krosing
Mindaugas Riauba kirjutas K, 10.09.2003 kell 13:21: > > router_db=# explain analyze update ifdata set ifspeed=256000, > ifreason='12121', iflastupdate=CURRENT_TIMESTAMP WHERE clientid='#0003904#'; > QUERY PLAN >

Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...

2003-09-10 Thread Tom Lane
James Robinson <[EMAIL PROTECTED]> writes: > Is this just a dead end, or is there some variation of this that might > possibly work, so that ultimately an undoctored literal number, when > applied to an int8 column, could find an index? I think it's a dead end. What I was playing with this af

Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread Josh Berkus
Chris, > 1) Memory - clumsily adjusted shared_buffer - tried three values: 64, > 128, 256 with no discernible change in performance. Also adjusted, > clumsily, effective_cache_size to 1000, 2000, 4000 - with no > discernible change in performance. I looked at the Admin manual and > googled around

Re: [PERFORM] Upgrade Woes

2003-09-10 Thread Richard Huxton
On Wednesday 10 September 2003 18:53, [EMAIL PROTECTED] wrote: > Hi, > > My name is Alex Turner and I work for a small Tech company in Pottstown PA. > We run Postgresql on a number of systems for a variety of different > applications, and it has been a joy to deal with all around, working fast > a

Re: [PERFORM] [osdldbt-general] Re: [GENERAL] how to get accurate

2003-09-10 Thread Christopher Browne
The world rejoiced as [EMAIL PROTECTED] (Mary Edie Meredith) wrote: > We tried 1000 as the default and found that the plans were good > plans and were consistent, but the pg_statistics was not exactly the > same. > > We took Tom's' advice and tried SET SEED=0 (actually select setseed > (0) ). When