pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete, foreign key, index usage
9+ messages / 4 participants
[nested] [flat]
* Delete, foreign key, index usage
@ 2017-04-04 12:07 Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-05-25 12:21 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Johann Spies @ 2017-04-04 12:07 UTC (permalink / raw)
To: pgsql-performance
While updating our database which includes a lot of deletions where a lot
of foreign key references are involved we found that in the case of two
tables the indexes are ignored and it slow down the process a lot.
Here are stats about those two tables:
relname seq_scan seq_tup_read idx_scan idx_tup_fetch n_tup_ins n_tup_upd
n_tup_del n_tup_hot_upd n_live_tup n_dead_tup n_mod_since_analyze
belongs_to 227 52539487559 0 0 771 0 1459 0 125 1459 2230
publication 229 11502854612 0 0 254 0 229 0 60 229 483
Publication ( has a foreign key (ut) and more than 50million records) that
references the top of the chain of references. This field (ut) is also the
primary key of publication.
In the case of belongs_to (about 231393000 records) which references the
same table (article) ut has an index.
All other tables in this dependency chain reports 100% or near 100% usage
of the indexes e.g.
citation_2010_2014 0 0 226 1882 2510 0 1910 0 816 1910 4420
The indexes are on a ssd and we have set the random_page_cost to 1 for
those queries.
The definition of belongs_to:
CREATE TABLE wos_2017_1.belongs_to
(
suborg_id uuid,
organisation_id uuid,
address_id uuid,
ut citext,
uuid uuid NOT NULL,
id integer NOT NULL DEFAULT
nextval('wos_2017_1.belongs2_id_seq'::regclass),
pref_name_id uuid,
addr_no smallint,
reprint_addr_no smallint,
CONSTRAINT belongs2_pkey PRIMARY KEY (uuid),
CONSTRAINT belongs_to_address_id_fkey FOREIGN KEY (address_id)
REFERENCES wos_2017_1.address (uuid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL,
CONSTRAINT belongs_to_pref_name_id_fkey FOREIGN KEY (pref_name_id)
REFERENCES wos_2017_1.org_pref_name (uuid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL,
CONSTRAINT belongs_to_suborg_id_fkey FOREIGN KEY (suborg_id)
REFERENCES wos_2017_1.suborg (uuid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL,
CONSTRAINT belongs_to_ut_fkey FOREIGN KEY (ut)
REFERENCES wos_2017_1.article (ut) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT belongs2_id_key UNIQUE (id),
CONSTRAINT
belongs2_ut_suborg_id_organisation_id_address_id_addr_no_re_key UNIQUE (ut,
suborg_id, organisation_id, address_id, addr_no, reprint_addr_no,
pref_name_id)
)
WITH (
OIDS=FALSE
);
with indexes on address_id, organisation_id, pref_name_id, ut
I have also tried to set enable_seqscan to false for these queries, but
still no usage of the indexes.
Why would that be?
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
@ 2017-04-05 10:40 ` Johann Spies <johann.spies@gmail.com>
2017-04-05 11:15 ` Re: Delete, foreign key, index usage Rick Otten <rottenwindfish@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Johann Spies @ 2017-04-05 10:40 UTC (permalink / raw)
To: pgsql-performance
On 4 April 2017 at 14:07, Johann Spies <johann.spies@gmail.com> wrote:
> Why would that be?
To answer my own question. After experimenting a lot we found that
9.6 uses a parallel seqscan that is actually a lot faster than using
the index on these large tables.
This, to us was a surprise!
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
--
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] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
@ 2017-04-05 11:15 ` Rick Otten <rottenwindfish@gmail.com>
2017-04-24 06:48 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rick Otten @ 2017-04-05 11:15 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; +Cc: pgsql-performance
On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies <johann.spies@gmail.com> wrote:
> On 4 April 2017 at 14:07, Johann Spies <johann.spies@gmail.com> wrote:
>
> > Why would that be?
>
> To answer my own question. After experimenting a lot we found that
> 9.6 uses a parallel seqscan that is actually a lot faster than using
> the index on these large tables.
>
> This, to us was a surprise!
>
>
If you have modern GPU's available, you could try the pg-strom extension -
https://github.com/pg-strom/devel
It leverages GPU's to further parallelize scans.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 11:15 ` Re: Delete, foreign key, index usage Rick Otten <rottenwindfish@gmail.com>
@ 2017-04-24 06:48 ` Johann Spies <johann.spies@gmail.com>
2017-04-24 13:17 ` Re: Delete, foreign key, index usage Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 9+ messages in thread
From: Johann Spies @ 2017-04-24 06:48 UTC (permalink / raw)
To: pgsql-performance
> On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies <johann.spies@gmail.com> wrote:
>>
>> On 4 April 2017 at 14:07, Johann Spies <johann.spies@gmail.com> wrote:
>>
>> > Why would that be?
>>
>> To answer my own question. After experimenting a lot we found that
>> 9.6 uses a parallel seqscan that is actually a lot faster than using
>> the index on these large tables.
Further experimenting resulted in a solution which we do not understand:
The table 'publication' had the field 'ut' as primary key and the ut
index was not used.
So we built an additional btree index(ut) on publication - which was
ignored as well.
Then we built a gin index(ut) on publication and now it is being used.
The same happened on the other table (belongs_to) where the btree
index was ignored by the planner but the gin-index used.
As a result our deletes runs between 25-60 times faster than earlier
with maximum of about 200000 records per hour in comparison with a
maximum of 4500 earlier..
In the case of both tables the ut has a foreign key reference to
another article.
Why would the planner prefer the use the gin index and not the btree
index in this case?
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
--
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] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 11:15 ` Re: Delete, foreign key, index usage Rick Otten <rottenwindfish@gmail.com>
2017-04-24 06:48 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
@ 2017-04-24 13:17 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2017-04-25 06:28 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tomas Vondra @ 2017-04-24 13:17 UTC (permalink / raw)
To: pgsql-performance
On 04/24/2017 08:48 AM, Johann Spies wrote:
>
> Why would the planner prefer the use the gin index and not the btree
> index in this case?
>
You'll need to show what queries are you running - that's a quite
important piece of information, and I don't see it anywhere in this
thread. Seeing explain plans would also be helpful.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
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] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 11:15 ` Re: Delete, foreign key, index usage Rick Otten <rottenwindfish@gmail.com>
2017-04-24 06:48 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-24 13:17 ` Re: Delete, foreign key, index usage Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2017-04-25 06:28 ` Johann Spies <johann.spies@gmail.com>
2017-04-25 07:34 ` Re: Delete, foreign key, index usage David Rowley <david.rowley@2ndquadrant.com>
2017-04-25 23:35 ` Re: Delete, foreign key, index usage Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 9+ messages in thread
From: Johann Spies @ 2017-04-25 06:28 UTC (permalink / raw)
To: pgsql-performance
On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
> On 04/24/2017 08:48 AM, Johann Spies wrote:
>>
>>
>> Why would the planner prefer the use the gin index and not the btree
>> index in this case?
>>
>
> You'll need to show what queries are you running - that's a quite important
> piece of information, and I don't see it anywhere in this thread. Seeing
> explain plans would also be helpful.
It is a simple "delete from wos_2017_1.article;" which causes a domino
effect deletes due to foreign keys. In the case of one table with more
than 50 million records where the primary key was also the foreign
key, the process only started to use the index when we built a gin
index. In the case of the "belongs_to" table (shown in my first
email) we first built a btree index on the foreign key - and it was
ignored. Only after the gin index was created did it use the index.
Regards.
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
--
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] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 11:15 ` Re: Delete, foreign key, index usage Rick Otten <rottenwindfish@gmail.com>
2017-04-24 06:48 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-24 13:17 ` Re: Delete, foreign key, index usage Tomas Vondra <tomas.vondra@2ndquadrant.com>
2017-04-25 06:28 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
@ 2017-04-25 07:34 ` David Rowley <david.rowley@2ndquadrant.com>
1 sibling, 0 replies; 9+ messages in thread
From: David Rowley @ 2017-04-25 07:34 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; +Cc: pgsql-performance
On 25 April 2017 at 18:28, Johann Spies <johann.spies@gmail.com> wrote:
> On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>> On 04/24/2017 08:48 AM, Johann Spies wrote:
>>>
>>>
>>> Why would the planner prefer the use the gin index and not the btree
>>> index in this case?
>>>
>>
>> You'll need to show what queries are you running - that's a quite important
>> piece of information, and I don't see it anywhere in this thread. Seeing
>> explain plans would also be helpful.
>
> It is a simple "delete from wos_2017_1.article;" which causes a domino
> effect deletes due to foreign keys. In the case of one table with more
> than 50 million records where the primary key was also the foreign
> key, the process only started to use the index when we built a gin
> index. In the case of the "belongs_to" table (shown in my first
> email) we first built a btree index on the foreign key - and it was
> ignored. Only after the gin index was created did it use the index.
Some suggestions:
(It's a good idea to CC the person you're replying to so that they're
more likely to notice the email)
psql's \d output for the referenced and referencing table would be a
good thing to show too.
This would confirm to us things like;
* you've got the indexes defined correctly
* there's nothing weird like the indexes are on some other tablesspace
with some other random_page_cost defined on it which is causing them
not to ever be preferred.
* you've actually got indexes
Also, you might like to try to EXPLAIN DELETE FROM wos_2017_1.article
WHERE ut = '<some constant>'; to see if the planner makes use of the
index for that. If that's not choosing the index then it might be an
easier issue to debug.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
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] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 11:15 ` Re: Delete, foreign key, index usage Rick Otten <rottenwindfish@gmail.com>
2017-04-24 06:48 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-24 13:17 ` Re: Delete, foreign key, index usage Tomas Vondra <tomas.vondra@2ndquadrant.com>
2017-04-25 06:28 ` Re: Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
@ 2017-04-25 23:35 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tomas Vondra @ 2017-04-25 23:35 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; pgsql-performance
On 04/25/2017 08:28 AM, Johann Spies wrote:
> On 24 April 2017 at 15:17, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>> On 04/24/2017 08:48 AM, Johann Spies wrote:
>>>
>>>
>>> Why would the planner prefer the use the gin index and not the btree
>>> index in this case?
>>>
>>
>> You'll need to show what queries are you running - that's a quite important
>> piece of information, and I don't see it anywhere in this thread. Seeing
>> explain plans would also be helpful.
>
> It is a simple "delete from wos_2017_1.article;" which causes a domino
> effect deletes due to foreign keys. In the case of one table with more
> than 50 million records where the primary key was also the foreign
> key, the process only started to use the index when we built a gin
> index. In the case of the "belongs_to" table (shown in my first
> email) we first built a btree index on the foreign key - and it was
> ignored. Only after the gin index was created did it use the index.
>
> Regards.
> Johann
Wouldn't it be easier to simply show the queries (with the exact
condition) and the associated explain plans? I understand you're doing
your best to explain what's happening, but the explain plans contain a
lot of information that you might have missed.
I suppose you actually did explain analyze to verify the query was not
using the btree index and then started using the gin index. Or how did
you verify that?
Also, which PostgreSQL version have you observed this on? I see you've
mentioned 9.6 when talking about parallel scans, but I suppose the issue
was originally observed on some older version.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
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] 9+ messages in thread
* Re: Delete, foreign key, index usage
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
@ 2017-05-25 12:21 ` Johann Spies <johann.spies@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Johann Spies @ 2017-05-25 12:21 UTC (permalink / raw)
To: pgsql-performance
On 4 April 2017 at 14:07, Johann Spies <johann.spies@gmail.com> wrote:
> While updating our database which includes a lot of deletions where a lot
> of foreign key references are involved we found that in the case of two
> tables the indexes are ignored and it slow down the process a lot.
> ...
>
>
> Why would that be?
>
After a long time we found the problem: The primary/foreign key fields had
different types: varchar and citext. In the case of the two tables where
the indexes were ignored indexes were built with the 'citext' type and the
queries assumed it was varchar as the case were in the other tables using
the same field.
Lesson learnt: Check your types in every field in every table - and we
have many tables.
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2017-05-25 12:21 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-04 12:07 Delete, foreign key, index usage Johann Spies <johann.spies@gmail.com>
2017-04-05 10:40 ` Johann Spies <johann.spies@gmail.com>
2017-04-05 11:15 ` Rick Otten <rottenwindfish@gmail.com>
2017-04-24 06:48 ` Johann Spies <johann.spies@gmail.com>
2017-04-24 13:17 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2017-04-25 06:28 ` Johann Spies <johann.spies@gmail.com>
2017-04-25 07:34 ` David Rowley <david.rowley@2ndquadrant.com>
2017-04-25 23:35 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2017-05-25 12:21 ` Johann Spies <johann.spies@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