6 ms·

Speedup of deletes on PostgreSQL

93 points by Ianvdl 2y ago

EdSchouten 2y ago
Out of curiosity, would placing all DELETE queries within a single transaction also help? Or does that still cause PostgreSQL to process each of the queries sequentially?
- londons_explore 2y ago
  seperate queries within a transaction aren't optimized together. It wouldn't help (apart from possibly some caching benefits).
- peter_l_downs 2y ago
  No — most constraint checks are by default deferred until the end of the transaction, but you'd still need to check them, and without an index you'll still have to do a large scan. See https://www.postgresql.org/docs/17/sql-set-constraints.html https://www.postgresql.org/docs/17/sql-set-constraints.html for more information regarding constraint checking.
mypalmike 2y ago
I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.
- hn_throwaway_99 2y ago
  I'm surprised there doesn't seem to be more consensus on this issue, but I guess it's because changing the default after the fact would be backwards incompatible. I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.
  - RegnisGnaw 2y ago
    I haven't touched MySQL since the 5.7 says, but I don't think it does. I remember back the creation of the FK will fail if there is no index, but it doesn't create them.
    - evanelias 2y ago
      In MySQL, the behavior differs between the parent and child sides of the FK. It will auto-create the index on the child side if one is missing. But the parent (referenced) side must already have an appropriate index on the referenced columns. Prior to MySQL 8.4, it was sufficient for the parent table to have any index that begins with the referenced columns. This has become stricter in 8.4 with default settings, to now require a UNIQUE index with the exact referenced columns.
    - hu3 2y ago
      https://dev.mysql.com/doc/refman/5.7/en/constraint-foreign-key.html https://dev.mysql.com/doc/refman/5.7/en/constraint-foreign-k... Documentation for 5.7 says it does create indexes for FKs automatically if one isn't created. > MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.
hn_throwaway_99 2y ago
I had problems with the "no foreign key indexes by default" issue, and as much as I love Postgres I think this is an unfortunate foot gun. I think it would be much better to create indexes for foreign keys by default, and then allow skipping index creation with something like a `NO INDEX` clause if explicitly desired.
- peterldowns 2y ago
  I agree with you. What I've done in the past (and continue to do with new projects) is write a database-backed test that: - applies migrations - parses the resulting schema - finds all foreign key references: ref{tableA columnA -> tableB columnB} - finds all the indexes: index{tableA columnA [columnB...]} - checks that there is an explicit index for every reference column: index{tableA columnA} must exist So basically, by default when you add a new foreign key, the tests fail until you either explicitly add an exception OR create the necessary index. Easy. This strategy is also really nice for linting other relational properties in your database. For instance, for GDPR/CCPA/correctness purposes, you probably want to prevent deletion of certain rows unless it's done by specialized code with an audit log. These kinds of lints can check to make sure that there are no ON DELETE CASCADE foreign keys from those tables that would result in a surprise deletion. You can also check to make sure that foreign keys are either ON DELETE CASCADE, ON DELETE SET NULL, or explicitly covered by custom deletion code.
- ltbarcly3 2y ago
  Not a footgun, it is well thought out and better than what you propose, and less surprising. (edited to be less insulting) What makes you think that Postgres automatically making an arbitrary number of indexes on an arbitrary number of tables that you aren't trying to modify, that might be extremely bad for overall performance or take weeks to create, will save you from the rest of the things you haven't bothered to learn?
  - hu3 2y ago
    I'll have to defend your parent commenter on this one. Not having indexes for FKs is on average much worse for overall performance. Defaults should be reasonable. In the great majority of cases you WANT to have indexes in FKs. > expect the universe to magically fix all of your mistakes This kind of derogatory hyperbole is not necessary nor productive. I should expect tools to help me avoid mistakes. Not having an index on FKs is, more often than not, a mistake. It is reasonable to expect PostgreSQL to help me here.
saisrirampur 2y ago
Nice post! Love the unique insight here. TL;DR Create indexes on the foreign key columns of the table on which the foreign keys are defined.
SoftTalker 2y ago
This is DBA 101 stuff. If a database is part of your software, you really need someone on the team who knows how it works.
- ltbarcly3 2y ago
  100% this, however my first boss shared some wisdom with me: "The kind of people who recognize the value of expertise don't need to be told to look for it, the kind of people who don't recognize it can't be told anything."
