Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sql: MAX(x),MIN(x) should inform index selection #2636

Closed
petermattis opened this issue Sep 23, 2015 · 2 comments
Closed

sql: MAX(x),MIN(x) should inform index selection #2636

petermattis opened this issue Sep 23, 2015 · 2 comments
Assignees
Milestone

Comments

@petermattis
Copy link
Collaborator

{MAX,MIN}(x) should be used to inform index selection when x is the first column of an index.

@bdarnell
Copy link
Contributor

Or when x is not the first column of an index but all preceding columns have been specified. SELECT MAX(x) FROM t WHERE y = $1 should be able to use an index on (y, x).

@petermattis
Copy link
Collaborator Author

Yes, good point.

petermattis added a commit that referenced this issue Sep 24, 2015
Compute desired ordering when aggregation functions are being used. If
there is a single MIN(k) or MAX(k) aggregate function, the desired
ordering is set to that column.

During ordering analysis for index selection, compute the prefix of the
index for which an exact match has been requested. This prefix is
ignored when determining how much of the index ordering matches the
requested ordering.

See #2636.
petermattis added a commit that referenced this issue Sep 25, 2015
Compute desired ordering when aggregation functions are being used. If
there is a single MIN(k) or MAX(k) aggregate function, the desired
ordering is set to that column.

During ordering analysis for index selection, compute the prefix of the
index for which an exact match has been requested. This prefix is
ignored when determining how much of the index ordering matches the
requested ordering.

See #2636.
petermattis added a commit that referenced this issue Sep 25, 2015
Use "a != <val>" and "a IS NOT NULL" expressions to restrict the start
of scan ranges to not include NULL values. Use "a IS NULL" expressions
to restrict the end range to stop at the end of the NULL values.

The code is thorny. I recommend concentrating on the test cases.

See #2636.
petermattis added a commit that referenced this issue Sep 28, 2015
Consider the query:

  SELECT v FROM t WHERE k = 1 ORDER BY v

If there is an index on (k, v) we know that an exact match has been
performed on k and thus the results are already ordered on v and thus no
sorting is required.

Added an additional return value to planNode.Ordering() to indicate the
prefix of the ordering for which an exact match has been performed. A
bit of code reorg allowed sortNode.wrap() to take advantage of this new
info.

See #2636.
petermattis added a commit that referenced this issue Oct 5, 2015
Apply index constraints to the WHERE clause in order to strip out
portions of the filter which are guaranteed to be true.

Add a "col IS NOT NULL" constraint to the WHERE clause if the groupNode
has a desired ordering on "col". This currently occurs if there is a
single "{MIN,MAX}(col)" aggregation function.

Limit the number of keys scanned to 1 if the desired group-by ordering
matches the index ordering and the WHERE clause expression has been
reduced to "true".

Fixes #2636.
petermattis added a commit that referenced this issue Oct 7, 2015
Apply index constraints to the WHERE clause in order to strip out
portions of the filter which are guaranteed to be true.

Add a "col IS NOT NULL" constraint to the WHERE clause if the groupNode
has a desired ordering on "col". This currently occurs if there is a
single "{MIN,MAX}(col)" aggregation function.

Limit the number of keys scanned to 1 if the desired group-by ordering
matches the index ordering and the WHERE clause expression has been
reduced to "true".

Fixes #2636.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants