> We recently did a test on COPY and found that on large tables (47 million
rows , 20GB of raw data) the
> difference in COPY with 16 indexes...
*I am very suspicious of why you need 16 indexes. Are you sure all those
indexes are actually being utilized?*
*Try executing the attached query, You may find find some are really not
needed.*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' ||
quote_ident(i.relname))) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' ||
quote_ident(i.indexrelname))) AS index_size,
pg_get_indexdef(idx.indexrelid) as idx_definition
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 i.idx_scan = 0
AND n.nspname <> 'pg_catalog'
AND NOT idx.indisprimary
AND NOT idx.indisunique
ORDER BY 1, 2, 3;