- AnotherGoodName 2y ago
  I've witnessed companies react with delight at the results after spending millions on consultants when all the consultants did was a few hours of explain query and create index. Not my problem when I see it, way to go consultants charging millions, but it's amazing how poorly big companies are run that this seriously happens. You use a database? You have someone who knows how to add indexes right? Right?!
- DaiPlusPlus 2y ago
  Yes, but consider the marketing, uh… I mean, messaging coming from the NoSQL and DBaaS (CosmosDb, DynamoDb, etc) and Firebase crowd: “databases are hard, let us manage it all for you” - and they’ve got a point: it’s 2024 now, we arguably shouldn’t need to handle those kinds of non-functional requirements by ourselves: a DB engine should be able to automatically infer the necessary indexes from the schema design, and automatically rebuild them asynchronously - if Google can search the web in under a second, then your RDBMS should have no problem querying your data in a fraction of the time. …which I imagine is the impression made to a lot of (even most?) people who got started writing software-that-uses-a-database within the past decade. If you’re using NodeJS then using a KV-store library feels far more natural than writing SQL in a string. At least the kids are using parameters now, so it’s not like how every PHP+MySQL site was vulnerable to injection attacks… (I know that recently RDBMS now do implement automatic indexes based on runtime query-profiling, which is great, but it isn’t pre-emptive, and often gets it wrong too) ———- Also, who calls themselves a “DBA” anymore? That word makes me think of a pipe-and-suspenders type, still employed well-past retirement age because they’re the only ones who knows how to keep the company’s Big Iron (…or AS/400) database from keeling over. Thesedays it’s all “Ops” - “DevOps”, “SysOps”, …”DatabaseOps”?
ltbarcly3 2y ago
Find missing indexes, return SQL to create them. SELECT CONCAT('CREATE INDEX ', relname, '_', conname, '_ix ON ', nspname, '.', relname, ' ', regexp_replace( regexp_replace(pg_get_constraintdef(pg_constraint.oid, true), ' REFERENCES.*$','',''), 'FOREIGN KEY ','',''), ';') AS query FROM pg_constraint JOIN pg_class ON (conrelid = pg_class.oid) JOIN pg_namespace ON (relnamespace = pg_namespace.oid) WHERE contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_index WHERE indrelid = conrelid AND conkey::int[] @> indkey::int[] AND indkey::int[] @> conkey::int[]);
- netcraft 2y ago
  youve got an extra escape in there, probably an artifact of HN or something, its showing as *
  - ltbarcly3 2y ago
    thank you, fixed
- heuristic81 2y ago
  This seems to have a few false positives. Multi-column indexes that have the first column as the foreign key work as well as a single column index. For more modern postgres, partial indexes with `WHERE column_name IS NOT NULL` on columns that can be null are also valid and more performant. Here's what we use in CI to check for missing indexes: -- Unindexed FK -- Missing indexes - For CI WITH y AS ( SELECT pg_catalog.format('%I', c1.relname) AS referencing_tbl, pg_catalog.quote_ident(a1.attname) AS referencing_column, (SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement FROM pg_catalog.pg_constraint t JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1] JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1] WHERE t.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_catalog.pg_index i WHERE i.indrelid = t.conrelid AND i.indkey[0] = t.conkey[1] AND indpred IS NULL ) ) SELECT referencing_tbl || '.' || referencing_column as column FROM y WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)')) ORDER BY 1; Additionally I have this to specify the index creation commands (CONCURRENTLY is recommended for existing tables in production as it doesn't cause locking): -- Unindexed FK -- Missing indexes - Show Create Syntax WITH y AS ( SELECT pg_catalog.format('%I.%I', n1.nspname, c1.relname) AS referencing_tbl, pg_catalog.quote_ident(a1.attname) AS referencing_column, (SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement, t1.typname AS referencing_type, t.conname AS existing_fk_on_referencing_tbl, pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl, pg_catalog.quote_ident(a2.attname) AS referenced_column, t2.typname AS referenced_type, pg_relation_size( pg_catalog.format('%I.%I', n1.nspname, c1.relname) ) AS referencing_tbl_bytes, pg_relation_size( pg_catalog.format('%I.%I', n2.nspname, c2.relname) ) AS referenced_tbl_bytes, pg_catalog.format($$CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %s%I(%I)%s;$$, c1.relname || '_' || a1.attname || 'x' , CASE WHEN n1.nspname = 'public' THEN '' ELSE n1.nspname || '.' END, c1.relname, a1.attname, CASE WHEN a1.attnotnull THEN '' ELSE ' WHERE ' || a1.attname || ' IS NOT NULL' END) AS suggestion FROM pg_catalog.pg_constraint t JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1] JOIN pg_catalog.pg_type t1 ON a1.atttypid = t1.oid JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1] JOIN pg_catalog.pg_type t2 ON a2.atttypid = t2.oid WHERE t.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_catalog.pg_index i WHERE i.indrelid = t.conrelid AND i.indkey[0] = t.conkey[1] AND i.indpred IS NULL ) ) SELECT referencing_tbl, referencing_column, existing_fk_on_referencing_tbl, referenced_tbl, referenced_column, pg_size_pretty(referencing_tbl_bytes) AS referencing_tbl_size, pg_size_pretty(referenced_tbl_bytes) AS referenced_tbl_size, suggestion FROM y WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)')) ORDER BY referencing_tbl_bytes DESC, referenced_tbl_bytes DESC, referencing_tbl, referenced_tbl, referencing_column, referenced_column;
