Re: [PERFORM] Help speeding up delete

Steve Wampler wrote: Is the performance behavior that we're experiencing a known problem with 7.2 that has been addressed in 7.4? Or will the upgrade fix other problems while leaving this one? I'm pretty sure that in versions earlier than 7.4, IN clauses that use a subquery will always use a

Re: [PERFORM] Help speeding up delete

I do not really see why all the distributions could do something like this, instead of mucking around with special statically compiled pg_dumps and the like... Contrib modules and tablespaces. Plus, no version of pg_dump before 8.0 is able to actually perform such reliable dumps and reloads (d

Re: [PERFORM] Help speeding up delete

Christopher Kings-Lynne wrote: Quite seriously, if you're still using 7.2.4 for production purposes you could justifiably be accused of negligence Perhaps we should put a link on the home page underneath LATEST RELEASEs saying 7.2: de-supported with a link to a scary note along the lin

Re: [PERFORM] Help speeding up delete

On Thu, Nov 17, 2005 at 11:07:42PM +0800, Christopher Kings-Lynne wrote: >> Isn't your distribution supposed to do this for you? Mine does these >> days... > A distribution that tries to automatically do a major postgresql update > is doomed to fail - spectacularly... Automatically? Well, you ca

Re: [PERFORM] Help speeding up delete

On Wed, 2005-11-16 at 19:40, Christopher Kings-Lynne wrote: > > Perhaps we should put a link on the home page underneath LATEST RELEASEs > > saying > > 7.2: de-supported > > > > with a link to a scary note along the lines of the above. > > > > ISTM that there are still too many people on olde

Re: [pgsql-www] [PERFORM] Help speeding up delete

> >>That way if someone wanted to upgrade from 7.2 to 8.1, they > can just > >>grab the latest dumper from the website, dump their old > database, then > >>upgrade easily. > > > > But if they're upgrading to 8.1, don't they already have the new > > pg_dump? How else are they going to dump the

Re: [pgsql-www] [PERFORM] Help speeding up delete

Christopher Kings-Lynne wrote: >> That most people don't know they should use the new one I understand >> though. But I don't see how this will help against that :-) > > It'll make it easy... As the miscreant that caused this thread to get started, let me *wholeheartedly* agree with Chris. An ea

Re: [pgsql-www] [PERFORM] Help speeding up delete

That way if someone wanted to upgrade from 7.2 to 8.1, they can just grab the latest dumper from the website, dump their old database, then upgrade easily. But if they're upgrading to 8.1, don't they already have the new pg_dump? How else are they going to dump their *new* database? Erm. Usu

Re: [PERFORM] Help speeding up delete

Isn't your distribution supposed to do this for you? Mine does these days... A distribution that tries to automatically do a major postgresql update is doomed to fail - spectacularly... Chris ---(end of broadcast)--- TIP 1: if posting/reading t

Re: [PERFORM] Help speeding up delete

On Thu, Nov 17, 2005 at 09:40:42AM +0800, Christopher Kings-Lynne wrote: > In my experience not many pgsql admins have test servers or the skills > to build up test machines with the latest pg_dump, etc. (Seriously.) > In fact, few realise at all that they should use the 8.1 dumper. Isn't your

Re: [pgsql-www] [PERFORM] Help speeding up delete

> > Perhaps we should put a link on the home page underneath LATEST > > RELEASEs saying > > 7.2: de-supported > > > > with a link to a scary note along the lines of the above. > > > > ISTM that there are still too many people on older releases. > > > > We probably need an explanation of why

Re: [PERFORM] Help speeding up delete

Perhaps we should put a link on the home page underneath LATEST RELEASEs saying 7.2: de-supported with a link to a scary note along the lines of the above. ISTM that there are still too many people on older releases. We probably need an explanation of why we support so many releases (in

Re: [PERFORM] Help speeding up delete

Update to 7.4 or later ;-) Quite seriously, if you're still using 7.2.4 for production purposes you could justifiably be accused of negligence. There are three or four data-loss-grade bugs fixed in the later 7.2.x releases, not to mention security holes; and that was before we abandoned support

Re: [PERFORM] Help speeding up delete

