Re: [PERFORM] Delete, foreign key, index usage
On 24 April 2017 at 15:17, Tomas Vondra 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
[PERFORM] Questionaire: Common WAL write rates on busy servers.
Hi, I've lately seen more and more installations where the generation of write-ahead-log (WAL) is one of the primary bottlenecks. I'm curious whether that's primarily a "sampling error" of mine, or whether that's indeed more common. The primary reason I'm curious is that I'm pondering a few potential optimizations, and would like to have some guidance which are more and which are less important. Questions (answer as many you can comfortably answer): - How many MB/s, segments/s do you see on busier servers? - What generates the bulk of WAL on your servers (9.5+ can use pg_xlogdump --stats to compute that)? - Are you seeing WAL writes being a bottleneck?OA - What kind of backup methods are you using and is the WAL volume a problem? - What kind of replication are you using and is the WAL volume a problem? - What are your settings for wal_compression, max_wal_size (9.5+) / checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers? - Could you quickly describe your workload? Feel free to add any information you think is pertinent ;) Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Delete, foreign key, index usage
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