mbb70 2y ago
Similar to 'If an article title poses a question, the answer is no', if an article promises a significant speedup of a database query, an index was added.
chasil 2y ago
Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle. This problem is common. "Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one. "The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table: a) If I update the parent table’s primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. b) If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well... "So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met: a) You do not delete from the parent table. b) You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools! c) You do not join from the parent to the child (like DEPT to EMP). If you satisfy all three above, feel free to skip the index – it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ‘over-lock’ data." -Tom Kyte, Expert One-on-One Oracle First Edition, 2005.
- trollied 2y ago
  Tom's great. Ask Tom taught me LOADS about Oracle back in the day, and I still have the book you reference.
  - chasil 2y ago
    This is the second edition, above deadlock discussion on page 211. https://javidhasanov.wordpress.com/wp-content/uploads/2012/01/expert-oracle-database-architecture.pdf https://javidhasanov.wordpress.com/wp-content/uploads/2012/0... Kyte said to search for "expert oracle database architecture pdf" to find these versions. https://asktom.oracle.com/ords/asktom.search?tag=tom-kytes-books https://asktom.oracle.com/ords/asktom.search?tag=tom-kytes-b...
- 2y ago
dagss 2y ago
What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple. In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned something. Would be so much easier to newcomers... instead of a giant footgun and obscure lore that only becomes evident after you have a oot of data in production. Google Data Store does this, just assumes that _of course_ you did not look to do a full table scan. Works great. Not SQL, but no reason popular SQL DBs could not have an option to have query planners throw errors at certain points instead of always making a plan no matter how bad. SQL has a reputation for a steep learning curve and I blame this single thing -- that you get a poor plan instead of an error -- a lot for it.
- John23832 2y ago
  > What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. You can run ANALYSE on Postgres.
  - dagss 2y ago
    I am an MS SQL user and didn't touch postgres but can I assume analyse is a tool for displaying the chosen query plan for a query? If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans. But...probably some tooling could be made to do such analysis automatically and throw similar errors... Does statistics ever cause query plans to suddenly change on postgres? In MS SQL you would also need to pin the plan / disable statistics on tables...
    - arp242 2y ago
      Analyze collects statistics the query planner uses to determine the query plan. It can change the resulting plan, yes. Production databases using different query plans sure is annoying and cause problems, but I'm not so sure whether returning errors is better. "Slow" beats "not working at all" in almost all cases. The typical case it will select a different query plan once the data grows, which is not so straight-forward to test for, especially since the hardware of your production may have quite different performance characteristics. Pinning the plan is temping, but has the downside you risk running a bad plan because what works well for your 100k test rows may not work equally well for your 1b actual rows, and testing all of that is again tricky. That's not really a brilliant either, and may also make your application slow. Just keeping an eye on slow query logs and/or query performance statistics is the general approach. I don't think it's really possible to improve on that without making some pretty serious trade-offs in other areas.
hakanderyal 2y ago
Coming across that early in my freelancing career, I create indexes on foreign keys by default now via auto-configuring it with ORM. It's almost always needed, and it's easier to remove them if they somehow become a problem.
3pm 2y ago
Obligatory MySQL Big Deletes: https://mysql.rjweb.org/doc.php/deletebig https://mysql.rjweb.org/doc.php/deletebig

