Fusionpbx – slow database – not show cdr

https://www.pbxforums.com/threads/cdrs-slow-experiencing-crashes-when-accessing-cdrs-add-indexes-to-v_xml_cdr.2793/

postgres database password location: /etc/fusionpbx/config.php

//pgsql: database connection information
        $db_host = 'localhost'; //set the host only if the database is not local
        $db_port = '5432';
        $db_name = 'fusionpbx';
        $db_username = 'fusionpbx';
        $db_password = '1CQorF0aQYmBnRIpNklTUz3pU';
#!/bin/sh
export PGPASSWORD="xxxxx"
START=1
DIV=100

#Get the total number of records from the database
TOTAL=`psql --host=127.0.0.1 --username=fusionpbx -t -c "SELECT count(*) FROM v_xml_cdr WHERE json ? 'variables' and start_stamp < NOW() - INTERVAL '20 days'"`

#Divide the toal number by 100 since we are doing limit 100
END=$(echo $((TOTAL/DIV)) | awk '{print int($0)}')
echo $END

#Loop through the records and set the json value equal NULL
i=$START
while [[ $i -le $END ]]
do
psql --host=127.0.0.1 --username=fusionpbx -t -c "WITH cte AS (SELECT uuid FROM v_xml_cdr WHERE json ? 'variables' and start_stamp < NOW() - INTERVAL '20 days' limit 100) UPDATE v_xml_cdr AS s SET json = NULL FROM cte WHERE s.uuid = cte.uuid"
((i = i + 1))
done
fusionpbx=# select count(*) from v_xml_cdr;
   count
 2025797
 (1 row)

create index index_billsec on v_xml_cdr(billsec); create index index_caller_id_name on v_xml_cdr(caller_id_name); create index index_destination_number on v_xml_cdr(destination_number); create index index_duration on v_xml_cdr(duration); create index index_hangup_cause on v_xml_cdr(hangup_cause); create index index_start_stamp on v_xml_cdr(start_stamp);