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
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
Even though the column in question is not unique on t2 could you not
index it? That should improve the performance of the inline query.
Are dates applicable in any way? In some cases adding a date field,
partitioning or indexing on that and adding where date>x days. That
can be an effectiv
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
You may want to consider using partitioning. That way you can drop the
appropriate partition and never have the overhead of a delete.
Jerry Champlin|Absolute Performance Inc.|Mobile: 303-588-2547
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-o
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
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
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
23 matches
Mail list logo