93 points by Ianvdl 2y ago

Out of curiosity, would placing all DELETE queries within a single transaction also help? Or does that still cause PostgreSQL to process each of the queries sequentially?

londons_explore 2y ago
seperate queries within a transaction aren't optimized together. It wouldn't help (apart from possibly some caching benefits).
peter_l_downs 2y ago
No — most constraint checks are by default deferred until the end of the transaction, but you'd still need to check them, and without an index you'll still have to do a large scan. See https://www.postgresql.org/docs/17/sql-set-constraints.html https://www.postgresql.org/docs/17/sql-set-constraints.html for more information regarding constraint checking.

mypalmike 2y ago

I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.

hn_throwaway_99 2y ago
I'm surprised there doesn't seem to be more consensus on this issue, but I guess it's because changing the default after the fact would be backwards incompatible. I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.
- RegnisGnaw 2y ago
  I haven't touched MySQL since the 5.7 says, but I don't think it does. I remember back the creation of the FK will fail if there is no index, but it doesn't create them.
  - evanelias 2y ago
    In MySQL, the behavior differs between the parent and child sides of the FK. It will auto-create the index on the child side if one is missing. But the parent (referenced) side must already have an appropriate index on the referenced columns. Prior to MySQL 8.4, it was sufficient for the parent table to have any index that begins with the referenced columns. This has become stricter in 8.4 with default settings, to now require a UNIQUE index with the exact referenced columns.
  - hu3 2y ago
    https://dev.mysql.com/doc/refman/5.7/en/constraint-foreign-key.html https://dev.mysql.com/doc/refman/5.7/en/constraint-foreign-k... Documentation for 5.7 says it does create indexes for FKs automatically if one isn't created. > MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

hn_throwaway_99 2y ago

I had problems with the "no foreign key indexes by default" issue, and as much as I love Postgres I think this is an unfortunate foot gun. I think it would be much better to create indexes for foreign keys by default, and then allow skipping index creation with something like a `NO INDEX` clause if explicitly desired.

peterldowns 2y ago
I agree with you. What I've done in the past (and continue to do with new projects) is write a database-backed test that: - applies migrations - parses the resulting schema - finds all foreign key references: ref{tableA columnA -> tableB columnB} - finds all the indexes: index{tableA columnA [columnB...]} - checks that there is an explicit index for every reference column: index{tableA columnA} must exist So basically, by default when you add a new foreign key, the tests fail until you either explicitly add an exception OR create the necessary index. Easy. This strategy is also really nice for linting other relational properties in your database. For instance, for GDPR/CCPA/correctness purposes, you probably want to prevent deletion of certain rows unless it's done by specialized code with an audit log. These kinds of lints can check to make sure that there are no ON DELETE CASCADE foreign keys from those tables that would result in a surprise deletion. You can also check to make sure that foreign keys are either ON DELETE CASCADE, ON DELETE SET NULL, or explicitly covered by custom deletion code.
ltbarcly3 2y ago
Not a footgun, it is well thought out and better than what you propose, and less surprising. (edited to be less insulting) What makes you think that Postgres automatically making an arbitrary number of indexes on an arbitrary number of tables that you aren't trying to modify, that might be extremely bad for overall performance or take weeks to create, will save you from the rest of the things you haven't bothered to learn?
- hu3 2y ago
  I'll have to defend your parent commenter on this one. Not having indexes for FKs is on average much worse for overall performance. Defaults should be reasonable. In the great majority of cases you WANT to have indexes in FKs. > expect the universe to magically fix all of your mistakes This kind of derogatory hyperbole is not necessary nor productive. I should expect tools to help me avoid mistakes. Not having an index on FKs is, more often than not, a mistake. It is reasonable to expect PostgreSQL to help me here.

saisrirampur 2y ago

Nice post! Love the unique insight here. TL;DR Create indexes on the foreign key columns of the table on which the foreign keys are defined.

SoftTalker 2y ago

This is DBA 101 stuff. If a database is part of your software, you really need someone on the team who knows how it works.

