Re: [PERFORM] function slower than the same code in an sql file
El 03/11/11 11:42, Robert Haas escribió: On Fri, Oct 28, 2011 at 9:39 AM, CS DBAcs_...@consistentstate.com wrote: No parameters, one of them looks like this: [ code snippet ] It's hard to believe this is the real code, because SELECT without INTO will bomb out inside a PL/pgsql function, won't it? But he's using CREATE TABLE xyz_view_m AS So it seems correct to me Regards Rodrigo
Re: [PERFORM] CPU bound at 99%
Are tables vacuumed often? Bryan Buecking escribió: On Tue, Apr 22, 2008 at 10:55:19AM -0500, Erik Jones wrote: On Apr 22, 2008, at 10:31 AM, Bryan Buecking wrote: max_connections = 2400 That is WAY too high. Get a real pooler, such as pgpool, and drop that down to 1000 and test from there. I agree, but the number of idle connections dont' seem to affect performace only memory usage. I'm trying to lessen the load of connection setup. But sounds like this tax is minimal? When these issues started happening, max_connections was set to 1000 and I was not using persistent connections. I see you mentioned 500 concurrent connections. Are each of those connections actually doing something? Yes out of the 2400 odd connections, 500 are either in SELECT or RESET. My guess that once you cut down on the number actual connections you'll find that each connection can get it's work done faster and you'll see that number drop significantly. I agree, but not in this case. I will look at using pooling. smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] seq scan issue...
kevin kempter escribió: Hi List; I have a large tble (playback_device) with 6million rows in it. The aff_id_tmp1 table has 600,000 rows. I also have this query: select distinct tmp1.affiliate_id, tmp1.name, tmp1.description, tmp1.create_dt, tmp1.playback_device_id, pf.segment_id from aff_id_tmp1 tmp1, playback_fragment pf where tmp1.playback_device_id = pf.playback_device_id ; The Primary Key for playback_device is the playback_device_id there is also an index on playback_device_id on the aff_id_tmp1 table. The only join condition I have is on this key pair (I've posted my explain plan below) - why am I still getting a seq scan ? Thanks in advance. Explain PLan explain select distinct tmp1.affiliate_id, tmp1.name, tmp1.description, tmp1.create_dt, tmp1.playback_device_id, pf.segment_id from aff_id_tmp1 tmp1, playback_fragment pf where tmp1.playback_device_id = pf.playback_device_id ; Unique (cost=2966361.56..3194555.91 rows=10104496 width=97) - Sort (cost=2966361.56..2998960.76 rows=13039677 width=97) Sort Key: tmp1.affiliate_id, tmp1.name, tmp1.description, tmp1.create_dt, tmp1.playback_device_id, pf.segment_id - Hash Join (cost=23925.45..814071.14 rows=13039677 width=97) Hash Cond: (pf.playback_device_id = tmp1.playback_device_id) - Seq Scan on playback_fragment pf (cost=0.00..464153.77 rows=130 39677 width=16) - Hash (cost=16031.31..16031.31 rows=631531 width=89) - Seq Scan on aff_id_tmp1 tmp1 (cost=0.00..16031.31 rows=63 1531 width=89) (1068 rows) Cause you are getting all the rows so pgsql need to scan all the table... smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] Autovacuum running out of memory
Jason Lustig escribió: On Oct 16, 2007, at 10:22 AM, Richard Huxton wrote: Add some lines to /etc/security/limits.conf to increase them. Sorry for being somewhat of a linux novice -- but what is the best way to do this? It doesn't seem to provide matching options from ulimit to the limits.conf file. Thanks, Jason ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly http://www.userlocal.com/security/secpam.php smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] Upgraded to 8.2.3 --- still having performance issues
Carlos Moreno wrote: Tom Lane wrote: Carlos Moreno [EMAIL PROTECTED] writes: I would have expected a mind-blowing increase in responsiveness and overall performance. However, that's not the case --- if I didn't know better, I'd probably tend to say that it is indeed the opposite (performance seems to have deteriorated) Did you remember to re-ANALYZE everything after loading up the new database? That's a frequent gotcha ... I had done it, even though I was under the impression that it wouldn't be necessary with 8.2.x (I still chose to do it just in case). I've since discovered a problem that *may* be related to the deterioration of the performance *now* --- but that still does not explain the machine choking since last night, so any comments or tips are still most welcome. Thanks, Carlos -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster And the problem that *may* be related is? All the information is required so someone can give you good information... ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Server Startup Error
Gauri Kanekar wrote: Hi List, Machine was down due to some hardware problem. After then when i issue this command /usr/local/pgsql/bin/psql -l its giving me the following error psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Can anybody tell me what going wrong?? -- Regards Gauri Postgres is not running, start it and try again ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Server Startup Error
Gauri Kanekar wrote: Thanks, But how to start postgres server On 2/26/07, *Rodrigo Gonzalez* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Gauri Kanekar wrote: Hi List, Machine was down due to some hardware problem. After then when i issue this command /usr/local/pgsql/bin/psql -l its giving me the following error psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Can anybody tell me what going wrong?? -- Regards Gauri Postgres is not running, start it and try again -- Regards Gauri which OS? compiled from source? did you install from package? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq