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

magaldima picture magaldima  ยท  3Comments

richardanaya picture richardanaya  ยท  3Comments

lwsanty picture lwsanty  ยท  4Comments

awoods187 picture awoods187  ยท  3Comments

otan picture otan  ยท  4Comments