Hi Umberto,
You are absolutely right and I was wrong.
I was thinking that the JDBC driver would add the LIMIT (or TOP) to the query but it doesn't.
What it seems to be doing is to send the query to the backend database as-is and then only retrieve the first x rows according to your max_returned_rows setting.
This is all fine but it does mean that in a lot of use-cases the database does need to process the whole resultset instead of the limited set.
So using LIMIT or TOP in your SQL will be more efficient on those cases.
To illustrate this I created a test table with 1 million records like so:
- Code: Select all
create table test as
select
i as id,
i || ' ' || repeat('padding', 2) as a,
repeat('xxx', 500) as b
from generate_series(1,1000000) i;
Created an index on it:
- Code: Select all
create index test_id on test (id);
And made sure the query planner had all it's statistics:
- Code: Select all
ANALYZE test;
Then I ran the following method:
- Code: Select all
var _dStartTime = new Date(),
_sQuery = "select * from test WHERE id > 1000 ORDER BY a",
_ds = databaseManager.getDataSetByQuery("rss_main", _sQuery, null, 10);
// Time it took for the query with a JDBC limit
application.output("JDBC limit: " + utils.numberFormat( (new Date() - _dStartTime) / 1000, '#.###') + " seconds");
_dStartTime = new Date();
_sQuery = "select * from test WHERE id > 1000 ORDER BY a LIMIT 10";
_ds = databaseManager.getDataSetByQuery("rss_main", _sQuery, null, -1);
// Time it took for the query with a SQL limit
application.output("SQL limit: " + utils.numberFormat( (new Date() - _dStartTime) / 1000, '#.###') + " seconds");
_dStartTime = new Date();
_ds = databaseManager.getDataSetByQuery("rss_main", _sQuery, null, 10);
// Time it took for the query with a JDBC + SQL limit
application.output("JDBC + SQL limit: " + utils.numberFormat( (new Date() - _dStartTime) / 1000, '#.###') + " seconds");
I ran this method a few times to make sure all queries would use the same cached data (if any).
This is what it eventually resulted in:
- Code: Select all
JDBC limit: 8.779 seconds
SQL limit: 1.864 seconds
JDBC + SQL limit: 1.841 seconds
So you see there is a penalty when using only the max_returned_rows limiter when using larger tables.