Diagnosing missing index in postgres-CodePudding

Apologies in advance if this question is ill posed. I am no DB Administrator and at bit of a noob to all this.

I have a google cloud Postgres 13 database that is running very slowly. That underlying hardware should be more than sufficient for our requirements. I have executed the following query to try and understand what might be happening.

SELECT   relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, seq_tup_read / seq_scan 
FROM   pg_stat_user_tables 
WHERE   seq_scan > 0 
ORDER BY seq_tup_read DESC;

I confess to no understanding the output (some reading for me) however the issue is pretty critical as occurring on our production database so want to get a solution in place and then take the time to understanding it.

The query shows the following output (run in pgadmin)

Which apparently flags that there is probably a missing index on my sessions table. My table has 300k rows and the following column structure

You can see that we are using a uuid (CREATE EXTENSION IF NOT EXISTS "uuid-ossp";) as the primary key.

I have the following indexes currently in place

select tablename,indexname,tablespace,indexdef  from pg_indexes where tablename = 'sessions';

Do I still need an index on the primary key beyond the unique one? More generally, how can I tell the index that I need to create?

Thanks in advance!

CodePudding user response：

Do I still need an index on the primary key beyond the unique one?

The unique index is an index, which simply doesn't allow duplicate values. But it still has the properties of an index that allows the DB to quickly find a value or a range of values.

Primary keys always create unique keys (and also a NOT NULL constraint) for you.

It's hard to give advice for which column(s) to create index(es) without having some more info about the query you want to optimize.

CodePudding user response：

You picked a bad example, because this is a system view, for which you cannot create indexes.

In general, you use EXPLAIN to figure out how a query is executed and devise indexes based on that information.