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);