Cockroach: opt: support index hints on DELETE/UPDATE

Created on 10 Jul 2019  ยท  1Comment  ยท  Source: cockroachdb/cockroach

Today, we silently ignore index hints on UPDATE/DELETE:

CREATE TABLE x (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX b_idx (b),
  INDEX c_idx (c)
)
----

opt
DELETE FROM
    x@primary
WHERE
    a = 1 AND b = 2 AND c = 3
----
delete x
 โ”œโ”€โ”€ columns: <none>
 โ”œโ”€โ”€ fetch columns: a:4(int) b:5(int) c:6(int)
 โ”œโ”€โ”€ cardinality: [0 - 0]
 โ”œโ”€โ”€ side-effects, mutations
 โ””โ”€โ”€ select
      โ”œโ”€โ”€ columns: a:4(int!null) b:5(int!null) c:6(int!null)
      โ”œโ”€โ”€ cardinality: [0 - 1]
      โ”œโ”€โ”€ key: ()
      โ”œโ”€โ”€ fd: ()-->(4-6)
      โ”œโ”€โ”€ index-join x
      โ”‚    โ”œโ”€โ”€ columns: a:4(int!null) b:5(int) c:6(int)
      โ”‚    โ”œโ”€โ”€ cardinality: [0 - 1]
      โ”‚    โ”œโ”€โ”€ key: ()
      โ”‚    โ”œโ”€โ”€ fd: ()-->(4-6)
      โ”‚    โ””โ”€โ”€ scan x@b_idx
      โ”‚         โ”œโ”€โ”€ columns: a:4(int!null) b:5(int!null)
      โ”‚         โ”œโ”€โ”€ constraint: /5/4: [/2/1 - /2/1]
      โ”‚         โ”œโ”€โ”€ cardinality: [0 - 1]
      โ”‚         โ”œโ”€โ”€ key: ()
      โ”‚         โ””โ”€โ”€ fd: ()-->(4,5)
      โ””โ”€โ”€ filters
           โ””โ”€โ”€ c = 3 [type=bool, outer=(6), constraints=(/6: [/3 - /3]; tight), fd=()-->(6)]

We should use the index requested by the hint or explicitly error out when one is provided (preferably the former)

A-sql-optimizer C-wishlist E-starter

Most helpful comment

We should extend index hints to work here.

>All comments

We should extend index hints to work here.

Was this page helpful?
0 / 5 - 0 ratings

Related issues

cockroach-teamcity picture cockroach-teamcity  ยท  56Comments

gigatexal picture gigatexal  ยท  30Comments

danhhz picture danhhz  ยท  84Comments

benesch picture benesch  ยท  140Comments

bdarnell picture bdarnell  ยท  37Comments