Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance

In fact, getting rid of vacuum full, or changing it to work like cluster, has been proposed in the past. The use case really is pretty narrow; cluster is a lot faster if there's a lot of unused space in the table, and if there's not, vacuum full isn't going to do much so there's not much point run

Re: [PERFORM] PostgreSQL in virtual machine

Andreas Tille wrote: Are there any experiences about reasonable performance increasing strategies? Are there any special things to regard in a VM? Not directly about Postgresql, but I'm seeing evidence that upgrading from vmware 2.5.3 to 3.0.1 seems to have solved disk access performance issue

[PERFORM] test ...please ignore

Test...please ignore Thanks Mike

Re: [PERFORM] Autocommit in libpq

"Dengler, Michael" <[EMAIL PROTECTED]> writes: > Thanks for the reply. Your advice to upgrade sounds urgent. Are there > critical reasons I need to go to 7.4.16? Read the release notes between 7.4.1 and 7.4.16 and judge for yourself: http://developer.postgresql.org/pgdocs/postgres/release.html

Re: [PERFORM] Autocommit in libpq

Thanks for the reply. Your advice to upgrade sounds urgent. Are there critical reasons I need to go to 7.4.16? Thanks Mike -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: March 13, 2007 11:29 AM To: Heikki Linnakangas Cc: Dengler, Michael; pgsql-performance@po

Re: [PERFORM] Postgres batch write very slow - what to do

Heikki Linnakangas <[EMAIL PROTECTED]> writes: > OP said he's using addBatch/executeBatch with a batch size of 100. The > JDBC driver sends the whole batch before waiting for responses. Perhaps a bit of checking with a packet sniffer would be warranted. If it's really working like that he shouldn

Re: [PERFORM] Postgres batch write very slow - what to do

Tom Lane wrote: It sounds like you're incurring a network round trip for each row, which will be expensive even for a co-located application. Perhaps Oracle's JDBC driver is smart enough to avoid that. I'm not sure what tricks are available for bulk loading with our JDBC driver --- the page Hei

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

