an Alfresco blockers query related to 2x largest tables of Alfresco PostgreSQL for windows

dba100 — Fri, 31 Dec 2021 07:49:51 GMT

hi,

Recently I see this query is the blockers in Alfresco 5.0 PostgreSQL 9.3.6 for Windows.

select
node.id as node_id,
node.version as node_version,
prop.qname_id as qname_id,
prop.locale_id as locale_id,
prop.list_index as list_index,
prop.actual_type_n as actual_type_n,
prop.persisted_type_n as persisted_type_n,
prop.boolean_value as boolean_value,
prop.long_value as long_value,
prop.float_value as float_value,
prop.double_value as double_value,
prop.string_value as string_value,
prop.serializable_value as serializable_value

from
alf_node node
join alf_node_properties prop on (prop.node_id = node.id)
WHERE node_id in
(
$1
,
$2
,
$3
,
$4
,

why this one is slow and is it related to the large size of alf_node node and alf_node_properties ?

what can be done to improve the speed ?

topic an Alfresco blockers query related to 2x largest tables of Alfresco PostgreSQL for windows in Alfresco Forum

an Alfresco blockers query related to 2x largest tables of Alfresco PostgreSQL for windows