Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-40553

Optimizer Trace shows "unprintable_geometry_value" for GIS ranges

    XMLWordPrintable

Details

    Description

      CREATE TABLE t1 (
        g GEOMETRY NOT NULL,
        SPATIAL KEY (g)
      );
       
      insert into t1 
      select ST_GeomFromText(CONCAT('POINT(', seq,' ', seq ,')')) from seq_1_to_1000;
      

      set optimizer_trace=1;
      explain
      select * from t1 
      where ST_Overlaps(g, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));
      select * from information_schema.optimizer_trace;
      

      Shows

                          "range_scan_alternatives": [
                            {
                              "index": "g",
                              "ranges": ["(g) (unprintable_geometry_value)"],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 1,
                              "cost": 0.00424968,
                              "chosen": true
                            }
      

      Optimizer context would also show

            "multi_range_read_info_const_calls": [
              {
                "index_name": "g",
                "ranges": ["(g) (unprintable_geometry_value)"],
                "num_rows": 53,
                "cost": {
                  "avg_io_cost": 0.0002048,
                  "cpu_cost": 0,
                  "comp_cost": 0.001696,
                  "copy_cost": 0,
                  "limit_cost": 0,
                  "setup_cost": 0.00079112,
                  "index_cost_io": 4,
                  "index_cost_cpu": 0.00607372,
                  "row_cost_io": 20,
                  "row_cost_cpu": 0.04570932
                },
      

      which will make context replay unreliable (as all ranges become the same).

      When fixing it, we could print the WKT form of the AABB used as index tuple:

      ST_Envelope(ST_GeomFromText('LINESTRING(0 0, 10 5)'))
      

      Attachments

        Activity

          People

            bsrikanth Srikanth Bondalapati
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - 0d
                0d
                Remaining:
                Remaining Estimate - 2d
                2d
                Logged:
                Time Spent - Not Specified
                Not Specified

                Git Integration

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