ltbarcly3 2y ago
100% this, however my first boss shared some wisdom with me: "The kind of people who recognize the value of expertise don't need to be told to look for it, the kind of people who don't recognize it can't be told anything."
AnotherGoodName 2y ago
I've witnessed companies react with delight at the results after spending millions on consultants when all the consultants did was a few hours of explain query and create index. Not my problem when I see it, way to go consultants charging millions, but it's amazing how poorly big companies are run that this seriously happens. You use a database? You have someone who knows how to add indexes right? Right?!
DaiPlusPlus 2y ago
Yes, but consider the marketing, uh… I mean, messaging coming from the NoSQL and DBaaS (CosmosDb, DynamoDb, etc) and Firebase crowd: “databases are hard, let us manage it all for you” - and they’ve got a point: it’s 2024 now, we arguably shouldn’t need to handle those kinds of non-functional requirements by ourselves: a DB engine should be able to automatically infer the necessary indexes from the schema design, and automatically rebuild them asynchronously - if Google can search the web in under a second, then your RDBMS should have no problem querying your data in a fraction of the time. …which I imagine is the impression made to a lot of (even most?) people who got started writing software-that-uses-a-database within the past decade. If you’re using NodeJS then using a KV-store library feels far more natural than writing SQL in a string. At least the kids are using parameters now, so it’s not like how every PHP+MySQL site was vulnerable to injection attacks… (I know that recently RDBMS now do implement automatic indexes based on runtime query-profiling, which is great, but it isn’t pre-emptive, and often gets it wrong too) ———- Also, who calls themselves a “DBA” anymore? That word makes me think of a pipe-and-suspenders type, still employed well-past retirement age because they’re the only ones who knows how to keep the company’s Big Iron (…or AS/400) database from keeling over. Thesedays it’s all “Ops” - “DevOps”, “SysOps”, …”DatabaseOps”?

ltbarcly3 2y ago

Find missing indexes, return SQL to create them. SELECT CONCAT('CREATE INDEX ', relname, '_', conname, '_ix ON ', nspname, '.', relname, ' ', regexp_replace( regexp_replace(pg_get_constraintdef(pg_constraint.oid, true), ' REFERENCES.*$','',''), 'FOREIGN KEY ','',''), ';') AS query FROM pg_constraint JOIN pg_class ON (conrelid = pg_class.oid) JOIN pg_namespace ON (relnamespace = pg_namespace.oid) WHERE contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_index WHERE indrelid = conrelid AND conkey::int[] @> indkey::int[] AND indkey::int[] @> conkey::int[]);

netcraft 2y ago
youve got an extra escape in there, probably an artifact of HN or something, its showing as *
- ltbarcly3 2y ago
  thank you, fixed
heuristic81 2y ago
This seems to have a few false positives. Multi-column indexes that have the first column as the foreign key work as well as a single column index. For more modern postgres, partial indexes with `WHERE column_name IS NOT NULL` on columns that can be null are also valid and more performant. Here's what we use in CI to check for missing indexes: -- Unindexed FK -- Missing indexes - For CI WITH y AS ( SELECT pg_catalog.format('%I', c1.relname) AS referencing_tbl, pg_catalog.quote_ident(a1.attname) AS referencing_column, (SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement FROM pg_catalog.pg_constraint t JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1] JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1] WHERE t.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_catalog.pg_index i WHERE i.indrelid = t.conrelid AND i.indkey[0] = t.conkey[1] AND indpred IS NULL ) ) SELECT referencing_tbl || '.' || referencing_column as column FROM y WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)')) ORDER BY 1; Additionally I have this to specify the index creation commands (CONCURRENTLY is recommended for existing tables in production as it doesn't cause locking): -- Unindexed FK -- Missing indexes - Show Create Syntax WITH y AS ( SELECT pg_catalog.format('%I.%I', n1.nspname, c1.relname) AS referencing_tbl, pg_catalog.quote_ident(a1.attname) AS referencing_column, (SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement, t1.typname AS referencing_type, t.conname AS existing_fk_on_referencing_tbl, pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl, pg_catalog.quote_ident(a2.attname) AS referenced_column, t2.typname AS referenced_type, pg_relation_size( pg_catalog.format('%I.%I', n1.nspname, c1.relname) ) AS referencing_tbl_bytes, pg_relation_size( pg_catalog.format('%I.%I', n2.nspname, c2.relname) ) AS referenced_tbl_bytes, pg_catalog.format($$CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %s%I(%I)%s;$$, c1.relname || '_' || a1.attname || 'x' , CASE WHEN n1.nspname = 'public' THEN '' ELSE n1.nspname || '.' END, c1.relname, a1.attname, CASE WHEN a1.attnotnull THEN '' ELSE ' WHERE ' || a1.attname || ' IS NOT NULL' END) AS suggestion FROM pg_catalog.pg_constraint t JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1] JOIN pg_catalog.pg_type t1 ON a1.atttypid = t1.oid JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1] JOIN pg_catalog.pg_type t2 ON a2.atttypid = t2.oid WHERE t.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_catalog.pg_index i WHERE i.indrelid = t.conrelid AND i.indkey[0] = t.conkey[1] AND i.indpred IS NULL ) ) SELECT referencing_tbl, referencing_column, existing_fk_on_referencing_tbl, referenced_tbl, referenced_column, pg_size_pretty(referencing_tbl_bytes) AS referencing_tbl_size, pg_size_pretty(referenced_tbl_bytes) AS referenced_tbl_size, suggestion FROM y WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)')) ORDER BY referencing_tbl_bytes DESC, referenced_tbl_bytes DESC, referencing_tbl, referenced_tbl, referencing_column, referenced_column;

