Uploaded image for project: 'Engineering'
  1. Engineering
  2. ENG-767

SQL planning incorrectly when index contains varchar column(s) and not named containing "tree" text

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: V1.1
    • Fix Version/s: V1.2
    • Component/s: Core

      Description

      The following scenario is not planned properly. Since idx_x_y contains a varchar it will be a tree index. The execution plan is assuming a hash index will be created (it does not appear to be checking index column data types and assumes hash if "tree" is not in the index name) so it is doing sequential scanning. If I create the index as idx_x_y_tree then the proper execution plan is generated (index scan).

      DDL:

      create table t (
      (x varchar(40) not null,
      y bigint not null);

      create index idx_x_y on t(x,ts);

      SQL:

      select * from t where x = ? and y >= ? and y <= ?;

        Attachments

          Activity

            People

            • Assignee:
              izzy Mike Ismert
              Reporter:
              tcallaghan Tim Callaghan
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Zendesk Support