RES: indexes missing

Hi all,

On the indexes I particularly eliminate the ones that are duplicated and later the indexes that are not being used,
howeverin this second exclusion I take great care with the activity time of the instance because whenever the instance
isrestarted the use counters are lost, at this point the index should be observed by the business, an example would be
aninstance that is active at 8 months and has some indexes that are only used once a year by annual reports that may
appearas unused indexes and have a fundamental role in the business.
 

regards,

 Lincoln Teixeira Gomes - Horus Solutions
 
E-mail: lincoln.teixeira@horusinfo.com.br
 
T.: +55 (41) 3312-0200   
 
www.horusinfo.com.br
 


-----Mensagem original-----
De: Fabio Pardi [mailto:f.pardi@portavita.eu] 
Enviada em: sexta-feira, 4 de maio de 2018 07:33
Para: Thomas Poty <thomas.poty@gmail.com>
Cc: pgsql-admin@postgresql.org
Assunto: Re: indexes missing

Hi Thomas,


if i recall correctly, index bloat can happen in various situations, a common one on BTREE indexes is when you have
sparsedeletions on the referred table and the index becomes unbalanced because the deletions are filled in with empty
spaces.


As per your question. short answer is: no, but sometimes yes. 

Long answer touches several aspects of the database architecture. 

I'll try to make a soup of it, with the help of my notes in the hope i will make it clearer, and not more obscure... 


Every time you insert a record in the database, also extra internal Postgres records are written, in order to know
'whenin time' the record was set and whom it is visible to. Those records are called xmin and xmax. They go together
withanother record called XID.
 
 
When you run a query, any query, you can only see all those records that have Xmin < XID > Xmax


Now, if your record is updated, then internally a new record is placed, with a newer Xmin, Xmax. Note that the old
recordstill exists internally! It tells to Postgres: 'Hey, we have a new occurrence for that record. Do not use the old
one'.During normal operations, the next vacuum will get rid of that old useless record.
 


But, what if a query was running and in the meanwhile you update the table it is running on? Here comes the 'but
sometimesyes' part of my answer.
 


Imagine i m running a query about the number of alive citizens of London. Query starts at 13:00:00. I would expect to
knowthe exact number of citizens at the time the query starts.
 

What If the query takes 2 minutes to run, and in the meanwhile we have 2 deceased? The already running query will
returnthe number of alive citizens at 13:00:00 while the database will ingest the new records, create 2 new rows about
thedeceased citizens, with a new xmin. then go to the old rows about the 2 deceased, and update xmax to avoid
visibilityto the new queries. 
 



If it sounds complicated in words an example can maybe make it more clear. I m taking an example from the book i have
onmy shelf, PostgreSQL 9.0 High Performance (by Gregory Smith, a must read in my opinion)
 



create table old_records(id int PRIMARY KEY,  value varchar);


insert into old_records values (1 , 'abc'); 


Now, we can check xmin and xmax for the current record:


select *,xmin,xmax from old_records ;


 id | value | xmin  | xmax 
----+-------+-------+------
  1 | abc   | 73037 |    0
(1 row)



SELECT * FROM txid_current();
 txid_current
--------------
        73038
(1 row)

------------
now open a new session:
-- session2 --

begin ;
BEGIN

SELECT * FROM txid_current();
 txid_current
--------------
        73039
(1 row)

update old_records SET value='def'  where id=1 ;


select *,xmin,xmax from old_records ;
 id | value | xmin  | xmax
----+-------+-------+------
  1 | def   | 73039 |    0


---------------

while if you go back to session 1:

portavita=# select *,xmin,xmax from old_records ;  id | value | xmin  | xmax  
----+-------+-------+-------
  1 | abc   | 73037 | 73039

---------------


This means that the database has 2 different occurrences of the record id=1 and those are available to 2 different
sessionsin 2 different ways.
 

Once you will run 'end;' in session 2, then the old record will disappear from the visibility, and Xmax will be reset
.

Next vacuum will then get rid of the old occurrences, not needed any longer.


Hope it helps.



regards,

fabio pardi









