Re: index question

On Tue, 3 May 2016 03:46 drum(dot)lucas(at)gmail(dot)com, <drum(dot)lucas(at)gmail(dot)com> wrote:

> The index that I've created and is working is:
>
> Index without typecasting:
>
>> CREATE INDEX CONCURRENTLY ix_clientids2 ON gorfs.inode_segments USING
>> btree (full_path);
>
>
> Thanks for the help, guys!
>
>
> Melvin, that Query you sent is very interesting..
>
> SELECT n.nspname as schema,
>> i.relname as table,
>> i.indexrelname as index,
>> i.idx_scan,
>> i.idx_tup_read,
>> i.idx_tup_fetch,
>> CASE WHEN idx.indisprimary
>> THEN 'pkey'
>> WHEN idx.indisunique
>> THEN 'uidx'
>> ELSE 'idx'
>> END AS type,
>> pg_get_indexdef(idx.indexrelid),
>> CASE WHEN idx.indisvalid
>> THEN 'valid'
>> ELSE 'INVALID'
>> END as statusi,
>> pg_relation_size(quote_ident(n.nspname)|| '.' ||
>> quote_ident(i.relname)) as size_in_bytes,
>> pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
>> quote_ident(i.relname))) as size
>> FROM pg_stat_all_indexes i
>> JOIN pg_class c ON (c.oid = i.relid)
>> JOIN pg_namespace n ON (n.oid = c.relnamespace)
>> JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
>> WHERE n.nspname NOT LIKE 'pg_%'
>> ORDER BY 1, 2, 3;
>
>
>
> I've found more then 100 indexes that the columns:
>

I am not sure, but I think if the size of index is very huge and you
suspect they are not being used, you might want to check the bloats
percentage in the index. If this is true, perhaps a reindex might help.

> "idx_scan", "idx_tup_read" and "idx_tup_fetch" are 0.
> So, it's safe to say that they are not being used, is that right?
>
> But some indexes have almost 100GB on the size column. This means they are
> not being used now, but they could be used in the past?
>
> - Is it safe to remove them?
>
> Cheers
> Lucas
>
--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com