pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp speeding up delete
28+ messages / 13 participants
[nested] [flat]
* Help speeding up delete
@ 2005-11-14 22:07 Steve Wampler <swampler@noao.edu>
0 siblings, 2 replies; 28+ messages in thread
From: Steve Wampler @ 2005-11-14 22:07 UTC (permalink / raw)
To: pgsql-performance
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 ..." is resulting in a seq scan that is causing
the problem, but we're not SQL expert enough to know
what to do about it.
Can someone point out what we're doing wrong, or how we
could get a (much) faster delete? Thanks!
Report:
============================================================
This command yields results in only a few seconds:
# SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
However, the following command does not seen to want to ever
complete (the person running this killed it after 1/2 hour).
# DELETE FROM "tmp_table2" WHERE id IN
# (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
==============================================================
The table has four columns. There are 6175 rows satifying the condition
given, and the table itself has 1539688 entries. Layout is:
lab.devel.configdb=# \d tmp_table2
Table "tmp_table2"
Column | Type | Modifiers
--------+--------------------------+-----------
id | character varying(64) |
name | character varying(64) |
units | character varying(32) |
value | text |
time | timestamp with time zone |
==============================================================
lab.devel.configdb=# EXPLAIN DELETE FROM "tmp_table2" WHERE id IN
lab.devel.configdb-# (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
lab.devel.configdb(# WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=0.00..154893452082.10 rows=769844 width=6)
SubPlan
-> Materialize (cost=100600.52..100600.52 rows=296330 width=100)
-> Hash Join (cost=42674.42..100600.52 rows=296330 width=100)
-> Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50)
-> Hash (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
lab.devel.configdb=# EXPLAIN (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
lab.devel.configdb(# WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505');
NOTICE: QUERY PLAN:
Hash Join (cost=42674.42..100600.52 rows=296330 width=100)
-> Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50)
-> Hash (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-14 23:20 Scott Lamb <slamb@slamb.org>
parent: Steve Wampler <swampler@noao.edu>
1 sibling, 1 reply; 28+ messages in thread
From: Scott Lamb @ 2005-11-14 23:20 UTC (permalink / raw)
To: Steve Wampler <swampler@noao.edu>; +Cc: pgsql-performance
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" WHERE id IN
> # (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
and this?
delete from tmp_table2 where name = 'obsid' and value = 'oid080505';
Why are you doing a self-join using id, which I assume is a primary key?
--
Scott Lamb <http://www.slamb.org/;
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-14 23:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Steve Wampler <swampler@noao.edu>
1 sibling, 2 replies; 28+ messages in thread
From: Tom Lane @ 2005-11-14 23:42 UTC (permalink / raw)
To: Steve Wampler <swampler@noao.edu>; +Cc: pgsql-performance
Steve Wampler <swampler@noao.edu> 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 synopsis of her report. It appears that the "WHERE
> id IN ..." is resulting in a seq scan that is causing
> the problem, but we're not SQL expert enough to know
> what to do about it.
> Can someone point out what we're doing wrong, or how we
> could get a (much) faster delete? Thanks!
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 for 7.2.
You *really* need to be thinking about an update.
regards, tom lane
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-14 23:52 Steve Wampler <swampler@noao.edu>
parent: Scott Lamb <slamb@slamb.org>
0 siblings, 2 replies; 28+ messages in thread
From: Steve Wampler @ 2005-11-14 23:52 UTC (permalink / raw)
To: Scott Lamb <slamb@slamb.org>; +Cc: pgsql-performance
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 = 'oid080505';
Probably, the user based the above on a query designed to find
all rows with the same id as those rows that have a.name='obsid' and
a.value='oid080505'. However, I think the above would work to locate
all the ids, which is all we need for the delete (see below)
>> # DELETE FROM "tmp_table2" WHERE id IN
>> # (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
>> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
>
>
> and this?
>
> delete from tmp_table2 where name = 'obsid' and value = 'oid080505';
>
> Why are you doing a self-join using id, which I assume is a primary key?
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 id FROM "temp_table2" WHERE name = 'obsid' and value= 'oid080505');
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 Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 width=6)
SubPlan
-> Materialize (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
And, sure enough, is taking an extrordinarily long time to run (more than
10 minutes so far, compared to < 10seconds for the select). Is this
really typical of deletes? It appears (to me) to be the Seq Scan on tmp_table2
that is the killer here. If we put an index on, would it help? (The user
claims she tried that and it's EXPLAIN cost went even higher, but I haven't
checked that...)
Thanks!
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 00:00 Steve Wampler <swampler@noao.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 28+ messages in thread
From: Steve Wampler @ 2005-11-15 00:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Steve Wampler <swampler@noao.edu> 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 synopsis of her report. It appears that the "WHERE
>>id IN ..." is resulting in a seq scan that is causing
>>the problem, but we're not SQL expert enough to know
>>what to do about it.
>
>
>>Can someone point out what we're doing wrong, or how we
>>could get a (much) faster delete? Thanks!
>
>
> Update to 7.4 or later ;-)
I was afraid you'd say that :-) I'm not officially involved in
this project anymore and was hoping for a fix that wouldn't drag
me back in. The security issues aren't a concern because this
DB is *well* hidden from the outside world (it's part of a telescope
control system behind several firewalls with no outside access).
However, the data-loss-grade bugs issue *is* important. We'll
try to do the upgrade as soon as we get some cloudy days to
actually do it!
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?
> 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 for 7.2.
> You *really* need to be thinking about an update.
Thanks!
Steve
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 00:10 Joshua Marsh <icub3d@gmail.com>
parent: Steve Wampler <swampler@noao.edu>
1 sibling, 1 reply; 28+ messages in thread
From: Joshua Marsh @ 2005-11-15 00:10 UTC (permalink / raw)
To: pgsql-performance
On 11/14/05, Steve Wampler <swampler@noao.edu> 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 Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 width=6)
> SubPlan
> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
> -> Seq Scan on tmp_table2 (cost=0.00..42674.32 rows=38 width=50)
>
For one reason or the other, the planner things a sequential scan is the
best solution. Try turning off seq_scan before the query and see if it
changes the plan (set enable_seqscan off;).
I've seen this problem with sub queries and that usually solves it.
--
This E-mail is covered by the Electronic Communications Privacy Act, 18
U.S.C. 2510-2521 and is legally privileged.
This information is confidential information and is intended only for the
use of the individual or entity named above. If the reader of this message
is not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 00:28 Steve Wampler <swampler@noao.edu>
parent: Joshua Marsh <icub3d@gmail.com>
0 siblings, 0 replies; 28+ messages in thread
From: Steve Wampler @ 2005-11-15 00:28 UTC (permalink / raw)
To: pgsql-performance
Joshua Marsh wrote:
>
>
> On 11/14/05, *Steve Wampler* <swampler@noao.edu
> <mailto:swampler@noao.edu>> 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 Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844
> width=6)
> SubPlan
> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
> -> Seq Scan on tmp_table2 (cost=0.00..42674.32
> rows=38 width=50)
>
>
> For one reason or the other, the planner things a sequential scan is the
> best solution. Try turning off seq_scan before the query and see if it
> changes the plan (set enable_seqscan off;).
>
> I've seen this problem with sub queries and that usually solves it.
>
Hmmm, not only does it still use sequential scans, it thinks it'll take
even longer:
set enable_seqscan to off;
SET VARIABLE
explain delete from "tmp_table2" where id in
(select id from tmp_table2 where name='obsid' and value = 'oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=100000000.00..160237039405992.50 rows=800836 width=6)
SubPlan
-> Materialize (cost=100043604.06..100043604.06 rows=45 width=26)
-> Seq Scan on tmp_table2 (cost=100000000.00..100043604.06 rows=45 width=26)
EXPLAIN
But the advice sounds like it *should* have helped...
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 01:08 Scott Lamb <slamb@slamb.org>
parent: Steve Wampler <swampler@noao.edu>
1 sibling, 1 reply; 28+ messages in thread
From: Scott Lamb @ 2005-11-15 01:08 UTC (permalink / raw)
To: Steve Wampler <swampler@noao.edu>; +Cc: pgsql-performance
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 name = 'obsid' and value =
>> 'oid080505';
>
> Probably, the user based the above on a query designed to find
> all rows with the same id as those rows that have a.name='obsid' and
> a.value='oid080505'.
Well, this indirection is only significant if those two sets can
differ. If (A) you meant "tmp_table2" when you wrote "tmp_tabl2e", so
this is a self-join, and (B) there is a primary key on "id", I don't
think that can ever happen.
> It appears (to me) to be the Seq Scan on tmp_table2
> that is the killer here. If we put an index on, would it help?
On...tmp_table2.id? If it is a primary key, there already is one. If
not, yeah, I expect it would help.
--
Scott Lamb <http://www.slamb.org/;
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 01:18 Leigh Dyer <lsd@linuxgamers.net>
parent: Steve Wampler <swampler@noao.edu>
0 siblings, 0 replies; 28+ messages in thread
From: Leigh Dyer @ 2005-11-15 01:18 UTC (permalink / raw)
To: pgsql-performance
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 seqscan, regardless of what indexes are
available. If you try an IN using explicit values though, it should use
the index.
Thanks
Leigh
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 04:03 Steve Wampler <swampler@noao.edu>
parent: Scott Lamb <slamb@slamb.org>
0 siblings, 0 replies; 28+ messages in thread
From: Steve Wampler @ 2005-11-15 04:03 UTC (permalink / raw)
To: Scott Lamb <slamb@slamb.org>; +Cc: pgsql-performance
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';
>>>
>>>
>>>
>>> Isn't this equivalent?
>>>
>>> select id from tmp_table2 where name = 'obsid' and value = 'oid080505';
>>
>>
>> Probably, the user based the above on a query designed to find
>> all rows with the same id as those rows that have a.name='obsid' and
>> a.value='oid080505'.
>
>
> Well, this indirection is only significant if those two sets can
> differ. If (A) you meant "tmp_table2" when you wrote "tmp_tabl2e", so
> this is a self-join, and (B) there is a primary key on "id", I don't
> think that can ever happen.
I wasn't clear. The original query was:
SELECT at.* FROM "tmp_table2" at, "tmp_table2" a
WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
which is significantly different than:
SELECT * FROM "tmp_table2" WHERE name='obsid' and value='oid080505';
The user had adapted that query for her needs, but it would have been
better to just use the query that you suggested (as the subselect in
the DELETE FROM...). Unfortunately, that only improves performance
slightly - it is still way too slow on deletes.
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 08:47 Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 28+ messages in thread
From: Magnus Hagander @ 2005-11-15 08:47 UTC (permalink / raw)
To: Steve Wampler <swampler@noao.edu>; Scott Lamb <slamb@slamb.org>; +Cc: pgsql-performance
> 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 id FROM "temp_table2" WHERE name = 'obsid' and
> value= 'oid080505');
>
> 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 Scan on tmp_table2 (cost=0.00..65705177237.26
> rows=769844 width=6)
> SubPlan
> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
> -> Seq Scan on tmp_table2 (cost=0.00..42674.32
> rows=38 width=50)
>
> EXPLAIN
>
> And, sure enough, is taking an extrordinarily long time to
> run (more than 10 minutes so far, compared to < 10seconds for
> the select). Is this really typical of deletes? It appears
> (to me) to be the Seq Scan on tmp_table2 that is the killer
> here. If we put an index on, would it help? (The user
> claims she tried that and it's EXPLAIN cost went even higher,
> but I haven't checked that...)
Earlier pg versions have always been bad at dealing with IN subqueries.
Try rewriting it as (with fixing any broken syntax, I'm not actually
testing this :P)
DELETE FROM tmp_table2 WHERE EXISTS
(SELECT * FROM tmp_table2 t2 WHERE t2.id=tmp_table2.id AND
t2.name='obsid' AND t2.value='oid080505')
I assume you do have an index on tmp_table2.id :-) And that it's
non-unique? (If it was unique, the previous simplification of the query
really should've worked..)
Do you also have an index on "name,value" or something like that, so you
get an index scan from it?
//Magnus
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 14:18 Steve Wampler <swampler@noao.edu>
parent: Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 28+ messages in thread
From: Steve Wampler @ 2005-11-15 14:18 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; +Cc: pgsql-performance
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 id IN
>> (SELECT id FROM "temp_table2" WHERE name = 'obsid' and
>>value= 'oid080505');
>>
>>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 Scan on tmp_table2 (cost=0.00..65705177237.26
>>rows=769844 width=6)
>> SubPlan
>> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
>> -> Seq Scan on tmp_table2 (cost=0.00..42674.32
>>rows=38 width=50)
>>
>> EXPLAIN
...
>
> Earlier pg versions have always been bad at dealing with IN subqueries.
> Try rewriting it as (with fixing any broken syntax, I'm not actually
> testing this :P)
>
> DELETE FROM tmp_table2 WHERE EXISTS
> (SELECT * FROM tmp_table2 t2 WHERE t2.id=tmp_table2.id AND
> t2.name='obsid' AND t2.value='oid080505')
Thanks - that looks *significantly* better:
lab.devel.configdb=# explain delete from tmp_table2 where exists
(select 1 from tmp_table2 t2 where
t2.id=tmp_table2.id and
t2.name='obsid' and t2.value='oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=0.00..9297614.80 rows=769844 width=6)
SubPlan
-> Index Scan using inv_index_2 on tmp_table2 t2 (cost=0.00..6.02 rows=1 width=0)
EXPLAIN
(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.
Thanks!!
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-16 20:00 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 28+ messages in thread
From: Simon Riggs @ 2005-11-16 20:00 UTC (permalink / raw)
To: pgsql-www@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Steve Wampler <swampler@noao.edu>; pgsql-performance
On Mon, 2005-11-14 at 18:42 -0500, Tom Lane wrote:
> Steve Wampler <swampler@noao.edu> 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 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 for 7.2.
> You *really* need to be thinking about an update.
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
comparison to licenced software) and a note that this does not imply the
latest releases are not yet production (in comparison to MySQL or Sybase
who have been in beta for a very long time).
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-16 20:22 Arjen van der Meijden <acmmailing@tweakers.net>
parent: Steve Wampler <swampler@noao.edu>
0 siblings, 1 reply; 28+ messages in thread
From: Arjen van der Meijden @ 2005-11-16 20:22 UTC (permalink / raw)
To: Steve Wampler <swampler@noao.edu>; +Cc: pgsql-performance
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 you tried with an index on (name,value) and of course one on id ?
Best regards,
Arjen
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-16 20:29 Steve Wampler <swampler@noao.edu>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
0 siblings, 0 replies; 28+ messages in thread
From: Steve Wampler @ 2005-11-16 20:29 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: pgsql-performance
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) recommendation that they
>> upgrade PG.
>
>
> Have you tried with an index on (name,value) and of course one on id ?
Yes, although not with a unique index on (name,value) [possible, but not
so on the just-id index]. Anyway, it turns out the latest incarnation
is 'fast enough' for the user's need, so she's not doing any more with
it until after an upgrade.
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 01:38 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 28+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 01:38 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-www@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Steve Wampler <swampler@noao.edu>; pgsql-performance
>>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 for 7.2.
>>You *really* need to be thinking about an update.
>
>
> 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.
I strongly support an explicit desupported notice for 7.2 and below on
the website...
Chris
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 01:40 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 2 replies; 28+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 01:40 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-www@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Steve Wampler <swampler@noao.edu>; pgsql-performance
> 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
> comparison to licenced software) and a note that this does not imply the
> latest releases are not yet production (in comparison to MySQL or Sybase
> who have been in beta for a very long time).
By the way, is anyone interested in creating some sort of online
repository on pgsql.org or pgfoundry where we can keep statically
compiled pg_dump/all for several platforms for 8.1?
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.
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.
Chris
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 09:19 Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 28+ messages in thread
From: Magnus Hagander @ 2005-11-17 09:19 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-www@postgresql.org; Tom Lane <tgl@sss.pgh.pa.us>; Steve Wampler <swampler@noao.edu>; pgsql-performance
> > 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
> > comparison to licenced software) and a note that this does
> not imply
> > the latest releases are not yet production (in comparison
> to MySQL or
> > Sybase who have been in beta for a very long time).
>
> By the way, is anyone interested in creating some sort of
> online repository on pgsql.org or pgfoundry where we can keep
> statically compiled pg_dump/all for several platforms for 8.1?
>
> 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?
> In my experience not many pgsql admins have test servers or
> the skills to build up test machines with the latest pg_dump,
I don't, but I still dump with the latest version - works fine both on
linux and windows for me...
> etc. (Seriously.) In fact, few realise at all that they
> should use the 8.1 dumper.
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 :-)
//Magnus
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-17 11:19 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 1 reply; 28+ messages in thread
From: Steinar H. Gunderson @ 2005-11-17 11:19 UTC (permalink / raw)
To: pgsql-performance
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 distribution supposed to do this for you? Mine does these days...
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:05 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 28+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 15:05 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-www@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Steve Wampler <swampler@noao.edu>; pgsql-performance
>>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. Usually when you install the new package/port for 8.1, you cannot
have both new and old installed at the same time man. Remember they
both store exactly the same binary files in exactly the same place.
>>In my experience not many pgsql admins have test servers or
>>the skills to build up test machines with the latest pg_dump,
>
> I don't, but I still dump with the latest version - works fine both on
> linux and windows for me...
So you're saying you DO have the skills to do it then...
>>etc. (Seriously.) In fact, few realise at all that they
>>should use the 8.1 dumper.
>
> 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...
Chris
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-17 15:07 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 28+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 15:07 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:13 Steve Wampler <swampler@noao.edu>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 28+ messages in thread
From: Steve Wampler @ 2005-11-17 15:13 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Magnus Hagander <mha@sollentuna.net>; Simon Riggs <simon@2ndquadrant.com>; pgsql-www@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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 easy way to get the pg_dump
for the upgrade target to run with the upgradable source
would work wonders. (Instructions included, of course.)
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:39 Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 28+ messages in thread
From: Magnus Hagander @ 2005-11-17 15:39 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-www@postgresql.org; Tom Lane <tgl@sss.pgh.pa.us>; Steve Wampler <swampler@noao.edu>; pgsql-performance
> >>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. Usually when you install the new package/port for 8.1,
> you cannot have both new and old installed at the same time
> man. Remember they both store exactly the same binary files
> in exactly the same place.
Urrk. Didn't think of that. I always install from source on Unix, which
doesn't have the problem. And the Windows port doesn't have this problem
- it will put the binaries in a version dependant directory.
One could claim the packages are broken ;-), but that's not gonig to
help here, I know...
(I always install in pgsql-<version>, and then symlink pgsql there..)
> >>etc. (Seriously.) In fact, few realise at all that they should use
> >>the 8.1 dumper.
> >
> > 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...
You assume they know enough to download it. If they don't know to look
for it, they still won't find it.
But the bottom line: I can see how it would be helpful if you're on a
distro which packages postgresql in a way that prevents you from
installing more than one version at the same time.
//Magnus
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:56 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 28+ messages in thread
From: Scott Marlowe @ 2005-11-17 15:56 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-www@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Steve Wampler <swampler@noao.edu>; pgsql-performance
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 older releases.
> >
> > We probably need an explanation of why we support so many releases (in
> > comparison to licenced software) and a note that this does not imply the
> > latest releases are not yet production (in comparison to MySQL or Sybase
> > who have been in beta for a very long time).
>
> By the way, is anyone interested in creating some sort of online
> repository on pgsql.org or pgfoundry where we can keep statically
> compiled pg_dump/all for several platforms for 8.1?
>
> 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.
>
> 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.
I would especially like such a thing available as an RPM. A
pgsql-8.1-clienttools.rpm or something like that, with psql, pg_dump,
pg_restore, and what other command line tools you can think of that
would help.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 16:26 Svenne Krap <svenne@krap.dk>
parent: Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 28+ messages in thread
From: Svenne Krap @ 2005-11-17 16:26 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; pgsql-www@postgresql.org
>You assume they know enough to download it. If they don't know to look
>for it, they still won't find it.
>
>
I think there should be a big, fat warning in the release notes.
Something like:
WARNING: Upgrading to version X.Y requires a full dump/restore cycle.
Please download the appropriate dump-utility from http://postgresql.org/dumputils/X.Y/
and make a copy of your database before installing the new version X.Y.
And then link to a dir with the statically linked pg_dump (and -all) for
the most common platforms. I must admit, I did not know that one should
use the new tool in a cyclus (and I have used Pg almost exclusively
since 7.0). That could also be the place to add a line about version S.T
is now considered obsolete and unsupported.
/Svenne
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-17 17:25 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 28+ messages in thread
From: Steinar H. Gunderson @ 2005-11-17 17:25 UTC (permalink / raw)
To: pgsql-performance
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 can install the two versions side-by-side, and do
pg_upgradecluster, which ports your configuration to the new version and does
a pg_dump between the two versions; exactly what a system administrator would
do. Of course, stuff _can_ fail, but it works for the simple cases, and a
great deal of the not-so-simple cases. I did this for our cluster the other
day (130 wildly different databases, from 7.4 to 8.1) and it worked
flawlessly.
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...
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-18 01:39 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 28+ messages in thread
From: Ron Mayer @ 2005-11-18 01:39 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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 lines of the above.
>
> I strongly support an explicit desupported notice for 7.2 and below on
> the website...
I'd go so far as to say the version #s of supported versions
is one of pieces of information I'd most expect to see on
the main support page ( http://www.postgresql.org/support/ ).
Perhaps it'd be nice to even show a table like
Version Released On Support Ends
7.1 4 BC Sep 3 1752
7.2 Feb 31 1900 Jan 0 2000
7.4 2003-11-17 At least 2005-x-x
8.0 2005-01-19 At least 2006-x-x
with a footnote saying that only the most recent dot release
of each family is considered supported.
It also might be nice to have a footnote saying that any
of the commercical support companies might support the older
versions for longer periods of time.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Help speeding up delete
@ 2005-11-18 01:56 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 28+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-18 01:56 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
> 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 (due to bugs). However, that's probably moot
these days.
Chris
^ permalink raw reply [nested|flat] 28+ messages in thread
end of thread, other threads:[~2005-11-18 01:56 UTC | newest]
Thread overview: 28+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-14 22:07 Help speeding up delete Steve Wampler <swampler@noao.edu>
2005-11-14 23:20 ` Scott Lamb <slamb@slamb.org>
2005-11-14 23:52 ` Steve Wampler <swampler@noao.edu>
2005-11-15 00:10 ` Joshua Marsh <icub3d@gmail.com>
2005-11-15 00:28 ` Steve Wampler <swampler@noao.edu>
2005-11-15 01:08 ` Scott Lamb <slamb@slamb.org>
2005-11-15 04:03 ` Steve Wampler <swampler@noao.edu>
2005-11-14 23:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-15 00:00 ` Steve Wampler <swampler@noao.edu>
2005-11-15 01:18 ` Leigh Dyer <lsd@linuxgamers.net>
2005-11-16 20:00 ` Simon Riggs <simon@2ndquadrant.com>
2005-11-17 01:38 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-18 01:39 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2005-11-17 01:40 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-17 11:19 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-11-17 15:07 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-17 17:25 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-11-18 01:56 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-17 15:56 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-11-15 08:47 Re: Help speeding up delete Magnus Hagander <mha@sollentuna.net>
2005-11-15 14:18 ` Steve Wampler <swampler@noao.edu>
2005-11-16 20:22 ` Arjen van der Meijden <acmmailing@tweakers.net>
2005-11-16 20:29 ` Steve Wampler <swampler@noao.edu>
2005-11-17 09:19 Re: [PERFORM] Help speeding up delete Magnus Hagander <mha@sollentuna.net>
2005-11-17 15:05 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-17 15:13 ` Steve Wampler <swampler@noao.edu>
2005-11-17 15:39 Re: [PERFORM] Help speeding up delete Magnus Hagander <mha@sollentuna.net>
2005-11-17 16:26 ` Svenne Krap <svenne@krap.dk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox