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

[YSQL] IN query should use index #1554

Closed
frozenspider opened this issue Jun 17, 2019 · 3 comments
Closed

[YSQL] IN query should use index #1554

frozenspider opened this issue Jun 17, 2019 · 3 comments
Assignees
Labels
kind/enhancement This is an enhancement of an existing feature
Milestone

Comments

@frozenspider
Copy link
Contributor

frozenspider commented Jun 17, 2019

Given the schema:

CREATE TABLE elements (id int PRIMARY KEY, val int, grp int);
CREATE INDEX elements_idx ON elements (grp) INCLUDE (val);

EXPLAIN shows that only the first of the following cases involves index:

postgres=# EXPLAIN SELECT val FROM elements WHERE grp = 3;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Only Scan using elements_idx on elements  (cost=0.00..4.11 rows=1 width=4)
   Index Cond: (grp = 3)
postgres=# EXPLAIN SELECT val FROM elements WHERE grp IN (1,2,3);
                           QUERY PLAN
-----------------------------------------------------------------
 Foreign Scan on elements  (cost=0.00..103.75 rows=1000 width=4)
   Filter: (grp = ANY ('{1,2,3}'::integer[]))
postgres=# EXPLAIN SELECT val FROM elements WHERE grp > 1 AND grp < 2;
                           QUERY PLAN
-----------------------------------------------------------------
 Foreign Scan on elements  (cost=0.00..105.00 rows=1000 width=4)
   Filter: ((grp > 1) AND (grp < 2))

(BETWEEN, etc. are also worth checking)

For reference, in Postgres they all have plans like this:

fs=# EXPLAIN SELECT val FROM elements WHERE grp IN (1,2,3);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on elements  (cost=4.69..15.12 rows=31 width=4)
   Recheck Cond: (grp = ANY ('{1,2,3}'::integer[]))
   ->  Bitmap Index Scan on elements_idx  (cost=0.00..4.68 rows=31 width=0)
         Index Cond: (grp = ANY ('{1,2,3}'::integer[]))
@frozenspider frozenspider added the kind/enhancement This is an enhancement of an existing feature label Jun 17, 2019
@frozenspider
Copy link
Contributor Author

frozenspider commented Jun 17, 2019

Related issue: #995

@ndeodhar
Copy link
Contributor

Duplicate of #1223 and #995

@ndeodhar ndeodhar changed the title [YSQL] Range and IN queries should use index [YSQL] IN query should use index Jun 20, 2019
@ndeodhar ndeodhar added this to the v2.0 milestone Jun 20, 2019
@bmatican
Copy link
Contributor

Talked to @m-iancu earlier, maybe let's hold on this for a while @JDNdeveloper, until we realize if there's a bottleneck on being able to have more folks working on these types of optimizations.

frozenspider added a commit to yugabyte/jepsen that referenced this issue Jun 24, 2019
The following tests were adopted:
* `bank-multitable`
* `set`
* `set-index` - disabled because index wasn't actually used - see yugabyte/yugabyte-db#1554
* `long-fork`
* `single-key-acid`
* `multi-key-acid`
@ndeodhar ndeodhar assigned rajukumaryb and unassigned JDNdeveloper Jul 16, 2019
aphyr pushed a commit to jepsen-io/jepsen that referenced this issue Jul 31, 2019
The following tests were adopted:
* `bank-multitable`
* `set`
* `set-index` - disabled because index wasn't actually used - see yugabyte/yugabyte-db#1554
* `long-fork`
* `single-key-acid`
* `multi-key-acid`
rajukumaryb added a commit that referenced this issue Aug 23, 2019
Summary:
  Usage: SELECT ... FROM ... WHERE range_col IN (val1, val2, ...)

  Before:       Client --------- (val1, val2, ...) ---------> Postgres ----------------------------------------------> DocDB
  After:        Client --------- (val1, val2, ...) ---------> Postgres --------- ascending(val1, val2, ...) ---------> DocDB

Test Plan:
  ./yb_build.sh debug --scb --java-test org.yb.pgsql.TestPgInequality#testInequalityPredicatePushdownTwoRangeColumnsPerformance

Reviewers: mihnea, neil

Reviewed By: neil

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D7032
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
kind/enhancement This is an enhancement of an existing feature
Projects
None yet
Development

No branches or pull requests

5 participants