Bug #2153
closed
Metacat Performace: Add/drop indices on key columns
Added by Saurabh Garg almost 19 years ago.
Updated over 16 years ago.
Description
From Matt's email...
DB indices aren't created on some key columns such as
xml_nodes.nodedata (originally because indices weren't supported on
fields that big). This means a full table scan of the 6.5M records in
xml_nodes for every query. We need to inspect for missing indices and
create them, and need to consider the effect of UPPER on these indices
in case-insensitive queries and be sure to create corresponding UPPER
indices too. Once the indices are in place we need to check EXPLAIN to
be sure the optimizer is using the indices instead of doing table scans
(see http://www.postgresql.org/docs/faqs.FAQ.html#4.7). Also, indices
slow down inserts and updates, so any that aren't in use should be
dropped, which will take some analysis of the running DB (but this is
low priority comparatively speaking).
Indexes can also be added to docid and nodetype in xml_nodes.....
Not enough time to do this for 1.6 release. The performance is fine for now. We
can take this up again in 1.7 release.
For each of the queries, EXPLAIN needs to be done. I have noticed that for some of the queries, full table scan is being done - even though we have a index created for that particular column. I have noticed this in Postgres - hence this needs to be investigated.
Besides adding indices mentioned in bug 1879:
xml_path_index on upper(nodedata)
xml_access on lower(principal_name)
xml_access on perm_type
xml_access on permission
xml_access on perm_order
xml_access on subtreeid
xml_documents on lower(user_owner)
We added more indices in order to improve performance:
CREATE INDEX xml_index_idx2 ON xml_index (docid);
CREATE INDEX xml_nodes_idx4 ON xml_nodes (docid);
CREATE INDEX xml_documents_idx3 ON xml_documents (rootnodeid);
CREATE INDEX xml_index_idx3 ON xml_index (nodeid);
We also changed the data type to TEXT for following columns:
nodedata in xml_nodes;
nodedata in xml_nodes_revision;
queryresult_string in xml_queryresult;
nodedata in xml_path_index;
path in xml_index;
Original Bugzilla ID was 2153
Also available in: Atom
PDF