On 01/06/2011, at 11:45 AM, Jarrod Chesney wrote:
> I'm executing 30,000 single delete statements in one transaction.
>
> At this point i'm looking into combining the multiple deletes into one
> statement and breaking my big transaction into smaller ones of about 100
> deletes or so.
>
> On 0
On 05/30/2011 08:08 PM, Jarrod Chesney wrote:
My database uses joined table inheritance and my server version is 9.0
I have about 120,000 records in the table that everything else inherits from, if i
truncate-cascaded this table it happens almost instantly. If i run 30,000 prepared "DELE
I'm executing 30,000 single delete statements in one transaction.
At this point i'm looking into combining the multiple deletes into one
statement and breaking my big transaction into smaller ones of about 100
deletes or so.
On 01/06/2011, at 11:40 AM, Craig Ringer wrote:
> On 1/06/2011 7:11 A
On 1/06/2011 7:11 AM, Pierre C wrote:
If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
takes close to 10 minutes.
Do you run those in a single transaction or do you use one transaction
per DELETE ?
In the latter case, postgres will ensure each transaction is commited to
di
If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
takes close to 10 minutes.
Do you run those in a single transaction or do you use one transaction per
DELETE ?
In the latter case, postgres will ensure each transaction is commited to
disk, at each commit. Since this in
9.0rc1 ?
You know that the stable 9.0 has been out for quite a while now.
Its not going to affect the delete speed in any way, but I would
generally advice you to upgrade it to the lates 9.0.x
As for the delete it self, check if you have indices on the tables
that refer the main table on the refer
Hi All
My database uses joined table inheritance and my server version is 9.0
Version string PostgreSQL 9.0rc1 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (Gentoo 4.4.4-r1 p1.1, pie-0.4.5) 4.4.4, 64-bit
I have about 120,000 records in the table that everything
On Wed, Nov 25, 2009 at 4:13 PM, Luca Tettamanti wrote:
>
>
> DELETE FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.annotation_id =
> t2.annotation_id)
>
> performs event better:
>
> Seq Scan on t1 (cost=0.00..170388415.89 rows=22937406 width=6) (actual
> time=272.625..561241.294 rows=26185953
On Wed, Nov 25, 2009 at 04:22:47PM +0100, marcin mank wrote:
> On Tue, Nov 24, 2009 at 2:37 PM, Luca Tettamanti wrote:
> > -> HashAggregate (cost=1031681.15..1033497.20 rows=181605
> > width=8) (a
> > ctual time=571807.575..610178.552 rows=26185953 loops=1)
>
>
> This is Your problem.
On Tue, Nov 24, 2009 at 2:37 PM, Luca Tettamanti wrote:
> -> HashAggregate (cost=1031681.15..1033497.20 rows=181605 width=8)
> (a
> ctual time=571807.575..610178.552 rows=26185953 loops=1)
This is Your problem. The system`s estimate for the number of distinct
annotation_ids in t2 is w
e: 303-588-2547
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Luca
Tettamanti
Sent: Tuesday, November 24, 2009 6:37 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] DELETE performance problem
Hello,
I&#x
On Tuesday 24 November 2009, Thom Brown wrote:
>
> It's a shame there isn't a LIMIT option on DELETE so this can be done in
> small batches.
delete from table where pk in (select pk from table where delete_condition
limit X);
--
"No animals were harmed in the recording of this episode. We tri
On Tue, Nov 24, 2009 at 3:19 PM, Thom Brown wrote:
> 2009/11/24 Luca Tettamanti
>
> On Tue, Nov 24, 2009 at 3:59 PM, Jerry Champlin
>> wrote:
>> > You may want to consider using partitioning. That way you can drop the
>> > appropriate partition and never have the overhead of a delete.
>>
>> Hu
-ow...@postgresql.org] On Behalf Of Luca Tettamanti
Sent: Tuesday, November 24, 2009 6:37 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] DELETE performance problem
Hello,
I've run in a severe performance problem with the following statement:
DELETE FROM t1 WHERE t1.annotation_
2009/11/24 Luca Tettamanti
> On Tue, Nov 24, 2009 at 3:59 PM, Jerry Champlin
> wrote:
> > You may want to consider using partitioning. That way you can drop the
> > appropriate partition and never have the overhead of a delete.
>
> Hum, I don't think it's doable in my case; the partitioning is
On Tue, Nov 24, 2009 at 3:59 PM, Jerry Champlin
wrote:
> You may want to consider using partitioning. That way you can drop the
> appropriate partition and never have the overhead of a delete.
Hum, I don't think it's doable in my case; the partitioning is not
know a priori. First t1 is fully pop
Hello,
I've run in a severe performance problem with the following statement:
DELETE FROM t1 WHERE t1.annotation_id IN (
SELECT t2.annotation_id FROM t2)
t1 contains about 48M record (table size is 5.8GB), while t2 contains about 60M
record (total size 8.6GB). annotation_id is the PK in t
BTW: Have just tried "clean" (without any foreign keys constraints)
peformance of
"delete from tbl where field not in (select)"
vs
"create temporary table tmp(id) as select distinct field from tbl; delete
from tmp where id in (select); delete from tbl where field in (select id
from tmp)".
both tbl
OK, I did try you proposal and correlated subselect.
I have a database ~90 companies.
First try was to remove randomly selected 1000 companies
Uncorrelated subselect: 65899ms
Correlated subselect: 97467ms
using: 9605ms
my way: 104979ms. (without constraints recreate)
My is the worst because it
Hi,
Maybe you can try this syntax. I'm not sure, but it eventually perform better:
delete from company_alias USING comprm
where company_alias.company_id =comprm.id
Cheers,
Marc
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscript
2008/10/2 Tom Lane <[EMAIL PROTECTED]>
> "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <[EMAIL PROTECTED]> writes:
> > delete from company where id not in (select company_id from
> company_descr);
> > I've tried to analyze command, but unlike to other RDBM I've used it did
> not
> > include cascade
"=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <[EMAIL PROTECTED]> writes:
> delete from company where id not in (select company_id from company_descr);
> I've tried to analyze command, but unlike to other RDBM I've used it did not
> include cascade deletes/checks into query plan. That is first proble
Hello.
I have a database with company table that have a bunch of related
(delete=cascade) tables.
Also it has 1<->M relation to company_descr table.
Once we've found that ~half of our companies do not have any description and
we would like to remove them.
First this I've tried was
delete from comp
Thanks Stephan and Tom for your responses. We have been busy, so I haven't
had time to do any further research on this till yesterday. I found that the
large number of triggers on the parent or master table were foreign key
triggers for each table back to the child tables (update and delete on
Chris Kratz <[EMAIL PROTECTED]> writes:
> There are about 67 inherited tables that inherit the fields from this table,
> hence the 134 constraint triggers.
Why "hence"? Inheritance doesn't create any FK relationships. You must
have done so. What are those FK constraints exactly?
> Some of t
On Wed, 3 Mar 2004, Chris Kratz wrote:
> Which certainly points to the triggers being the culprit. In reading the
> documentation, it seems like the "delete from only..." statement should
> ignore the constraint triggers. But it seems quite obvious from the
Delete from only merely means that c
Hello all,
I have a performance issue that I cannot seem to solve and am hoping that
someone might be able to make some suggestions.
First some background information. We are using PostgreSQL 7.3.4 on Linux
with kernel 2.4.19. The box is a single P4 2.4Ghz proc with 1G ram and uw
scsi drive
27 matches
Mail list logo
