Skip to content

Posts from Сентябрь 2016

postgresql query to find duplicate indexes

postgresql query to find duplicate indexes published on Комментариев к записи postgresql query to find duplicate indexes нет
1
2
3
4
5
6
7
8
9
SELECT sub.table, pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
     (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
     (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
            FROM (  SELECT indrelid::regclass as table,indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                    COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
                    FROM pg_index
                     where  pg_relation_size(indexrelid::regclass)>100*1024*1024 ) sub
GROUP BY sub.table, KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

sample output

1
2
3
4
       table       |  size  |            idx1             |                idx2                | idx3 | idx4
-------------------+--------+-----------------------------+------------------------------------+------+------
 mdm_record_column | 797 MB | mdm_record_column_column_fk | mdm_record_column_record_fk        |      |
 fin_bill_generate | 300 MB | fin_bill_generate_fk        | fin_bill_generate_spec_item_id_idx |      |

also usefull to look at usage stat on this indexes

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
    relid::regclass AS table,
    indexrelid::regclass AS index,
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan,
    pg_get_indexdef(pg_index.indexrelid) as indexdef
FROM
    pg_stat_user_indexes
    JOIN pg_index USING (indexrelid)
WHERE
     (relid::regclass)::text ='mdm_record_column' order by idx_scan desc ;
 
       table       |              index              | index_size | idx_tup_read | idx_tup_fetch | idx_scan |                                         indexdef
-------------------+---------------------------------+------------+--------------+---------------+----------+-------------------------------------------------------------------------------------------
 mdm_record_column | mdm_record_column_record_fk     | 399 MB     |       758024 |        758024 |     2992 | CREATE INDEX mdm_record_column_record_fk ON mdm_record_column USING btree (column_id)
 mdm_record_column | mdm_record_column_record_id_idx | 399 MB     |         1922 |          1922 |      442 | CREATE INDEX mdm_record_column_record_id_idx ON mdm_record_column USING btree (record_id)
 mdm_record_column | mdm_record_column_pk            | 399 MB     |            0 |             0 |        0 | CREATE UNIQUE INDEX mdm_record_column_pk ON mdm_record_column USING btree (id)
 mdm_record_column | mdm_record_column_column_fk     | 399 MB     |            0 |             0 |        0 | CREATE INDEX mdm_record_column_column_fk ON mdm_record_column USING btree (column_id)

PgSQL Indexes and «LIKE»

PgSQL Indexes and «LIKE» published on Комментариев к записи PgSQL Indexes and «LIKE» нет

Original from Paul Ramsey

Hi all, because English is not my native language, that’s why I will write as little as possible =)
here is the case, when we write a query with like and get Seq Scan (full table scan), instead index scan this material can be usefull for you

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table tt as   select s, md5(random()::text) from generate_Series(1,990000) s;
 create index on tt(md5);
 show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
 
 explain analyze select * from tt where md5 like 'a6b90b58a652b8e1bd01bbe2%';
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on tt  (cost=0.00..20625.00 rows=4950 width=36) (actual time=132.559..132.559 rows=0 loops=1)
   Filter: (md5 ~~ 'a6b90b58a652b8e1bd01bbe2%'::text)
   Rows Removed by Filter: 990000
 Planning time: 0.203 ms
 Execution time: 132.583 ms

if we create same index but with text_pattern_ops we can get good improvement:

1
2
3
4
5
6
7
8
9
10
create index on tt(md5 text_pattern_ops);
 
 explain analyze select * from tt where md5 like 'a6b90b58a652b8e1bd01bbe2%';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using tt_md5_idx1 on tt  (cost=0.42..8.45 rows=99 width=37) (actual time=0.022..0.022 rows=0 loops=1)
   Index Cond: ((md5 >= 'a6b90b58a652b8e1bd01bbe2'::text) AND (md5 < 'a6b90b58a652b8e1bd01bbe3'::text))
   Filter: (md5 ~~ 'a6b90b58a652b8e1bd01bbe2%'::text)
 Planning time: 0.403 ms
 Execution time: 0.043 ms

Primary Sidebar