mbb70 2y ago

Similar to 'If an article title poses a question, the answer is no', if an article promises a significant speedup of a database query, an index was added.

chasil 2y ago

Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle. This problem is common. "Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one. "The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table: a) If I update the parent table’s primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. b) If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well... "So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met: a) You do not delete from the parent table. b) You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools! c) You do not join from the parent to the child (like DEPT to EMP). If you satisfy all three above, feel free to skip the index – it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ‘over-lock’ data." -Tom Kyte, Expert One-on-One Oracle First Edition, 2005.

trollied 2y ago
Tom's great. Ask Tom taught me LOADS about Oracle back in the day, and I still have the book you reference.
- chasil 2y ago
  This is the second edition, above deadlock discussion on page 211. https://javidhasanov.wordpress.com/wp-content/uploads/2012/01/expert-oracle-database-architecture.pdf https://javidhasanov.wordpress.com/wp-content/uploads/2012/0... Kyte said to search for "expert oracle database architecture pdf" to find these versions. https://asktom.oracle.com/ords/asktom.search?tag=tom-kytes-books https://asktom.oracle.com/ords/asktom.search?tag=tom-kytes-b...
2y ago

dagss 2y ago

What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple. In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned something. Would be so much easier to newcomers... instead of a giant footgun and obscure lore that only becomes evident after you have a oot of data in production. Google Data Store does this, just assumes that _of course_ you did not look to do a full table scan. Works great. Not SQL, but no reason popular SQL DBs could not have an option to have query planners throw errors at certain points instead of always making a plan no matter how bad. SQL has a reputation for a steep learning curve and I blame this single thing -- that you get a poor plan instead of an error -- a lot for it.

John23832 2y ago
> What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. You can run ANALYSE on Postgres.
- dagss 2y ago
  I am an MS SQL user and didn't touch postgres but can I assume analyse is a tool for displaying the chosen query plan for a query? If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans. But...probably some tooling could be made to do such analysis automatically and throw similar errors... Does statistics ever cause query plans to suddenly change on postgres? In MS SQL you would also need to pin the plan / disable statistics on tables...
  - arp242 2y ago
    Analyze collects statistics the query planner uses to determine the query plan. It can change the resulting plan, yes. Production databases using different query plans sure is annoying and cause problems, but I'm not so sure whether returning errors is better. "Slow" beats "not working at all" in almost all cases. The typical case it will select a different query plan once the data grows, which is not so straight-forward to test for, especially since the hardware of your production may have quite different performance characteristics. Pinning the plan is temping, but has the downside you risk running a bad plan because what works well for your 100k test rows may not work equally well for your 1b actual rows, and testing all of that is again tricky. That's not really a brilliant either, and may also make your application slow. Just keeping an eye on slow query logs and/or query performance statistics is the general approach. I don't think it's really possible to improve on that without making some pretty serious trade-offs in other areas.

hakanderyal 2y ago

Coming across that early in my freelancing career, I create indexes on foreign keys by default now via auto-configuring it with ORM. It's almost always needed, and it's easier to remove them if they somehow become a problem.

3pm 2y ago

Obligatory MySQL Big Deletes: https://mysql.rjweb.org/doc.php/deletebig https://mysql.rjweb.org/doc.php/deletebig