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.

Sign up for GitHub

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

Jump to bottom

[YSQL] Push down SELECT COUNT(1) #4276

Closed
ndeodhar opened this issue Apr 22, 2020 · 0 comments
Closed

[YSQL] Push down SELECT COUNT(1) #4276

ndeodhar opened this issue Apr 22, 2020 · 0 comments
Assignees
@frozenspider
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature
Projects
YSQL

Comments

@ndeodhar
Copy link
Contributor

Today, queries like select count(*) from table or select count(column) from table get pushed down to docdb.
However, select count(1) from table results in a table scan and results are aggregated in postgres. We should push down this query as well.
@ndeodhar ndeodhar added the area/ysql Yugabyte SQL (YSQL) label Apr 22, 2020
@ndeodhar ndeodhar added this to To do in YSQL via automation Apr 22, 2020
@frozenspider frozenspider added the kind/enhancement This is an enhancement of an existing feature label Apr 23, 2020
@frozenspider frozenspider changed the title [YSQL] Push down select count(1) [YSQL] Push down SELECT COUNT(1) May 22, 2020
@frozenspider frozenspider moved this from To do to In progress in YSQL May 29, 2020
frozenspider added a commit that referenced this issue Jun 2, 2020
@frozenspider
#4276: [YSQL] Push down SELECT <aggregate>(<const>) to DocDB
6ac0772 
Summary:
# Problem description
While `SELECT COUNT(*)` and `SELECT COUNT(col_name)` queries were pushed down to DocDB, `SELECT COUNT(1)` was resulting in a full scan with analysis on YSQL side.

# What changed

* Allowed pusing down `Const` arguments in aggregates (in addition to `Var`s) - including `NULL` constant. This affects all aggregate functions, not just `COUNT`.
* Previously, `COUNT(*)` was sent to DocDB as `COUNT(NULL)`. Since `NULL` is no longer a placeholder value, `COUNT(*)` is transformed to `COUNT(0)` instead,
* (In the light of the change above) Changed semantics of how DocDB handles `COUNT(NULL)` - instead of counting rows it now guarantees to return 0. [To be fair, I'm not sure why original postgres code doesn't immediately evaluate this to 0]
* Slightly tweaked `TestPgPushdown` - extracted `SelectPushdownTester` as a superclass of `InClausePushdownTester`, implemented `AggregatePushdownTester` extending this superclass.

Test Plan:
ybd --java-test org.yb.pgsql.TestPgRegressTypesMisc
ybd --java-test org.yb.pgsql.TestPgPushdown

Reviewers: neha, mihnea, neil

Reviewed By: mihnea, neil

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D8581
YSQL automation moved this from In progress to Done Jun 2, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@frozenspider frozenspider

Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature
Projects
YSQL
  
Done
Milestone
No milestone
Development

No branches or pull requests
2 participants
@frozenspider @ndeodhar