On 03/05/18 20:17, Thomas Poty wrote:
> Thanks Fabio for your useful advice.
> About index bloat and bloat in general, if i am right it is due to 
> mvcc. So my question is : is there any way to retrieve old data?
> 
> Thank you
> 
> Thomas
> 
> Le lun. 23 avr. 2018 à 17:22, Fabio Pardi <f.pardi@portavita.eu <mailto:f.pardi@portavita.eu>> a écrit :
> 
>     Hi Thomas,
> 
>     I usually also take a look to 'Unused Indexes' and 'Index Bloat' too.
> 
> 
>     Unused indexes:
> 
>     after some time you use your database, you should be able to identify indexes that are never used.
> 
>     Is good to have in your database only indexes you use, given the impact of indexes into your db (space, time to
writedata where indexes are present), and remove the unused ones.
 
> 
> 
> 
>     Index bloat:
> 
>     https://wiki.postgresql.org/wiki/Index_Maintenance#Index_Bloat
> 
> 
> 
>     On the same wiki page you can read more about indexes maintenance, which might clarify some other doubt you
have.
> 
> 
>     About your question on missing indexes, I m not sure what to answer.
>     I think it depends a lot on your setup and how data is queried, eg: if my memory serves me well, sequential scan
canbe chosen by the query plan in some cases, even where an index is present.
 
> 
>     A way I use to tackle slow queries is to periodically analyse 
> statistics and to have slow queries reported in the logs (eg: queries 
> slower than X ms)
> 
> 
>     regards,
> 
>     fabio pardi
> 
> 
> 
>     On 04/23/2018 12:22 PM, Thomas Poty wrote:
>     > hello World,
>     >
>     > Soon, Il will have to "monitor" indexes of a database in production...
>     > I know pg_stattuple may help with this job. I also have several query to help me :
>     >
>     >
>     > This query indicates if an index is invalid :
>     >
>     > select ir.relname    as indexname,
>     >    it.relname        as tablename,
>     >    n.nspname         as schemaname
>     > from pg_index i
>     >    join pg_class ir on ir.oid = i.indexrelid
>     >    join pg_class it on it.oid = i.inderelid
>     >    join pg_namespace n on n.oid = it.relnamespace
>     > where not i.indisvalid;
>     >
>     >
>     >
>     > This one indicates if there are duplicated indexes
>     >
>     >
>     >
>     > select il.table_name,
>     >     il.index_columns,
>     >     array_agg(il.index_name)    as implied_indexes_name
>     > from (
>     >         select
>     >             distinct(pct.relname,pci.relname,pi.indkey) as key,
>     >             pct.oid        as table_oid,
>     >             pct.relname    as table_name,
>     >             pci.relname    as index_name,
>     >             pi.indkey      as index_columns
>     >         from pg_index pi
>     >             join pg_class pci
>     >                 on pi.indexrelid=pci.oid
>     >             join pg_class pct
>     >                 on pi.indrelid=pct.oid
>     >             join pg_attribute pa
>     >                 on pa.attrelid=pct.oid
>     >         where pct.relkind='r'
>     >             and pa.attnum=any(pi.indkey)
>     >      ) il
>     > group by il.table_name, il.index_columns
>     > having count(*)>1;
>     >
>     >
>     > I have found this one but i am not sure if it is technically correct :
>     > - the table must be greater then 100 kB
>     > - the way of a "missing index" is calculated ( Can i have your opinion?)
>     >
>     > SELECT  relname             AS TableName,
>     >         seq_scan-idx_scan   AS TotalSeqScan,
>     >         CASE WHEN seq_scan-idx_scan > 0
>     >             THEN 'Missing Index Found'
>     >             ELSE 'Missing Index Not Found'
>     >         END                 AS MissingIndex,
>     >         pg_size_pretty(pg_relation_size(concat(schemaname,'.',relname)::regclass)) AS TableSize,
>     >         idx_scan            AS TotalIndexScan
>     > FROM pg_stat_all_tables
>     > WHERE schemaname !~'pg_catalog|pg_temp'
>     >     AND pg_relation_size(concat(schemaname,'.',relname)::regclass)>100000
>     > ORDER BY 2 DESC;
>     >
>     >
>     > Is there any others stuffs to keep an eye?
>     > Is there any remarks about my queries?
>     >
>     > Thanks a lot,
>     >
>     > Thomas
>