pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow delete
10+ messages / 8 participants
[nested] [flat]
* slow delete
@ 2008-07-04 00:44 Jessica Richard <rjessil@yahoo.com>
2008-07-04 05:16 ` Re: slow delete Craig Ringer <craig@postnewspapers.com.au>
2008-07-04 10:11 ` Re: slow delete PFC <lists@peufeu.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jessica Richard @ 2008-07-04 00:44 UTC (permalink / raw)
To: pgsql-performance
I have a table with 29K rows total and I need to delete about 80K out of it.
I have a b-tree index on column cola (varchar(255) ) for my where clause to use.
my "select count(*) from test where cola = 'abc' runs very fast,
but my actual "delete from test where cola = 'abc';" takes forever, never can finish and I haven't figured why....
In my explain output, what is that "Bitmap Heap Scan on table"? is it a table scan? is my index being used?
How does delete work? to delete 80K rows that meet my condition, does Postgres find them all and delete them all together or one at a time?
by the way, there is a foreign key on another table that references the primary key col0 on table test.
Could some one help me out here?
Thanks a lot,
Jessica
testdb=# select count(*) from test;
count
--------
295793 --total 295,793 rows
(1 row)
Time: 155.079 ms
testdb=# select count(*) from test where cola = 'abc';
count
-------
80998 - need to delete 80,988 rows
(1 row)
testdb=# explain delete from test where cola = 'abc';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=2110.49..10491.57 rows=79766 width=6)
Recheck Cond: ((cola)::text = 'abc'::text)
-> Bitmap Index Scan on test_cola_idx (cost=0.00..2090.55 rows=79766 width=0)
Index Cond: ((cola)::text = 'abc'::text)
(4 rows)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
2008-07-04 00:44 slow delete Jessica Richard <rjessil@yahoo.com>
@ 2008-07-04 05:16 ` Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 0 replies; 10+ messages in thread
From: Craig Ringer @ 2008-07-04 05:16 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-performance
Jessica Richard wrote:
> I have a table with 29K rows total and I need to delete about 80K out of it.
I assume you meant 290K or something.
> I have a b-tree index on column cola (varchar(255) ) for my where clause
> to use.
>
> my "select count(*) from test where cola = 'abc' runs very fast,
>
> but my actual "delete from test where cola = 'abc';" takes forever,
> never can finish and I haven't figured why....
When you delete, the database server must:
- Check all foreign keys referencing the data being deleted
- Update all indexes on the data being deleted
- and actually flag the tuples as deleted by your transaction
All of which takes time. It's a much slower operation than a query that
just has to find out how many tuples match the search criteria like your
SELECT does.
How many indexes do you have on the table you're deleting from? How many
foreign key constraints are there to the table you're deleting from?
If you find that it just takes too long, you could drop the indexes and
foreign key constraints, do the delete, then recreate the indexes and
foreign key constraints. This can sometimes be faster, depending on just
what proportion of the table must be deleted.
Additionally, remember to VACUUM ANALYZE the table after that sort of
big change. AFAIK you shouldn't really have to if autovacuum is doing
its job, but it's not a bad idea anyway.
--
Craig Ringer
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
2008-07-04 00:44 slow delete Jessica Richard <rjessil@yahoo.com>
@ 2008-07-04 10:11 ` PFC <lists@peufeu.com>
1 sibling, 0 replies; 10+ messages in thread
From: PFC @ 2008-07-04 10:11 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; pgsql-performance
> by the way, there is a foreign key on another table that references the
> primary key col0 on table test.
Is there an index on the referencing field in the other table ? Postgres
must find the rows referencing the deleted rows, so if you forget to index
the referencing column, this can take forever.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
@ 2008-07-04 12:30 Jessica Richard <rjessil@yahoo.com>
2008-07-04 13:00 ` Re: slow delete tv@fuzzy.cz
0 siblings, 1 reply; 10+ messages in thread
From: Jessica Richard @ 2008-07-04 12:30 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
Thanks so much for your help.
I can select the 80K data out of 29K rows very fast, but we I delete them, it always just hangs there(> 4 hours without finishing), not deleting anything at all. Finally, I select pky_col where cola = 'abc', and redirect it to an out put file with a list of pky_col numbers, then put them in to a script with 80k lines of individual delete, then it ran fine, slow but actually doing the delete work:
delete from test where pk_col = n1;
delete from test where pk_col = n2;
...
My next question is: what is the difference between "select" and "delete"? There is another table that has one foreign key to reference the test (parent) table that I am deleting from and this foreign key does not have an index on it (a 330K row table).
Deleting one row at a time is fine: delete from test where pk_col = n1;
but deleting the big chunk all together (with 80K rows to delete) always hangs: delete from test where cola = 'abc';
I am wondering if I don't have enough memory to hold and carry on the 80k-row delete.....
but how come I can select those 80k-row very fast? what is the difference between select and delete?
Maybe the foreign key without an index does play a big role here, a 330K-row table references a 29K-row table will get a lot of table scan on the foreign table to check if each row can be deleted from the parent table... Maybe select from the parent table does not have to check the child table?
Thank you for pointing out about dropping the constraint first, I can imagine that it will be a lot faster.
But what if it is a memory issue that prevent me from deleting the 80K-row all at once, where do I check about the memory issue(buffer pool) how to tune it on the memory side?
Thanks a lot,
Jessica
----- Original Message ----
From: Craig Ringer <craig@postnewspapers.com.au>
To: Jessica Richard <rjessil@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Friday, July 4, 2008 1:16:31 AM
Subject: Re: [PERFORM] slow delete
Jessica Richard wrote:
> I have a table with 29K rows total and I need to delete about 80K out of it.
I assume you meant 290K or something.
> I have a b-tree index on column cola (varchar(255) ) for my where clause
> to use.
>
> my "select count(*) from test where cola = 'abc' runs very fast,
>
> but my actual "delete from test where cola = 'abc';" takes forever,
> never can finish and I haven't figured why....
When you delete, the database server must:
- Check all foreign keys referencing the data being deleted
- Update all indexes on the data being deleted
- and actually flag the tuples as deleted by your transaction
All of which takes time. It's a much slower operation than a query that
just has to find out how many tuples match the search criteria like your
SELECT does.
How many indexes do you have on the table you're deleting from? How many
foreign key constraints are there to the table you're deleting from?
If you find that it just takes too long, you could drop the indexes and
foreign key constraints, do the delete, then recreate the indexes and
foreign key constraints. This can sometimes be faster, depending on just
what proportion of the table must be deleted.
Additionally, remember to VACUUM ANALYZE the table after that sort of
big change. AFAIK you shouldn't really have to if autovacuum is doing
its job, but it's not a bad idea anyway.
--
Craig Ringer
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
2008-07-04 12:30 Re: slow delete Jessica Richard <rjessil@yahoo.com>
@ 2008-07-04 13:00 ` tv@fuzzy.cz
2008-07-04 15:48 ` Re: slow delete Alan Hodgson <ahodgson@simkin.ca>
0 siblings, 1 reply; 10+ messages in thread
From: tv@fuzzy.cz @ 2008-07-04 13:00 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
> My next question is: what is the difference between "select" and "delete"?
> There is another table that has one foreign key to reference the test
> (parent) table that I am deleting from and this foreign key does not have
> an index on it (a 330K row table).
The difference is that with SELECT you're not performing any modifications
to the data, while with DELETE you are. That means that with DELETE you
may have a lot of overhead due to FK checks etc.
Someone already pointed out that if you reference a table A from table B
(using a foreign key), then you have to check FK in case of DELETE, and
that may knock the server down if the table B is huge and does not have an
index on the FK column.
> Deleting one row at a time is fine: delete from test where pk_col = n1;
>
> but deleting the big chunk all together (with 80K rows to delete) always
> hangs: delete from test where cola = 'abc';
>
> I am wondering if I don't have enough memory to hold and carry on the
> 80k-row delete.....
> but how come I can select those 80k-row very fast? what is the difference
> between select and delete?
>
> Maybe the foreign key without an index does play a big role here, a
> 330K-row table references a 29K-row table will get a lot of table scan on
> the foreign table to check if each row can be deleted from the parent
> table... Maybe select from the parent table does not have to check the
> child table?
Yes, and PFC already pointed this out.
Tomas
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
2008-07-04 12:30 Re: slow delete Jessica Richard <rjessil@yahoo.com>
2008-07-04 13:00 ` Re: slow delete tv@fuzzy.cz
@ 2008-07-04 15:48 ` Alan Hodgson <ahodgson@simkin.ca>
0 siblings, 0 replies; 10+ messages in thread
From: Alan Hodgson @ 2008-07-04 15:48 UTC (permalink / raw)
To: pgsql-performance
On Friday 04 July 2008, tv@fuzzy.cz wrote:
> > My next question is: what is the difference between "select" and
> > "delete"? There is another table that has one foreign key to reference
> > the test (parent) table that I am deleting from and this foreign key
> > does not have an index on it (a 330K row table).
>
Yeah you need to fix that. You're doing 80,000 sequential scans of that
table to do your delete. That's a whole lot of memory access ...
I don't let people here create foreign key relationships without matching
indexes - they always cause problems otherwise.
--
Alan
^ permalink raw reply [nested|flat] 10+ messages in thread
* slow delete
@ 2023-08-15 20:23 Les <nagylzs@gmail.com>
2023-08-15 20:37 ` Re: slow delete Tom Lane <tgl@sss.pgh.pa.us>
2023-08-16 14:03 ` Re: slow delete Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Les @ 2023-08-15 20:23 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
I have created a table called _td with about 43 000 rows. I have tried to
use this as a primary key id list to delete records from my
product.product_file table, but I could not do it. It uses 100% of one CPU
and it takes forever. Then I changed the query to delete 100 records only,
and measure the speed:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
delete from product.product_file where id in (
select pf2_id from _td limit 100
)
It still takes 11 seconds. It means 110 msec / record, and that is
unacceptable.
I'm going to post the whole query plan at the end of this email, but I
would like to highlight the "Triggers" part:
"Triggers": [
{
"Trigger Name": "RI_ConstraintTrigger_a_26535",
"Constraint Name": "fk_pfft_product",
"Relation": "product_file",
"Time": 4.600,
"Calls": 90
},
{
"Trigger Name": "RI_ConstraintTrigger_a_26837",
"Constraint Name": "fk_product_file_src",
"Relation": "product_file",
"Time": 5.795,
"Calls": 90
},
{
"Trigger Name": "RI_ConstraintTrigger_a_75463",
"Constraint Name": "fk_pfq_src_product_file",
"Relation": "product_file",
"Time": 11179.429,
"Calls": 90
},
{
"Trigger Name": "_trg_002_aiu_audit_row",
"Relation": "product_file",
"Time": 49.410,
"Calls": 90
}
]
It seems that two foreign key constraints use 10.395 seconds out of the
total 11.24 seconds. But I don't see why it takes that much?
The product.product_file table has 477 000 rows:
CREATE TABLE product.product_file (
id uuid NOT NULL,
c_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
c_uid uuid NULL,
c_sid uuid NULL,
m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
m_uid uuid NULL,
m_sid uuid NULL,
product_id uuid NOT NULL,
product_file_type_id uuid NOT NULL,
file_id uuid NOT NULL,
product_file_status_id uuid NOT NULL,
dl_url text NULL,
src_product_file_id uuid NULL,
CONSTRAINT product_file_pkey PRIMARY KEY (id),
CONSTRAINT fk_pf_file FOREIGN KEY (file_id) REFERENCES media.file(id),
CONSTRAINT fk_pf_file_type FOREIGN KEY (product_file_type_id) REFERENCES
product.product_file_type(id),
CONSTRAINT fk_pf_product FOREIGN KEY (product_id) REFERENCES
product.product(id) ON DELETE CASCADE,
CONSTRAINT fk_product_file_src FOREIGN KEY (src_product_file_id) REFERENCES
product.product_file(id),
CONSTRAINT fk_product_file_status FOREIGN KEY (product_file_status_id)
REFERENCES product.product_file_status(id)
);
CREATE INDEX idx_product_file_dl_url ON product.product_file USING btree
(dl_url) INCLUDE (product_id) WHERE (dl_url IS NOT NULL);
CREATE INDEX idx_product_file_file_product ON product.product_file USING
btree (file_id, product_id);
CREATE INDEX idx_product_file_product_file ON product.product_file USING
btree (product_id, file_id);
CREATE INDEX idx_product_file_src ON product.product_file USING btree
(src_product_file_id) WHERE (src_product_file_id IS NOT NULL);
The one with fk_pfft_product looks like this, it has about 5000 records in
it:
CREATE TABLE product.product_file_tag (
id uuid NOT NULL,
c_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
c_uid uuid NULL,
c_sid uuid NULL,
m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
m_uid uuid NULL,
m_sid uuid NULL,
product_file_id uuid NOT NULL,
file_tag_id uuid NOT NULL,
CONSTRAINT product_file_tag_pkey PRIMARY KEY (id),
CONSTRAINT fk_pfft_file_tag FOREIGN KEY (file_tag_id) REFERENCES
product.file_tag(id) ON DELETE CASCADE DEFERRABLE,
CONSTRAINT fk_pfft_product FOREIGN KEY (product_file_id) REFERENCES
product.product_file(id) ON DELETE CASCADE DEFERRABLE
);
CREATE UNIQUE INDEX uidx_product_file_file_tag ON product.product_file_tag
USING btree (product_file_id, file_tag_id);
The other constraint has zero actual references, this returns zero:
select count(*) from product.product_file where src_product_file_id in (
select pf2_id from _td
); -- 0
I was trying to figure out how a foreign key constraint with zero actual
references can cost 100 msec / record, but I failed.
Can somebody please explain what is wrong here?
The plan is also visualized here:
http://tatiyants.com/pev/#/plans/plan_1692129126258
[
{
"Plan": {
"Node Type": "ModifyTable",
"Operation": "Delete",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "product_file",
"Schema": "product",
"Alias": "product_file",
"Startup Cost": 4.21,
"Total Cost": 840.79,
"Plan Rows": 0,
"Plan Width": 0,
"Actual Startup Time": 0.567,
"Actual Total Time": 0.568,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 582,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 10,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 4.21,
"Total Cost": 840.79,
"Plan Rows": 100,
"Plan Width": 46,
"Actual Startup Time": 0.161,
"Actual Total Time": 0.451,
"Actual Rows": 90,
"Actual Loops": 1,
"Output": ["product_file.ctid", "\"ANY_subquery\".*"],
"Inner Unique": true,
"Shared Hit Blocks": 402,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 10,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3.79,
"Total Cost": 4.79,
"Plan Rows": 100,
"Plan Width": 56,
"Actual Startup Time": 0.118,
"Actual Total Time": 0.136,
"Actual Rows": 100,
"Actual Loops": 1,
"Output": ["\"ANY_subquery\".*", "\"ANY_subquery\".pf2_id"],
"Group Key": ["\"ANY_subquery\".pf2_id"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 32,
"Disk Usage": 0,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Alias": "ANY_subquery",
"Startup Cost": 0.00,
"Total Cost": 3.54,
"Plan Rows": 100,
"Plan Width": 56,
"Actual Startup Time": 0.030,
"Actual Total Time": 0.083,
"Actual Rows": 100,
"Actual Loops": 1,
"Output": ["\"ANY_subquery\".*", "\"ANY_subquery\".pf2_id"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Limit",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.00,
"Total Cost": 2.54,
"Plan Rows": 100,
"Plan Width": 16,
"Actual Startup Time": 0.024,
"Actual Total Time": 0.053,
"Actual Rows": 100,
"Actual Loops": 1,
"Output": ["_td.pf2_id"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "_td",
"Schema": "public",
"Alias": "_td",
"Startup Cost": 0.00,
"Total Cost": 1100.07,
"Plan Rows": 43307,
"Plan Width": 16,
"Actual Startup Time": 0.023,
"Actual Total Time": 0.042,
"Actual Rows": 100,
"Actual Loops": 1,
"Output": ["_td.pf2_id"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "product_file_pkey",
"Relation Name": "product_file",
"Schema": "product",
"Alias": "product_file",
"Startup Cost": 0.42,
"Total Cost": 8.36,
"Plan Rows": 1,
"Plan Width": 22,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.003,
"Actual Rows": 1,
"Actual Loops": 100,
"Output": ["product_file.ctid", "product_file.id"],
"Index Cond": "(product_file.id = \"ANY_subquery\".pf2_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 400,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 10,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
"Planning": {
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 0.249,
"Triggers": [
{
"Trigger Name": "RI_ConstraintTrigger_a_26535",
"Constraint Name": "fk_pfft_product",
"Relation": "product_file",
"Time": 4.600,
"Calls": 90
},
{
"Trigger Name": "RI_ConstraintTrigger_a_26837",
"Constraint Name": "fk_product_file_src",
"Relation": "product_file",
"Time": 5.795,
"Calls": 90
},
{
"Trigger Name": "RI_ConstraintTrigger_a_75463",
"Constraint Name": "fk_pfq_src_product_file",
"Relation": "product_file",
"Time": 11179.429,
"Calls": 90
},
{
"Trigger Name": "_trg_002_aiu_audit_row",
"Relation": "product_file",
"Time": 49.410,
"Calls": 90
}
],
"Execution Time": 11240.265
}
]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
2023-08-15 20:23 slow delete Les <nagylzs@gmail.com>
@ 2023-08-15 20:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-16 04:43 ` Re: slow delete Les <nagylzs@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-08-15 20:37 UTC (permalink / raw)
To: Les <nagylzs@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Les <nagylzs@gmail.com> writes:
> It seems that two foreign key constraints use 10.395 seconds out of the
> total 11.24 seconds. But I don't see why it takes that much?
Probably because you don't have an index on the referencing column.
You can get away with that, if you don't care about the speed of
deletes from the PK table ...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
2023-08-15 20:23 slow delete Les <nagylzs@gmail.com>
2023-08-15 20:37 ` Re: slow delete Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-16 04:43 ` Les <nagylzs@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Les @ 2023-08-16 04:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Tom Lane <tgl@sss.pgh.pa.us> ezt írta (időpont: 2023. aug. 15., K, 22:37):
> Les <nagylzs@gmail.com> writes:
> > It seems that two foreign key constraints use 10.395 seconds out of the
> > total 11.24 seconds. But I don't see why it takes that much?
>
> Probably because you don't have an index on the referencing column.
> You can get away with that, if you don't care about the speed of
> deletes from the PK table ...
>
For fk_pfft_product constraint this is true, but I always thought that
PostgreSQL can use an index "partially". There is already an index:
CREATE UNIQUE INDEX uidx_product_file_file_tag ON product.product_file_tag
USING btree (product_file_id, file_tag_id);
It has the same order, only it has one column more. Wouldn't it be possible
to use it for the plan?
After I created these two missing indices:
CREATE INDEX idx_pft_pf ON product.product_file_tag USING btree
(product_file_id);
CREATE INDEX idx_pfq_src_pf ON product.product_file_queue USING btree
(src_product_file_id);
I could delete all 40 000 records in 10 seconds.
Thank you!
Laszlo
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: slow delete
2023-08-15 20:23 slow delete Les <nagylzs@gmail.com>
@ 2023-08-16 14:03 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Jeff Janes @ 2023-08-16 14:03 UTC (permalink / raw)
To: Les <nagylzs@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Aug 15, 2023 at 4:23 PM Les <nagylzs@gmail.com> wrote:
{
>
> "Trigger Name": "RI_ConstraintTrigger_a_75463",
>
> "Constraint Name": "fk_pfq_src_product_file",
>
> "Relation": "product_file",
>
> "Time": 11179.429,
>
> "Calls": 90
>
> },
>
...
> The one with fk_pfft_product looks like this, it has about 5000 records in
> it:
>
That constraint took essentially no time. You need to look into the one
that took all of the time,
which is fk_pfq_src_product_file.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-08-16 14:03 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-04 00:44 slow delete Jessica Richard <rjessil@yahoo.com>
2008-07-04 05:16 ` Craig Ringer <craig@postnewspapers.com.au>
2008-07-04 10:11 ` PFC <lists@peufeu.com>
2008-07-04 12:30 Re: slow delete Jessica Richard <rjessil@yahoo.com>
2008-07-04 13:00 ` tv@fuzzy.cz
2008-07-04 15:48 ` Alan Hodgson <ahodgson@simkin.ca>
2023-08-15 20:23 slow delete Les <nagylzs@gmail.com>
2023-08-15 20:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-16 04:43 ` Les <nagylzs@gmail.com>
2023-08-16 14:03 ` Jeff Janes <jeff.janes@gmail.com>
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