Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Jon Nelson
On Mon, May 17, 2010 at 7:28 AM, A. Kretschmer wrote: > In response to Jon Nelson : >> On Mon, May 17, 2010 at 5:10 AM, Pierre C wrote: >> > - or use a JOIN delete with a virtual VALUES table >> > - or fill a temp table with ids and use a JOIN DELETE >> >> What is a virtual VALUES table? Can you

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Grzegorz Jaśkiewicz
again VALUES(1,2), (2,3), ; is a 'virtual table', as he calls it. It really is not a table to postgresql. I guess he is just using that naming convention. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Jon Nelson
2010/5/17 Віталій Тимчишин : > > > 2010/5/17 Jon Nelson >> >> On Mon, May 17, 2010 at 5:10 AM, Pierre C wrote: >> > - or use a JOIN delete with a virtual VALUES table >> > - or fill a temp table with ids and use a JOIN DELETE >> >> What is a virtual VALUES table? Can you give me an example of usi

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread A. Kretschmer
In response to Jon Nelson : > On Mon, May 17, 2010 at 5:10 AM, Pierre C wrote: > > - or use a JOIN delete with a virtual VALUES table > > - or fill a temp table with ids and use a JOIN DELETE > > What is a virtual VALUES table? Can you give me an example of using a > virtual table with selects, j

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Grzegorz Jaśkiewicz
On Mon, May 17, 2010 at 12:54 PM, Jon Nelson wrote: > On Mon, May 17, 2010 at 5:10 AM, Pierre C wrote: >> - or use a JOIN delete with a virtual VALUES table >> - or fill a temp table with ids and use a JOIN DELETE > > What is a virtual VALUES table? Can you give me an example of using a > virtual

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Віталій Тимчишин
2010/5/17 Jon Nelson > > On Mon, May 17, 2010 at 5:10 AM, Pierre C wrote: > > - or use a JOIN delete with a virtual VALUES table > > - or fill a temp table with ids and use a JOIN DELETE > > What is a virtual VALUES table? Can you give me an example of using a > virtual table with selects, joins

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Jon Nelson
On Mon, May 17, 2010 at 5:10 AM, Pierre C wrote: > - or use a JOIN delete with a virtual VALUES table > - or fill a temp table with ids and use a JOIN DELETE What is a virtual VALUES table? Can you give me an example of using a virtual table with selects, joins, and also deletes? -- Jon -- Se

Re: [PERFORM] old server, new server, same performance

2010-05-17 Thread Scott Marlowe
On Mon, May 17, 2010 at 3:52 AM, Piotr Legiecki wrote: > Scott Marlowe pisze: > So still I don't get this: select * from table; on old server takes 0,5 sec, on new one takes 6sec. Why there is so big difference? And it does not matter how good or bad select is to measure perfor

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Pierre C
DELETE FROM table1 WHERE table2_id = ? For bulk deletes, try : DELETE FROM table1 WHERE table2_id IN (list of a few thousands ids) - or use a JOIN delete with a virtual VALUES table - or fill a temp table with ids and use a JOIN DELETE This will save cliet/server roundtrips. Now, something t

Re: [PERFORM] old server, new server, same performance

2010-05-17 Thread Piotr Legiecki
Scott Marlowe pisze: So still I don't get this: select * from table; on old server takes 0,5 sec, on new one takes 6sec. Why there is so big difference? And it does not matter how good or bad select is to measure performance, because I don't measure the performance, I measure the relative diffe

Re: [PERFORM] old server, new server, same performance

2010-05-17 Thread Scott Marlowe
Whoops, wrong thread. On Mon, May 17, 2010 at 2:52 AM, Scott Marlowe wrote: > On Mon, May 17, 2010 at 2:10 AM, Scott Marlowe > wrote: >> On Mon, May 17, 2010 at 2:06 AM, Piotr Legiecki wrote: >>> 2. select count(*) from some_table; runs in a fraction of a second on the >>> console on both serv

Re: [PERFORM] old server, new server, same performance

2010-05-17 Thread Scott Marlowe
On Mon, May 17, 2010 at 2:10 AM, Scott Marlowe wrote: > On Mon, May 17, 2010 at 2:06 AM, Piotr Legiecki wrote: >> 2. select count(*) from some_table; runs in a fraction of a second on the >> console on both servers (there are only 4000 records, the second longer >> table has 5 but it does not

Re: [PERFORM] old server, new server, same performance

2010-05-17 Thread Scott Marlowe
On Mon, May 17, 2010 at 2:06 AM, Piotr Legiecki wrote: > 2. select count(*) from some_table; runs in a fraction of a second on the > console on both servers (there are only 4000 records, the second longer > table has 5 but it does not matter very much). From pg_admin the results > are: > - slo

Re: [PERFORM] old server, new server, same performance

2010-05-17 Thread Piotr Legiecki
Scott Marlowe pisze: 2010/5/14 Piotr Legiecki : So what is the problem? My simple 'benchmarks' I have done with pgAdmin in spare time. pgAdmin is the latest 1.8.2 on both D and E. Using pgAdmin on my (D) computer I have run SELECT * from some_table; and noted the execution time on both A and B

Re: [PERFORM] Benchmark with FreeBSD 8.0 and pgbench

2010-05-17 Thread Віталій Тимчишин
Hi. Not strictly connected to your tests, but: As of ZFS, we've had experience that it degrades over time after random updates because of files becoming non-linear and sequential reads becomes random. Also there are Q about ZFS block size - setting it to 8K makes first problem worse, setting it to