Metacat

IN clause is not so efficient. I was able to improve performance by using outer joins instead of IN to improve performance in some of the other queries and those might be worth checking out. Also I think IN clause performance is much improved in Postgres 8 as opposed to Postgres 7

Just changing the 'LIKE' operator to the '=' operator allows the optimizer to use indices if they exist. Unfortunately, this has the negative side effect of not working for wildcard substring queries, thereby significantly reducing functionality.

Following query can be used to replace the first IN clause

select distinct a.docid, a.docname, a.doctype, a.date_created, a.date_updated, a.rev from xml_documents a LEFT JOIN xml_path_index b ON a.docid=b.docid where UPPER LIKE '%WATER%' AND path IN ('abstract/para','givenName','keyword','organizationName','title','surName', 'para','geographicDescription','literalLayout') and b.docid is NOT NULL

Now I found a problem in query modification.
If path query looks like:

<pathquery version="1.0">
<meta_file_id>test</meta_file_id>
<returndoctype>eml://ecoinformatics.org//eml-2.0.0</returndoctype>
<returnfield>dataset/title</returnfield>
<querygroup operator="UNION">
<queryterm casesensitive="false" searchmode="contains">
<value>soil</value>
<pathexpr>dataset/title</pathexpr>
</queryterm>
<queryterm casesensitive="false" searchmode="contains">
<value>soil</value>
<pathexpr>keyword</pathexpr>
</queryterm>
<queryterm casesensitive="false" searchmode="contains">
<value>soil</value>
<pathexpr>organizationName</pathexpr>
</queryterm>
</querygroup>
</pathquery>

Then the query:
SELECT docid,docname,doctype,date_created, date_updated, rev
FROM xml_documents
WHERE docid IN (

SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'dataset/title'  
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'keyword'
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'organizationName' 
                 )
 )

can be modified to
SELECT docid,docname,doctype,date_created, date_updated, rev
FROM xml_documents
WHERE docid IN (
(
SELECT DISTINCT docid FROM xml_path_index
WHERE UPPER LIKE '%SOIL%'
AND path IN ("dataset/title','keyword','organizationName'
)
)
)

However, from the path query definition, it is not necessary that every path will hold the same value, e.g. dataset/title can be soil, keyword can be grass land and organization can be NCEAS. In this case the above modification doesn't work. So our modification now only fits a special case instead of a general case.

Jing,

We were aware of this before. But still, the special case where the same query term is being searched is the most common query case (most of our clients have this mode of searching multiploe fields for the same query term). So, I think that modifying QuerySpecification to produce the simpler, non-unioned query when the query term is the same for several fields will result in noticeable performance improvements.

In the more general case when the query terms do not match, you could still eliminate the UNION with a query like this:

