0

I'm trying to diagnose a high traffic and problematic table(it has an abysmal cache hit rate 0.035)

Given the following sentence from AWS RDS docs:

The tup_returned metric shows the number of rows read. The tup_fetched metric shows the number of rows returned to the client. If tup_returned is significantly larger than tup_fetched, the data might not be properly indexed. Also, your table statistics might not be current.

Our table statistics are up to date.

The following metrics are from a high read event:

tup_returned = 13_432_903.37
tup_fetched =     258_434.83

> tup_returned/tup_fetched
=> 51.977914006405406

This table has an index for almost every column. Is there such a thing as over indexing? Not properly indexed means we're using btree, but should be using gin etc, is this a correct statement? There are materialized views on this table because performance was so bad.

0

1 Answer 1

2

This table has an index for almost every column

This doesn't mean any of them are being used.

is there such a thing as over indexing?

There certainly is, but it is not the problem in your case.

Not properly indexed means we're using btree but should be using gin etc, is this a correct statement?

No, it's not. The statement means that the optimiser often does not find a useful index and has to scan the heap (table), then throw away the majority of rows because the don't match the query conditions.

tup_returned and tup_fetched are database-wide metrics. You need to identify the specific queries that cause the problem. From the pg_stat_user_tables you can obtain seq_scan and seq_tup_read, as well as idx_scan and idx_tup_fetch to find the tables from which a large portion of rows is frequently retrieved. Similarly, high values in rows and shared_blks_read of pg_stat_statements will let you find the queries with high I/O.

You will then use EXPLAIN (ANALYZE, BUFFERS) with the worst queries to determine why they are so bad.

Once you have that information, you can post a question here, following this advice, if you need help with any particular query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.