0

I know how to debug a particular slow query with explain analyze (PostgreSQL).

If I received vague "DB is slow" feelings, I worked with this answer: https://stackoverflow.com/a/12818168/633961

In my case there are no sequence scans. I guess a combined index is missing.

Is there a tool which can help me to find missing combined indexes?

Other strategies which help to solve the goal are welcome, too.

Update

According to Julien Rouhaud this is fixed in PostgreSQL 10: https://groups.google.com/d/msg/powa-users/UmIEWvgJ2-k/sS5lfRE4CAAJ

  • Have you used 'pgAdmin' – Md Haidar Ali Khan Sep 25 '17 at 12:57
  • @MdHaidarAliKhan No, I never used it. What does PgAdmin provide to help me in this context? – guettli Sep 25 '17 at 13:06
  • ,The pgAdmin is a sort of client. You are able to manipulate schema and data on an instance or multiple instances of PostgreSQL engines. – Md Haidar Ali Khan Sep 25 '17 at 13:25
  • You could enable logging of slow queries or even the auto-explain module which automatically logs the execution plans of slow queries – a_horse_with_no_name Sep 25 '17 at 14:55
  • @a_horse_with_no_name yes, I could enable logging of slow queries. But this would overwhelm me. Is there a tool which could suggest combined indexes after processing the logs of slow queries? – guettli Sep 26 '17 at 7:38
1

To resolve overall DB slow, these are the things you can look into in Postgres:

1-

 -- Lock monitoring
 SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;


-- To find waiting queries
SELECT pid, datname, usename, now() - query_start AS runtime, wait_event,
wait_event_type, state, query FROM pg_stat_activity WHERE wait_event
is NOT NULL AND state = 'active';


-- To look at what all is running
select * from pg_stat_activity;


-- Once you have identified the session that is blocking you, you can kill it as below, make sure you don't kill someone's else work,
-- check with DBA if unsure (replace blocking_pid with pid that is blocking your session)


   SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_db'
  AND pid <> pg_backend_pid() and pid='blocking_pid';

2- Make sure autocommit is enabled in your client (This is especially an issue in DBeaver)

3- If there is no lock, let's generate an explain plan to see what is the cost of the query and places where we can improve. How to generate an explain plan

4- You might have to vacuum the tables if there have been large DML, it is also advisable to collect stats after any large DML, by default vacuum and stats collection happen automatically. (See link above for info n VACUUM, too).

5- Regarding your combined index, that will depend on the slow queries, there is no automated way to check that, you can however make sure that your foreign keys are indexed and that you can check using information_schema views.

| improve this answer | |
  • Thank you for these steps. In my case locks are not the problem. Vacuum is ok. Nr 5 is the next. Since I see no sequence scans (see link in question), I guess all relevant columns are indexed.... No automated way to detect missing combined indexes? What can a human expert do in this case, that a computer can't? – guettli Sep 26 '17 at 7:28
  • You can modify your parameters to log slow queries set log_min_duration_statement to an appropriate value e.g. 10000 (10 seconds), and tune the queries that get logged, you can see if combined indexes are missing for these queries. – virtual void Sep 28 '17 at 15:33

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