exec dbms_sqltune.alter_sql_profile ( NAME => 'SYS_SQLPROF_0251acbeeb080004', attribute_name => 'NAME', VALUE => 'FIX_FTS_ON_SMEVLOG');
Posts from Сентябрь 2016
postgresql query to find duplicate indexes
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
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
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»
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
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:
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