SELECT docid,docname,doctype,date_created, date_updated, rev
FROM xml_documents
WHERE docid IN (
(
SELECT DISTINCT docid FROM xml_path_index
WHERE
(UPPER LIKE '%SOIL%'
AND path ="dataset/title')
OR
(UPPER LIKE '%NITROGEN%'
AND path = 'keyword')
)
)

I haven't tested this for performace improvements, but I suspect it is faster than the UNION.

So QuerySpecification would have to detect when clusters of paths are being searched for the same term and use whichever form is appropriate. You can also mix the two approaches (ie, search a bunch of fields for one term, and OR that with a search of one or more fields for different terms.

It took about 39 seconds to run above query in knb database and got 982 rows.
If run the union query:
SELECT docid,docname,doctype,date_created, date_updated, rev
FROM xml_documents
WHERE docid IN (
(

SELECT DISTINCT docid FROM xml_path_index WHERE         UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'dataset/title'
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%NITROGEN%' AND path LIKE 'keyword' 
                 )
                );

It took 29 seconds and got 982 rows too. So your guess is right. So QuerySpecification would have to detect when clusters of paths are being
searched for the same term and use whichever form is appropriate.

Hi, matt:

The query modification looks good for "UNION" operator in Query Group. Do you have any idea if the operator is "INTERCECT"?

A readable format of metacat original query with INTERSECT operator.

Here is the timing for query:
1. In default search page, search "datos" and got 8 hits.

total search time 33 seconds:
a. Selection docid total took 32 seconds. 32 of 32 seconds was used to run selection query (note this query only select xml_nodes table).
b. Preparing return fields took 1.0 second. (1.0 was taken to run the query of selection of xml_queryresult table).
c.Transform from xml to html took 0 second

2. Ran query:

<pathquery version="1.0">
<meta_file_id>test</meta_file_id>
<returndoctype>eml://ecoinformatics.org//eml-2.0.0</returndoctype>
<returnfield>dataset/title</returnfield>
<querygroup operator="UNION">
<queryterm casesensitive="false" searchmode="contains">
<value>datos</value>
<pathexpr>dataset/title</pathexpr>
</queryterm>
<queryterm casesensitive="false" searchmode="contains">
<value>datos</value>
<pathexpr>keyword</pathexpr>
</queryterm>
<queryterm casesensitive="false" searchmode="contains">
<value>datos</value>
<pathexpr>originator/individualName/surName</pathexpr>
</queryterm>
</querygroup>
</pathquery>

It took 21 seconds and got 0 hits.

a. Selection docid total took 17 seconds. 17 of 17 seconds was used to run selection query.
b. Preparing return fields took 0.0 second.
c.Transform from xml to html took 4 second

select docid, path, nodedata, parentnodeid from xml_path_index where
(path like 'originator/individualName/surName' or
path like 'originator/individualName/givenName' or
path like 'creator/individualName/surName' or
path like 'creator/individualName/givenName' or
path like 'originator/organizationName' or
path like 'creator/organizationName' or
path like 'dataset/title' or

AND docid in
('ABS.4','access.10','access.7','access.8','access.9',
'ADCP_template.50','adler.3','ALASDatabaseAccess.3','ALEXXX_015ADCP015R00_19990817.50',
'ALEXXX_015ADCP015R00_19990906.50','ALEXXX_015ADCP015R00_19991108.50',
'ALEXXX_015ADCP015R00_20000106.50','ALEXXX_015ADCP015R00_20000302.50',
'ALEXXX_015ADCP015R00_20000523.50','ALEXXX_015ADCP015R00_20000815.50')

<pathquery version="1.2">
<returndoctype>-//ecoinformatics.org//eml-dataset-2.0.0beta6//EN</returndoctype>
<returndoctype>-//ecoinformatics.org//eml-dataset-2.0.0beta4//EN</returndoctype>
<returndoctype>eml://ecoinformatics.org/eml-2.0.0</returndoctype>
<returndoctype>eml://ecoinformatics.org/eml-2.0.1</returndoctype>
<returndoctype>-//NCEAS//eml-dataset-2.0//EN</returndoctype>
<returndoctype>-//NCEAS//resource//EN</returndoctype>
<returnfield>originator/individualName/surName</returnfield>
<returnfield>originator/individualName/givenName</returnfield>
<returnfield>originator/organizationName</returnfield>
<returnfield>creator/individualName/surName</returnfield>
<returnfield>creator/organizationName</returnfield>
<returnfield>dataset/title</returnfield>
<returnfield>keyword</returnfield>
<querygroup operator="UNION">
<queryterm casesensitive="false" searchmode="contains">
<value>datos</value>
</queryterm>
</querygroup>
</pathquery>

Value Hits Total(seconds) Selection[selection query] GetReturnFields[xml_queryresult, extend(extend query, attribute query), storeBackXml_queryresult] xml-html
% 14751 448 401² 24 [13, 8 (?, 0), 0] 23
land 6253 169 38³² 8 [4, 3 (?, 0), 0] 123
soil 1612 42 33³² 3 [2, 0 (0, 0), 0] 6
datos 7 32 32³¹ 0 [0, 0 (0, 0), 0] 0

<pathquery version="1.2">
<querytitle>Web-Search</querytitle>
<returndoctype>eml://ecoinformatics.org/eml-2.0.1</returndoctype>
<returndoctype>eml://ecoinformatics.org/eml-2.0.0</returndoctype>
<returndoctype>-//ecoinformatics.org//eml-dataset-2.0.0beta6//EN</returndoctype>
<returndoctype>-//ecoinformatics.org//eml-dataset-2.0.0beta4//EN</returndoctype>
<returndoctype>-//NCEAS//resource//EN</returndoctype><returndoctype>-//NCEAS//eml-dataset//EN</returndoctype>
<returnfield>originator/individualName/surName</returnfield>
<returnfield>originator/individualName/givenName</returnfield>
<returnfield>creator/individualName/surName</returnfield>
<returnfield>creator/individualName/givenName</returnfield>
<returnfield>originator/organizationName</returnfield>
<returnfield>creator/organizationName</returnfield>
<returnfield>dataset/title</returnfield>
<returnfield>keyword</returnfield>
<returnfield>@packageId</returnfield>
<querygroup operator="UNION">
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>surName</pathexpr>
</queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>givenName</pathexpr>
</queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>keyword</pathexpr>
</queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>para</pathexpr></queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>geographicDescription</pathexpr>
</queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>literalLayout</pathexpr>
</queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>title</pathexpr>
</queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>@packageId</pathexpr>
</queryterm>
<queryterm searchmode="contains" casesensitive="false">
<value>datos</value>
<pathexpr>abstract/para</pathexpr>
</queryterm>
</querygroup>
</pathquery>

Value Hits Total(seconds) Selection[selection query] GetReturnFields[xml_queryresult, extend(extend query, attribute query), relationship, storeBackXml_queryresult] xml-html
% ? 10075 94⁷¹ 8581 [1, 8347 (132, 89), ? 63] 1400
land 5944 112 14¹¹ 39 [2, 35 (1, 32), ? 0] 59
soil 1509 50 10¹⁰ 34 [1, 32 (0, 30), ? 0] 5
datos 7 10 10¹⁰ 0 [0, 0 (0, 0), ? 0] 0

Above query after removing a attribute return field
Value Hits Total(seconds) Selection[selection query] GetReturnFields[xml_queryresult, extend(extend query, attribute query), relationship, storeBackXml_queryresult] xml-html
land 5944 141 65(61) 59 [9, 10 (7, 0), 1, 0] 16
soil 1509 104 44(44) 45 [3, 5 (2, 0), 0, 0] 14
datos 7 49 46(46) 2 [0, 0 (0, 0), 0, 0] 1

After removing xml_queryresult and xml_returnfield table
Value Hits Total(seconds) Selection[selection query] GetReturnFields[xml_queryresult, extend(extend query, attribute query), relationship, storeBackXml_queryresult] xml-html
land ? 3113 49(40) 1668 [1, 1530 (24, 0), 51, 63] 1396
soil 1509 471 36(33) 360 [0, 101 (13, 0), 11, 225] 75
datos 7 59 50(49) 7 [0, 0 (0, 0), 0, 5] 2

The previous comment has the same content as this attachment. However, the comment is not readable. So I added this attachment.

SELECT docid, nodedata, path FROM xml_path_index WHERE path IN ('dataset/title','entityName','individualName/surName','keyword');
SELECT docid, rev, docname, doctype, date_created,date_updated from xml_documents;
INSERT INTO xml_queryresult (returnfield_id, docid, queryresult_string) VALUES (?, ?, ?);

Move to 1.7.1 release

Move to release 1.7.1

Move this bug from 1.7.1 to 1.8. Continue to improve metacat performance.

I played around Postgresql new feature - text search. Here are some results:

Using metacat 1.8.0 search query (base on xml_path_index):
SELECT docid,docname,doctype,date_created, date_updated, rev
FROM xml_documents
WHERE docid IN
(
(
SELECT DISTINCT docid FROM xml_path_index WHERE
( UPPER like '%TEST%'
AND path IN ('abstract/para','givenName','keyword','organizationName','title','surName','para','geographicDescription','literalLayout')
)
)
)
AND (
docid IN (
SELECT docid from xml_access WHERE = 'public') AND perm_type = 'allow' AND permission > 3)
)
AND
docid NOT IN (
SELECT docid from xml_access WHERE = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3)
)
)
It took 5.5 second in dev.nceas.

