Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4527

Simple query performace is degraded between 5.4 and 5.5

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 5.5.1
    • 5.5.2
    • None
    • None
    • 2021-2, 2021-3

    Description

      There appears to be a large loss of performance between 5.4 and 5.5 (close to 100%).

      In this reproduction, the table charset is set to lating1_bin, so 5 characters ZIP does participate in extent elimination in both versions. The 5.5 elapsed time, however, appears twice that of 5.4.

      CREATE TABLE `mailing_addresses_enriched` (
      `ZIP` varchar(5) NOT NULL,
      `CarrierRoute` varchar(4) NOT NULL,
      `ZIP4` varchar(4) DEFAULT NULL,
      `WalkSequence` int(11) DEFAULT NULL,
      `StreetNumber` varchar(10) DEFAULT NULL,
      `StreetPreDirectional` varchar(2) DEFAULT NULL,
      `StreetName` varchar(28) DEFAULT NULL,
      `StreetPostDirectional` varchar(2) DEFAULT NULL,
      `StreetSuffix` varchar(4) DEFAULT NULL,
      `SecondaryUnitDesignator` varchar(4) DEFAULT NULL,
      `SecondaryUnitNumber` varchar(8) DEFAULT NULL,
      `AddressVacancyIndicator` varchar(1) DEFAULT NULL,
      `ThrowBackIndicator` varchar(1) DEFAULT NULL,
      `SeasonalDeliveryIndicator` varchar(1) DEFAULT NULL,
      `SeasonalStartSuppression` varchar(5) DEFAULT NULL,
      `SeasonalEndSuppression` varchar(5) DEFAULT NULL,
      `DNDIndicator` varchar(1) DEFAULT NULL,
      `CollegeIndicator` varchar(1) DEFAULT NULL,
      `CollegeStartSuppression` varchar(10) DEFAULT NULL,
      `CollegeEndSuppression` varchar(10) DEFAULT NULL,
      `AddressStyleFlag` varchar(1) DEFAULT NULL,
      `SimplifyAddressCount` decimal(5,0) DEFAULT NULL,
      `DropIndicator` varchar(1) DEFAULT NULL,
      `DeliveryPointUsageCode` varchar(1) DEFAULT NULL,
      `DPBCDigit` varchar(2) DEFAULT NULL,
      `DPBCCheckDigit` int(11) DEFAULT NULL,
      `UpdateDate` varchar(10) DEFAULT NULL,
      `FileReleaseDate` varchar(10) DEFAULT NULL,
      `OverrideFileReleaseDate` varchar(10) DEFAULT NULL,
      `CountyNumber` varchar(3) DEFAULT NULL,
      `CountyName` varchar(28) DEFAULT NULL,
      `CityName` varchar(28) DEFAULT NULL,
      `StateCode` varchar(2) DEFAULT NULL,
      `StateNumber` varchar(2) DEFAULT NULL,
      `CongressionalDistrictNumber` varchar(2) DEFAULT NULL,
      `OWGMIndicator` varchar(1) DEFAULT NULL,
      `RecordTypeCode` varchar(1) DEFAULT NULL,
      `AddressKey` int(11) NOT NULL,
      `AddressType` int(11) DEFAULT NULL,
      `DeliveryPointTypeCode` varchar(1) DEFAULT NULL,
      `latitude` float DEFAULT NULL,
      `longitude` float DEFAULT NULL,
      `FIPS` varchar(6) DEFAULT NULL,
      `ZIPCR` varchar(10) DEFAULT NULL,
      `PO_Box_Flag` int(11) DEFAULT NULL,
      `Secondary_Unit_Flag` int(11) DEFAULT NULL,
      `DeliveryPointUsageCode2` varchar(1) DEFAULT NULL,
      `Zip_Zip4` bigint(20) DEFAULT NULL,
      `Zip_Zip4_DPV` bigint(20) DEFAULT NULL,
      `ZZ_Street` varchar(29) DEFAULT NULL,
      `ZZD_Street_Unit` varchar(29) DEFAULT NULL,
      `Z_Street` varchar(60) DEFAULT NULL,
      `Z_Street_Unit` varchar(60) DEFAULT NULL,
      `address1` varchar(150) DEFAULT NULL,
      `address2` varchar(150) DEFAULT NULL,
      `UPPER_CityName` varchar(28) DEFAULT NULL,
      `mailing_dwelling` varchar(33) DEFAULT NULL,
      `SeasonalStartSuppression_DayofYear` int(11) DEFAULT NULL,
      `SeasonalEndSuppression_DayofYear` int(11) DEFAULT NULL,
      `CollegeStartSuppression_DayofYear` int(11) DEFAULT NULL,
      `CollegeEndSuppression_DayofYear` int(11) DEFAULT NULL,
      `Mailing_Dwelling_Description` varchar(15) DEFAULT NULL,
      `Load_Seq` int(11) DEFAULT NULL,
      `Load_Group` int(11) DEFAULT NULL,
      `CBSA` int(11) DEFAULT NULL,
      `CBSA_Description` varchar(55) DEFAULT NULL,
      `CBSA_State_Description` varchar(60) DEFAULT NULL,
      `DMA` int(11) DEFAULT NULL,
      `DMA_Name` varchar(50) DEFAULT NULL,
      `DMA_State_Name` varchar(55) DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=latin1_bin;

      SELECT `AddressKey`,
      `CountyName`,
      `StateCode`
      FROM `mailing_addresses_enriched` WHERE zip = '33776';

      Server 1: Enterprise CS 5.5.1
      Table CHAR SET latin1/latin1_bin
      5679 rows in set, 1 warning (1.780 sec)

      Get Trace

                                                          • 1. row ***************************
                                                            calGetTrace():
                                                            Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
                                                            BPS PM mailing_addresses_cistest 6432 (addresskey,cityname,statecode,zip) 0 158478 0 1.744 5679
                                                            TNS UM - - - - - - 1.724 5679

      Server 3: Enterprise CS 5.4.1
      Table CHAR SET utf8/utf8mb4
      5679 rows in set, 1 warning (0.866 sec)

      Get Trace

                                                          • 1. row ***************************
                                                            calGetTrace():
                                                            Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
                                                            BPS PM mailing_addresses_cis 4343 (addresskey,cityname,statecode,zip) 0 109325 49152 0.851 5679
                                                            TNS UM - - - - - - 0.828 5679

      Attachments

        Issue Links

          There are no Sub-Tasks for this issue.

          Activity

            People

              dleeyh Daniel Lee (Inactive)
              gdorman Gregory Dorman (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.