On 15-11-2005 15:18, Steve Wampler wrote: Magnus Hagander wrote: (This is after putting an index on the (id,name,value) tuple.) That outer seq scan is still annoying, but maybe this will be fast enough. I've passed this on, along with the (strong) recommendation that they upgrade PG. Have yo

Re: [PERFORM] Help speeding up delete

Arjen van der Meijden wrote: > On 15-11-2005 15:18, Steve Wampler wrote: > >> Magnus Hagander wrote: >> (This is after putting an index on the (id,name,value) tuple.) That >> outer seq scan >> is still annoying, but maybe this will be fast enough. >> >> I've passed this on, along with the (strong

Re: [PERFORM] Help speeding up delete

On Mon, 2005-11-14 at 18:42 -0500, Tom Lane wrote: > Steve Wampler <[EMAIL PROTECTED]> writes: > > We've got an older system in production (PG 7.2.4). > > Update to 7.4 or later ;-) > > Quite seriously, if you're still using 7.2.4 for production purposes > you could justifiably be accused of n

Re: [PERFORM] Help speeding up delete

Magnus Hagander wrote: >>Because I think we need to. The above would only delete rows >>that have name = 'obsid' and value = 'oid080505'. We need to >>delete all rows that have the same ids as those rows. >>However, from what you note, I bet we could do: >> >> DELETE FROM "tmp_table2" WHERE

Re: [PERFORM] Help speeding up delete

> Because I think we need to. The above would only delete rows > that have name = 'obsid' and value = 'oid080505'. We need to > delete all rows that have the same ids as those rows. > However, from what you note, I bet we could do: > >DELETE FROM "tmp_table2" WHERE id IN > (SELECT

Re: [PERFORM] Help speeding up delete

Scott Lamb wrote: > On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote: > >> Scott Lamb wrote: >> >>> On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: >>> # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; >>> >>> >>> >>> I

Re: [PERFORM] Help speeding up delete

On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote: Scott Lamb wrote: On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; Isn't this equivalent? select id from tmp_table2 where

Re: [PERFORM] Help speeding up delete

Joshua Marsh wrote: > > > On 11/14/05, *Steve Wampler* <[EMAIL PROTECTED] > > wrote: > > However, even that seems to have a much higher cost than I'd expect: > >lab.devel.configdb=# explain delete from "tmp_table2" where id in > (select id from

Re: [PERFORM] Help speeding up delete

On 11/14/05, Steve Wampler <[EMAIL PROTECTED]> wrote: However, even that seems to have a much higher cost than I'd expect:   lab.devel.configdb=# explain delete from "tmp_table2" where id in(select id from tmp_table2 where name='obsid' and value = 'oid080505');    NOTICE:  QUERY PLAN:   Seq

Re: [PERFORM] Help speeding up delete

Tom Lane wrote: > Steve Wampler <[EMAIL PROTECTED]> writes: > >>We've got an older system in production (PG 7.2.4). Recently >>one of the users has wanted to implement a selective delete, >>but is finding that the time it appears to take exceeds her >>patience factor by several orders of magnitud

Re: [PERFORM] Help speeding up delete

Scott Lamb wrote: > On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: > >> # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a >> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; > > > Isn't this equivalent? > > select id from tmp_table2 where name = 'obsid' and value = 'oid0805

Re: [PERFORM] Help speeding up delete

Steve Wampler <[EMAIL PROTECTED]> writes: > We've got an older system in production (PG 7.2.4). Recently > one of the users has wanted to implement a selective delete, > but is finding that the time it appears to take exceeds her > patience factor by several orders of magnitude. Here's > a synops

Re: [PERFORM] Help speeding up delete

On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; Isn't this equivalent? select id from tmp_table2 where name = 'obsid' and value = 'oid080505'; # DELETE FROM "tmp_table2" WHE

[PERFORM] Help speeding up delete

We've got an older system in production (PG 7.2.4). Recently one of the users has wanted to implement a selective delete, but is finding that the time it appears to take exceeds her patience factor by several orders of magnitude. Here's a synopsis of her report. It appears that the "WHERE id IN