Then I modified the xml_path_index table:

BEGIN;
ALTER TABLE xml_path_index ADD COLUMN nodedatavector TSVECTOR;
UPDATE xml_path_index SET nodedatavector = to_tsvector(nodedata);
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON xml_path_index FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(nodedatavector, 'pg_catalog.english', nodedata);
CREATE INDEX xml_path_index_vector ON xml_path_index USING gin(nodedatavector);
COMMIT;

Then using new modified query
SELECT docid,docname,doctype,date_created, date_updated, rev
FROM xml_documents
WHERE docid IN
(
(
SELECT DISTINCT docid FROM xml_path_index WHERE
( UPPER like '%TEST%'
AND path IN ('abstract/para','givenName','keyword','organizationName','title','surName','para','geographicDescription','literalLayout')
)
)
)
AND (
docid IN (
SELECT docid from xml_access WHERE = 'public') AND perm_type = 'allow' AND permission > 3)
)
AND
docid NOT IN (
SELECT docid from xml_access WHERE = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3)
)
)

It took 33 seconds. It slows down.

Here is the 1.8.0 query base on xml_nodes(not specifying the search path):
SELECT docid,docname,doctype,date_created, date_updated, rev FROM xml_documents
WHERE docid IN
(((SELECT DISTINCT docid FROM xml_nodes WHERE UPPER LIKE '%TEST%' ) ))
AND
(docid IN
(SELECT docid from xml_access WHERE = 'public') AND perm_type = 'allow' AND permission > 3))
AND docid NOT IN
(SELECT docid from xml_access WHERE = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3))

