0

When records in table orbeon_form_data grow to 2600+ rows, we started to experience slow response for Persistent API /search/$app/$form, it took 24000+ ms to return result.

Search API and the example of POST BODY as below: http://localhost/orbeon/fr/service/persistence/search/MYAPP/MYFORM

<search>
<query search-field="true" match="exact" path="section-search/myKey">myValue</query>
    <page-size>10</page-size>
    <page-number>1</page-number>
<lang>en</lang>
</search>

We're using PostgreSQL and by comparing official postgresql-2016_2.sql with oracle-2016_2.sql, the Oracle version contains extra statement to create indexes.

Could the missing index the reason of the slow response?

Below is the partial log:

2016-09-30 14:22:58,846 INFO ProcessorService - /fr/service/persistence/form/MYAPP/MYFORM - Timing: 64

2016-09-30 14:22:58,846 INFO lifecycle - event: {"request": "5624", "session": "FBBA97DDA0A35BA878C01250536E791F", "source": "service", "message": "end: handle", "time": "64 ms"}

2016-09-30 14:23:23,443 INFO ProcessorService - /fr/service/postgresql/search/MYAPP/MYFORM - Timing: 24670

2016-09-30 14:23:23,444 INFO lifecycle - event: {"request": "5623", "session": "FBBA97DDA0A35BA878C01250536E791F", "source": "service", "message": "end: handle", "time": "24,671 ms"}

SELECT COUNT(*)
FROM (SELECT c.data_id,
             c.document_id,
             c.draft,
             c.created,
             c.last_modified_time,
             c.last_modified_by,
             c.username,
             c.groupname,
             ROW_NUMBER() OVER (ORDER BY c.last_modified_time DESC) ROW_NUMBER
      FROM orbeon_i_current c,
           orbeon_form_data d
      WHERE c.app = 'MYAPP'
      AND   c.form = 'MYFORM'
      AND   c.draft = 'N'
      AND   d.id = c.data_id
      AND   xml::text ilike ?) a;

SELECT c.*,
       t.control,
       t.pos,
       t.val
FROM (SELECT c.data_id,
             c.document_id,
             c.draft,
             c.created,
             c.last_modified_time,
             c.last_modified_by,
             c.username,
             c.groupname,
             ROW_NUMBER() OVER (ORDER BY c.last_modified_time DESC) ROW_NUMBER
      FROM orbeon_i_current c,
           orbeon_form_data d
      WHERE c.app = 'MYAPP'
      AND   c.form = 'MYFORM'
      AND   c.draft = 'N'
      AND   d.id = c.data_id
      AND   xml::text ilike ?) c
  LEFT JOIN orbeon_i_control_text t ON c.data_id = t.data_id
WHERE ROW_NUMBER BETWEEN 1 AND 10 ;
Poo Cb
  • 63
  • 4
  • Just to confirm, you're using Orbeon Forms 2016.2, correct? I imagine that is the case, since you're referring to the DDL for that version, but I'd just like to confirm, as 2016.2 includes major changes to improve the performance of the search API. If that is the case, yes, I'm sure that indexes could help, but I'm wondering if there isn't something else going on, as even a full table scan shouldn't take 25s on 2600 rows. – avernet Sep 30 '16 at 17:22
  • Yup, you're right, we're using 2016.2. We are pretty sure Database and Tomcat is not overload. From the log above, there is no other process happen in between, is there a way to debug out the SQL running behind? Secondly, would orbeon provides updated DDL that has create indexes in postgresql version similar to Oracle DDL? – Poo Cb Oct 04 '16 at 02:10
  • You can have the SQL logged by setting the category `org.orbeon.relational` in `debug`, in Orbeon Forms `log4j.xml`. See [this section of the doc](https://doc.orbeon.com/form-runner/feature/home-page.html#logging) for the snippet you can add Orbeon Forms' `log4j.xml`. You'll let us know what you find with this. – avernet Oct 04 '16 at 16:46
  • Great, I managed to capture it. The sql(edited into question) is query against orbeon_form_data's xml column, but not searching orbeon_i_control_text's val column that already has my index value defined by fr-search within my form. (Addition info:- I had upgraded orbeon from 2016.2 to 2016.2.1 before running this test) – Poo Cb Oct 05 '16 at 01:40
  • You have 2 queries there, which I re-formatted slighted to improve legibility. Do you know which one is slow? Or are they both slow? And how long do they take in your case? I suspect that is the `xml::text ilike ?` which is slow. This happens when doing a "free text search" from a summary page, correct? – avernet Oct 05 '16 at 21:34
  • For free text search, the issue is that Orbeon Forms does a `xml::text ILIKE %gaga%`, and that based on what I am reading [here](http://stackoverflow.com/a/12126659/5295), no index can be used in this case. So it's not just a matter of creating an index, but instead Orbeon Forms should use [PostgreSQL full text search](https://www.postgresql.org/docs/current/static/textsearch.html) capability, as done on Oracle and SQL Server. – avernet Oct 05 '16 at 21:35
  • Would you be able to figure out how the query needs to be modified to use PostgreSQL full text search? And how the DDL for the table would need to be modified? Maybe this isn't too hard, and you could contribute this to the product to improve the performance of free text search on PostgreSQL? – avernet Oct 05 '16 at 21:37
  • Hi avernet, I'm not running from summary page, but Search Persistent API. I edited my question to include example of my Search API. I was based on "Data is still stored as XML as it was before, but the subset thereof needed for summary pages and the search API is also stored in the index tables. As a result, summary pages and the search API never need to access data stored in XML, and can thus run much faster." in this article(http://blog.orbeon.com/2016/06/new-indexes-boost-performance-with.html) . Did I miss out any step that cause my Search not running against index table? – Poo Cb Oct 06 '16 at 01:23
  • hi avernet, I had also sent an email to orbeon support to request official support on this issue since we have PE Basic license support. – Poo Cb Oct 10 '16 at 02:20
  • Hi Poo Cb, the "never need to access data stored in XML" is true if you're searching for a value of a specific field, as since 2016.2 those values are indexed in `orbeon_i_control_text`. But if you're doing a "free text search", then the XML still needs to be accessed. Would you, in your case, be able to do the search on specific fields? – avernet Oct 10 '16 at 19:28
  • Hi avernet, I thought i already using "Structure Search", not "Free Text" because as example above, the query attribute is not blank, with search path defined. Could you advice me how to use Search API to search orbeon_i_control_text table where control = 'section-search/myKey' and val = 'myValue'; – Poo Cb Oct 11 '16 at 07:29
  • Hi Poo, I think I see the problem: the first is taken to be a free text search, and its attributes are ignored. So if you don't want to do a free text search, you should have an empty before your "structured-search" . Could you try this? And I agree: the documentation isn't clear on this, and this doesn't make much sense. I think we should change this for the next release. – avernet Oct 11 '16 at 16:54
  • I've updated section of the doc on [full-text search](https://doc.orbeon.com/form-runner/api/persistence/search.html#full-text-search) to clarify that the first `` is always expected. – avernet Oct 11 '16 at 17:11
  • FYI, I've also created an [RFE](https://github.com/orbeon/orbeon-forms/issues/2960) to improve the way the query is interpreted. – avernet Oct 12 '16 at 00:06
  • hi avernet, after change to use correct syntax, it's now running against index table, only took 400+ms. thank you very much, – Poo Cb Oct 12 '16 at 14:19
  • Excellent, I'm glad that worked, and thank you for the confirmation. – avernet Oct 13 '16 at 16:39

0 Answers0