db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
Date Mon, 02 Feb 2009 09:07:59 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12669532#action_12669532

Dag H. Wanvik commented on DERBY-3505:

In PredicateList#orderUsefulPredicates, restriction predicates are analyzed and possibly converted
into start/stop keys for index scans (cf. calls to markStartKey/markStopkey).

I looked at an example where we have a table with two ints, i and j with a primary key on
i giving rise to an index. The query: 'select * from t where i < 5' converted the literal
5 into a stop key when scanning the index.

This technique allows predicates to effectively limit how many rows are read when applicable.
It seems we need  some similar analysis/logic for converting predicates on ROW_NUMBER to
an efficient "LIMIT". This seems independent of whether the underlying window is ordered or
the logic would be applied the language level, not at the store level.

> Current implementation of ROW_NUMBER() window function does not stop execution once criteria
is met
> ---------------------------------------------------------------------------------------------------
>                 Key: DERBY-3505
>                 URL: https://issues.apache.org/jira/browse/DERBY-3505
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>            Reporter: Thomas Nielsen
> Using ROW_NUMBER() to limit the number of rows returned is typically done with the following
>     SELECT * FROM (
>       SELECT row_number() over () as r, t.* FROM T
>     ) AS tmp WHERE r <= 3;
> The query plan shows that the restriction is perfomed in the outermost ProjectRestrictResultSet,
and that it actually sees all rows in the table.
>   ******* Project-Restrict ResultSet (1):
>   Number of opens = 1
>   Rows seen = 1280
>   Rows filtered = 1277
>   restriction = true
> In this case all 1280 rows are read from disk, and passed up the ResultSet chain. 1277
rows are filtered out so that, in the end, we only return 3 rows. 
> Ideally the execution should stop after pulling only 3 rows through the chain.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message