It took 60 seconds and got 895 hits.

xml_nodes table was modified by
BEGIN;
ALTER TABLE xml_nodes ADD COLUMN nodedatavector TSVECTOR;
UPDATE xml_nodes SET nodedatavector = to_tsvector(nodedata);
CREATE TRIGGER xml_node_tsvectorupdate BEFORE INSERT OR UPDATE ON xml_nodes FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(nodedatavector, 'pg_catalog.english', nodedata);
CREATE INDEX xml_node_vector ON xml_nodes USING gin(nodedatavector);
COMMIT;

Then run the following query:
ELECT docid,docname,doctype,date_created, date_updated, rev FROM xml_documents
WHERE docid IN
(((SELECT DISTINCT docid FROM xml_nodes WHERE nodedatavector @@ to_tsquery('english', 'test') ) ))
AND
(
docid IN (SELECT docid from xml_access WHERE = 'public') AND perm_type = 'allow' AND permission > 3))
AND
docid NOT IN (SELECT docid from xml_access WHERE = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3))
)

It took 1.8 seconds and got 736 hits. Oh, much faster!

Here is the comparison when use search a eml path which is not in xml_path_index table:
SELECT docid,docname,doctype,date_created, date_updated, rev FROM
xml_documents WHERE docid IN
((
SELECT DISTINCT docid FROM xml_nodes
WHERE UPPER LIKE '%VALUE1%'
AND
parentnodeid IN (SELECT nodeid FROM xml_index WHERE path LIKE 'path1')
))
AND (docid IN (SELECT docid from xml_access WHERE = 'public') AND perm_type = 'allow' AND permission > 3)) AND docid NOT IN (SELECT docid from xml_access WHERE = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3) ))

