PostgreSQL Performace on "metadatavalue"

[Stephen Brush]

We have a large (I think) DSpace DB with a metadatavalue row count of ~10 million rows.

I am seeing some performance issues related to search results as each Item lookup in the metadatavalue table is taking ~800ms. I noticed in the explain plan that its using a Parallel Sequential Scan, which is a parallized Full table scan.

We have a Dev environment of ~500000 records where the same query still uses the indexes on the tables and returns in ~150ms (on lesser hardware).

I've tried several things in postgres to get it to use the index, like running ANALYZE, setting "enable_setscan=false", setting the random_page_cost to 1.1.

Since postgres doesn't have query hints I'm not sure where to go -- I would like to at least be able to verify if the hitting the index would improve performance.

Any ideas?

Steve