PostgreSQL – How to Discover Missing Foreign Keys and Indexes

database-designforeign keyindexpostgresql

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes.

I'd like to know if there is some automated way of discovering such potential missing keys/indexes using some kind of log parsing or something alike.

Best Answer

I remembered seeing a query sometime ago in a post so quick searching resulted in:

CREATE FUNCTION pg_temp.sortarray(int2[]) returns int2[] as '
  SELECT ARRAY(
      SELECT $1[i]
        FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
    ORDER BY 1
  )
' language sql;

  SELECT conrelid::regclass
         ,conname
         ,reltuples::bigint
    FROM pg_constraint
         JOIN pg_class ON (conrelid = pg_class.oid)
   WHERE contype = 'f'
         AND NOT EXISTS (
           SELECT 1
             FROM pg_index
            WHERE indrelid = conrelid
                  AND pg_temp.sortarray(conkey) = pg_temp.sortarray(indkey)
         )
ORDER BY reltuples DESC
;

From http://mlawire.blogspot.nl/2009/08/postgresql-indexes-on-foreign-keys.html

That will give you what you want.

And also found https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql but I haven't used this myself.

Related Solutions

Postgresql 9 speeding up indexed inserts (JPA)

Deferred indexing would be nice, but isn't currently supported.

Adding indexes has a cost - write performance. They're a trade-off.

COPY won't help much if index maintenance is the main issue.

The simplest solution is to drop the indexes, and re-create them when you're done importing.

Since you can live with losing all your data if the DB crashes, you have a bunch of options to further improve performance, including:

Unlogged tables
fsync=off
Non-durable disk write caching on the drive that hosts the DB

The use of any of the above will eat your data if anything goes wrong. The last option might eat the file-system too.

I wrote more about this in https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing .

Mysql – Tablestructure for fast inserts/deletes with foreign keys

First, if your tables are InnoDB, and column c.P_ID is the reference of column p.P_ID (note actual column names may be different), then you absolutely should use foreign keys to avoid orphans, but know that you will have to explicitly state ON DELETE CASCADE, since the default is ON DELETE SET NULL, retaining orphans.

But this doesn't really have anything to do with the 'single query' to select the products and information about the competitors.

To achieve:

I want to achieve, that with only one query i got the product information from p an also the competitor ones from c where the product id is the same.

your query would use a join:

SELECT products.P_ID, products.product_name, competitors.info
 FROM products
 LEFT JOIN competitors ON competitors.P_ID=products.P_ID

This will get you repeat information for the products (competitor info will be different). And then you will use your application to parse that info into the array you need.

If you want a single row for the products, you can use the GROUP_CONCAT function for the competitors:

SELECT products.P_ID, products.product_name, 
  GROUP_CONCAT(competitors.info SEPARATOR ',') as competitor_info
 FROM products
 LEFT JOIN competitors ON competitors.P_ID=products.P_ID
 GROUP BY products.P_ID;

A major caveat you should be aware of: ON UPDATE|DELETE CASCADE does not fire triggers on the rows that were deleted/updated by the cascade:

Note

Currently, cascaded foreign key actions do not activate triggers.

This shouldn't stop you from using CASCADE if you want to remove orphan rows. But you should be aware of it.

Best Answer

Related Solutions

Postgresql 9 speeding up indexed inserts (JPA)

Mysql – Tablestructure for fast inserts/deletes with foreign keys

Related Question