30 seconds

SELECT docid,docname,doctype,date_created, date_updated, rev FROM
xml_documents WHERE docid IN
((
SELECT DISTINCT docid FROM xml_nodes
WHERE nodedatavector @@ to_tsquery('english', 'test')
AND
parentnodeid IN (SELECT nodeid FROM xml_index WHERE path LIKE 'path1')
))
AND (docid IN (SELECT docid from xml_access WHERE = 'public') AND perm_type = 'allow' AND permission > 3)) AND docid NOT IN (SELECT docid from xml_access WHERE = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3) ))

12 seconds

We can see ts_vector help the search.

Now we can conclude:
ts search helps the query 1. without specifying xpath.
2. xpath is not in xml_path_index.
However, for xpath is in xml_path_index, the search speed is slower.

In order to figure out why it is slower I did following test. We can see if i remove the line : path IN ('abstract/para','givenName','keyword','organizationName','title','surName','para','geographicDescription','literalLayout')

The speed will jump to 0.8 seconds from 47 seconds. Any one have idea about this?

Here is the query and query plan in postgresql:
SELECT queryresult_string
FROM xml_queryresult
WHERE docid IN
(
(
SELECT DISTINCT docid FROM xml_path_index WHERE
(path IN ('abstract/para','givenName','keyword','organizationName','title','surName','para','geographicDescription','literalLayout')
AND nodedatavector @@ to_tsquery('english', 'test')
)
)
)
AND (
docid IN (
SELECT docid from xml_access WHERE = 'public') AND perm_type = 'allow' AND permission > 3)
)
AND
docid NOT IN (
SELECT docid from xml_access WHERE = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3)
)
)
AND (
returnfield_id=1
)

Nested Loop IN Join  (cost=11787.67..27048.77 rows=1 width=969) (actual time=145.877..47048.898 rows=691 loops=1)
   Join Filter: ((xml_queryresult.docid)::text = (public.xml_access.docid)::text)
   ->  Nested Loop  (cost=11787.67..19651.83 rows=1 width=1510) (actual time=142.646..175.635 rows=691 loops=1)
         ->  Unique  (cost=3942.40..3944.56 rows=2 width=25) (actual time=17.997..25.338 rows=737 loops=1)
               ->  Sort  (cost=3942.40..3943.48 rows=432 width=25) (actual time=17.997..20.641 rows=1464 loops=1)
                     Sort Key: xml_path_index.docid
                     Sort Method:  quicksort  Memory: 93kB
                     ->  Bitmap Heap Scan on xml_path_index  (cost=60.88..3923.49 rows=432 width=25) (actual time=0.000..5.040 rows=1464 loops=1)
                           Recheck Cond: (nodedatavector @ '''test'''::tsquery)
                           Filter: ((path)::text = ANY (('{abstract/para,givenName,keyword,organizationName,title,surName,para,geographicDescription,literalLayout}'::character varying[])::text[]))
                           ->  Bitmap Index Scan on xml_path_index_vector  (cost=0.00..60.77 rows=1069 width=0) (actual time=0.000..0.000 rows=1569 loops=1)
                                 Index Cond: (nodedatavector @ '''test'''::tsquery)
         ->  Index Scan using xml_queryresult_idx1 on xml_queryresult  (cost=7845.27..7853.61 rows=1 width=994) (actual time=0.191..0.197 rows=1 loops=737)
               Index Cond: ((xml_queryresult.returnfield_id = 1) AND ((xml_queryresult.docid)::text = (xml_path_index.docid)::text))
               Filter: (NOT (hashed subplan))
               SubPlan
                 ->  Seq Scan on xml_access  (cost=0.00..7845.27 rows=1 width=30) (actual time=0.000..123.606 rows=124 loops=1)
                       Filter: ((permission > 3) AND ((perm_type)::text = 'deny'::text) AND ((perm_order)::text = 'allowFirst'::text) AND (lower((principal_name)::text) = 'public'::text))
   ->  Seq Scan on xml_access  (cost=0.00..7385.46 rows=918 width=30) (actual time=0.013..59.893 rows=3482 loops=691)
         Filter: ((public.xml_access.permission > 3) AND ((public.xml_access.perm_type)::text = 'allow'::text) AND (lower((public.xml_access.principal_name)::text) = 'public'::text))
 Total runtime: 47050.383 ms

