Query slower in Servoy than PGAdmin. Why?

[swingman]

Code: Select all

EXPLAIN ANALYSE SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' ||  to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE
document_number = 1210001 OR document_customer_acc_no = '1210001' ORDER BY document_date DESC OFFSET 0 LIMIT 26


"Limit (cost=119.99..120.06 rows=26 width=41) (actual time=0.063..0.064 rows=1 loops=1)"
" -> Sort (cost=119.99..120.06 rows=28 width=41) (actual time=0.062..0.062 rows=1 loops=1)"
" Sort Key: document_date"
" Sort Method: quicksort Memory: 25kB"
" -> Bitmap Heap Scan on acc_documents (cost=8.82..119.32 rows=28 width=41) (actual time=0.056..0.056 rows=1 loops=1)"
" Recheck Cond: ((document_number = 1210001) OR ((document_customer_acc_no)::text = '1210001'::text))"
" -> BitmapOr (cost=8.82..8.82 rows=28 width=0) (actual time=0.037..0.037 rows=0 loops=1)"
" -> Bitmap Index Scan on acc_documents2_document_number_ndx (cost=0.00..4.33 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)"
" Index Cond: (document_number = 1210001)"
" -> Bitmap Index Scan on document_customer_account_number_ndx (cost=0.00..4.48 rows=27 width=0) (actual time=0.024..0.024 rows=0 loops=1)"
" Index Cond: ((document_customer_acc_no)::text = '1210001'::text)"
"Total runtime: 0.091 ms"

Without the Explain Analyse it runs in 17ms.

[david]

I'd be interested to see what results you get setting the JDBC driver to use the V2 protocol instead of the default V3. I think adding "?protocolVersion=2" to the URL string would do the trick.

As an aside, also note that Servoy isn't shipping with the latest JDBC driver for the db versions you're running. I assume you switch out the drivers accordingly.

[swingman]

I'd be interested to see what results you get setting the JDBC driver to use the V2 protocol instead of the default V3. I think adding "?protocolVersion=2" to the URL string would do the trick.

I have been using the driver that ships with Servoy, I install each major version of Servoy into a new directory. So for 6.0.x, I have been using the driver that Servoy supplies.

I can confirm that setting

Code: Select all

"?protocolVersion=2"

cures the slowness.
The average time goes from

Code: Select all

00:00:012   2   00:00:006   Custom   SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' || to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE document_number = ?::integer OR document_customer_acc_no = ?::text ORDER BY document_date DESC OFFSET ?::integer LIMIT 26


for the typed query to

Code: Select all

00:00:002   2   00:00:001   Custom   SELECT COALESCE(to_char(document_number,'99999999'),'n/a') || ' ' || to_char(document_date,'dd/MM/YY') || ' ' || COALESCE(document_customer_acc_no,'') || ' ' || COALESCE(document_customer_name,''), document_id FROM acc_documents WHERE document_number = ? OR document_customer_acc_no = ? ORDER BY document_date DESC OFFSET ? LIMIT 26


for the untyped.

C.

[swingman]

I'd be interested to see what results you get setting the JDBC driver to use the V2 protocol instead of the default V3. I think adding "?protocolVersion=2" to the URL string would do the trick.

As an aside, also note that Servoy isn't shipping with the latest JDBC driver for the db versions you're running. I assume you switch out the drivers accordingly.

I install each new major release of in afresh folder, so I use whatever driver Servoy currently supply.

Code: Select all

"?protocolVersion=2"

I can confirm that adding this makes the slowness go away for the untyped query. It executes in 0.001 Secs.

[david]

Good news for you -- from 9.2 what's new wiki:

Prepared statements used to be optimized once, without any knowledge of the parameters' values. With 9.2, the planner will use specific plans regarding to the parameters sent (the query will be planned at execution), except if the query is executed several times and the planner decides that the generic plan is not too much more expensive than the specific plans.

You can actually test prepared statements in PGAdmin:

Code: Select all

PREPARE test (text) AS
    select * from nut_data where nutr_no = $1;
explain analyze EXECUTE test('262');

I suspect that Servoy via the JDBC driver isn't passing type at all:

Code: Select all

PREPARE test AS
    select * from nut_data where nutr_no = $1;
explain analyze EXECUTE test('262');

Your cool trick does the same thing as the first block of code and I think is more along the lines of:

Code: Select all

PREPARE test AS
    select * from nut_data where nutr_no = $1::text;
explain analyze EXECUTE test('262');

[rgansevles]

I suspect that Servoy via the JDBC driver isn't passing type at all

For custom queries (getDatasetByQuery) we don't have the type and use PreparedStatement.setObject().
I guess that the new postgres will base the type on the actual value used and not on wether setString/setTimestamp/etc is called.

For find-queries we do call setTimestamp because we know the type.

Rob