Thanks for the advice Tom ! Setting enable_nestloop = off did improve the query a much better way than setting enable_seqscan to off. It does not screw the costs either (I had very odd costs with enable_seqscan to off like this : Nested Loop (cost=41665.30..42197.96 rows=1 width=96)

Re: [PERFORM] Postgres batch write very slow - what to do

femski <[EMAIL PROTECTED]> writes: > I am runing Postgres 8.2 on OpenSuse 10.2 with latest jdbc driver. I moved > the app to be collocated with the server. Oracle takes 60 sec. Postgres 275 > sec. For 4.7 million rows. > There are 4 CPUs on the server and one is runing close to 100% during > inser

Re: [PERFORM] Autocommit in libpq

Heikki Linnakangas wrote: > Dengler, Michael wrote: >> Please forgive me if I missed something (I have been searching for a >> definitive answer for this for 2 days). >> >> Is there any way to disable autocommit in libpq? (PG 7.4.1) > > Just call BEGIN to start a transaction, and COMMIT to commit

Re: [PERFORM] Autocommit in libpq

Dengler, Michael wrote: Please forgive me if I missed something (I have been searching for a definitive answer for this for 2 days). Is there any way to disable autocommit in libpq? (PG 7.4.1) Just call BEGIN to start a transaction, and COMMIT to commit it. Other than that, no. -- Heikki

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

<[EMAIL PROTECTED]> writes: > I was able to improve response time by seting enable_seqscan to off enable_nestloop = off would probably be a saner choice, at least for this particular query. regards, tom lane ---(end of broadcast)---

[PERFORM] Autocommit in libpq

Hi, Please forgive me if I missed something (I have been searching for a definitive answer for this for 2 days). Is there any way to disable autocommit in libpq? (PG 7.4.1) Thanks Mike

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

> From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Subject: Re: [PERFORM] Execution plan changed after upgrade > from 7.3.9 to 8.2.3 > > > Increasing the default_statistics_target to 1000 did not help. > It just make the vacuum full analyze to take longer

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

Increasing the default_statistics_target to 1000 did not help. It just make the vacuum full analyze to take longer to complete. Here is the output : CCM=# VACUUM FULL ANALYZE ; VACUUM CCM=# explain ANALYZE SELECT distinct C.cod_couleur_panneau, C.cod_couleur_panneau, cast ('LM05' as varchar), ca

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

All planner types were enabled. CCM=# select * from pg_settings where name like 'enable_%'; name| setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val -

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

[EMAIL PROTECTED] wrote: > Here it is : > > CCM=# SHOW enable_mergejoin; > enable_mergejoin > -- > on > (1 row) Sorry, my question was more general. Do you have _any_ of the planner types disabled? Try also enable_indexscan, etc; maybe select * from pg_settings where name like

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

[EMAIL PROTECTED] wrote: Thanks for the update. The following did not change anything in the execution plan ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS 1000 ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET STATISTICS 1000 ANALYZE lm05_t_tarif_panneau Hmm - so

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

Here it is : CCM=# SHOW enable_mergejoin; enable_mergejoin -- on (1 row) CCM=# Alvaro Herrera wrote: > [EMAIL PROTECTED] wrote: > >> I have attached the requested information. >> >> You will see that the query is quite messy and could be easily improved. >> Unfortunately, i

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

> From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Subject: Re: [PERFORM] Execution plan changed after upgrade > from 7.3.9 to 8.2.3 > > The following did not change anything in the execution plan > > ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min S

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

[EMAIL PROTECTED] wrote: > I have attached the requested information. > > You will see that the query is quite messy and could be easily improved. > Unfortunately, it came from a third party application and we do not have > access to the source code. There are only nested loops and hash joins, w

Re: [PERFORM] Postgres batch write very slow - what to do

I am runing Postgres 8.2 on OpenSuse 10.2 with latest jdbc driver. I moved the app to be collocated with the server. Oracle takes 60 sec. Postgres 275 sec. For 4.7 million rows. There are 4 CPUs on the server and one is runing close to 100% during inserts. Network history shows spikes of upto 60%

Re: [PERFORM] Postgres batch write very slow - what to do

On 3/13/07, femski <[EMAIL PROTECTED]> wrote: Folks ! I have a batch application that writes approx. 4 million rows into a narrow table. I am using JDBC addBatch/ExecuteBatch with auto commit turned off. Batch size is 100. So far I am seeing Postgres take roughly five times the time it takes to

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

Thanks for the update. The following did not change anything in the execution plan ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS 1000 ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET STATISTICS 1000 ANALYZE lm05_t_tarif_panneau I was able to improve response time

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

[EMAIL PROTECTED] wrote: I have attached the requested information. You will see that the query is quite messy and could be easily improved. Unfortunately, it came from a third party application and we do not have access to the source code. -> Hash Join (cost=6.31..3056.17 rows=116 width=47

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

I have attached the requested information. You will see that the query is quite messy and could be easily improved. Unfortunately, it came from a third party application and we do not have access to the source code. Thanks for your help, Best Regards, Vincent Michael Fuhr wrote: > On Tue, M

Re: [PERFORM] Postgres batch write very slow - what to do

femski wrote: I have a batch application that writes approx. 4 million rows into a narrow table. I am using JDBC addBatch/ExecuteBatch with auto commit turned off. Batch size is 100. So far I am seeing Postgres take roughly five times the time it takes to do this in the Oracle. The usual trick

[PERFORM] Postgres batch write very slow - what to do

Folks ! I have a batch application that writes approx. 4 million rows into a narrow table. I am using JDBC addBatch/ExecuteBatch with auto commit turned off. Batch size is 100. So far I am seeing Postgres take roughly five times the time it takes to do this in the Oracle. I have played with man

Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

On Tue, Mar 13, 2007 at 09:19:47AM +0100, [EMAIL PROTECTED] wrote: > Is there an option in the 8.2.3 to change in order to have the same > execution plan than before ? Let's see if we can figure out why 8.2.3 is choosing a bad plan. Have you run ANALYZE on the tables in 8.2.3? Could you post the

[PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3

Hello, I have upgraded from 7.3.9 to 8.2.3 and now the application that is using Postgres is really slow. Using pgfouine, I was able to identify a SQL select statement that was running in 500 ms before and now that is running in more than 20 seconds ! The reason is that the execution plan is