SELECT queryresult_string
FROM xml_queryresult
WHERE docid IN
(
(
SELECT DISTINCT docid FROM xml_path_index WHERE
(nodedatavector @@ to_tsquery('english', 'test')

)
                     )
                    )  
      AND (
           docid IN (
                      SELECT docid from xml_access WHERE( (lower(principal_name) = 'public') AND perm_type = 'allow' AND permission > 3)
                     ) 
            AND 
            docid NOT IN (
                           SELECT docid from xml_access WHERE( (lower(principal_name) = 'public') AND perm_type = 'deny' AND perm_order ='allowFirst' AND permission > 3) 
                         )
          )
      AND (
           returnfield_id=1
          )

Hash Join  (cost=19200.43..42775.07 rows=1 width=969) (actual time=758.885..807.122 rows=691 loops=1)
   Hash Cond: ((xml_queryresult.docid)::text = (public.xml_access.docid)::text)
   ->  Nested Loop  (cost=11810.67..35385.30 rows=2 width=1510) (actual time=141.284..171.137 rows=691 loops=1)
         ->  Unique  (cost=3965.40..3970.75 rows=4 width=25) (actual time=19.300..26.762 rows=739 loops=1)
               ->  Sort  (cost=3965.40..3968.08 rows=1069 width=25) (actual time=19.297..22.357 rows=1552 loops=1)
                     Sort Key: xml_path_index.docid
                     Sort Method:  quicksort  Memory: 97kB
                     ->  Bitmap Heap Scan on xml_path_index  (cost=61.04..3911.62 rows=1069 width=25) (actual time=1.029..5.876 rows=1552 loops=1)
                           Recheck Cond: (nodedatavector @ '''test'''::tsquery)
                           ->  Bitmap Index Scan on xml_path_index_vector  (cost=0.00..60.77 rows=1069 width=0) (actual time=0.882..0.882 rows=1569 loops=1)
                                 Index Cond: (nodedatavector @ '''test'''::tsquery)
         ->  Index Scan using xml_queryresult_idx1 on xml_queryresult  (cost=7845.27..7853.61 rows=1 width=994) (actual time=0.182..0.185 rows=1 loops=739)
               Index Cond: ((xml_queryresult.returnfield_id = 1) AND ((xml_queryresult.docid)::text = (xml_path_index.docid)::text))
               Filter: (NOT (hashed subplan))
               SubPlan
                 ->  Seq Scan on xml_access  (cost=0.00..7845.27 rows=1 width=30) (actual time=0.594..120.731 rows=124 loops=1)
                       Filter: ((permission > 3) AND ((perm_type)::text = 'deny'::text) AND ((perm_order)::text = 'allowFirst'::text) AND (lower((principal_name)::text) = 'public'::text))
   ->  Hash  (cost=7388.65..7388.65 rows=89 width=30) (actual time=617.598..617.598 rows=23194 loops=1)
         ->  HashAggregate  (cost=7387.76..7388.65 rows=89 width=30) (actual time=488.550..539.284 rows=23194 loops=1)
               ->  Seq Scan on xml_access  (cost=0.00..7385.46 rows=918 width=30) (actual time=0.000..406.257 rows=23194 loops=1)
                     Filter: ((permission > 3) AND ((perm_type)::text = 'allow'::text) AND (lower((principal_name)::text) = 'public'::text))
 Total runtime: 811.484 ms

Original Bugzilla ID was 2155