We considered table partitioning to reduce the time necessary for index scans on Marmotta's very large triples table; but found that partitioning the triples table is impossible because of foreign-key constraints upon it, especially from the versioning-related tables, and we don't want to disable versioning.

With the creation of an extra index on triples and the adjustment of some statistics-related storage parameters, we were able to keep queries from doing full table scans of triples, which was great, but now even index scans take longer than we'd like because of the sheer size of the table's indices.

As of 2015-09-18, the triples table has 294M rows. The table occupies 32GB and its two most-significant indices, for the analysis we've been doing, are each 13GB (idx_triples_c, which we had to create, and idx_triples_cspo).

We thought that having a partitioned table could result in shorter index scans, on indices somewhat smaller than multiple gigabytes. Unfortunately, foreign keys onto a partitioned table will not work, and therein lies our problem, because there are four tables that have foreign keys with "REFERENCES triples(id)" constraints: reasoner_just_supp_triples, reasoner_justifications, versions_added, and versions_removed.

That said, it's possible that the behavior of foreign keys could be changed in some future version of PostgreSQL (according to the PostgreSQL documentation, which reads "These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application"), but there doesn't appear to be any roadmap for that.

Browser not supported