SQL Tips & Tricks – PostgreSQL Queries – Part 1

If you are working daily with PostgreSQL database, sometimes you may have a need to get some information or valid stuff that normally are inside of a Database Administrator scope.

In this article I will share with you 3 useful queries mainly for working with indexes in PostgreSQL, version 9.6+.

Duplicate Indexes

When multiple developers are working on a database and they have a possibility to create indexes, it may be a bit troublesome. There may be a case that one or many indexes will be created for same field (or fields). To check for duplicate indexes you can use query below. It uses standard pg_index results prepared key. One flaw of this query is that if you have more then 4 indexes duplicated it will show only 4, but hopefully it is not your case 🙂

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

--Get Duplicated Indexes Information

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT)ASSIZE,

(array_agg(idx))[1]ASidx1,

(array_agg(idx))[2]ASidx2,

(array_agg(idx))[3]ASidx3,

(array_agg(idx))[4]ASidx4

FROM(SELECT indexrelid::regclass ASidx,

(indrelid::text||

E'\n'||

indclass::text||

E'\n'||

indkey::text||

E'\n'||

COALESCE(indexprs::text,'')||

E'\n'||

COALESCE(indpred::text,''))ASKEY

FROM pg_index)sub

GROUP BY KEY HAVING COUNT(*)>1

ORDER BY SUM(pg_relation_size(idx))DESC;

Query Results:

idx1, idx2 … idx4 are Indexes names that are duplicates on a certain fields.

Indexes With Less Than 300 Scans

Another popular case is when your created index is not being used by query plan, either because of a way data are being queried or due to simple design issue.

With below query, you can verify all non unique (it is important to remove from analysis indexes created on unique keys) indexes with less than 300 scans, on schema different than postgres.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

--Get Information about indexes with less then300scans that are notunique

We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it. For any details please reach out to our privacy policy.OkRead more