Cost-based optimizer

When Manticore executes a fullscan query, it can either use plain scan to check every document against the filters, or it can use additional data and/or algorithms to speed up the query execution. To decide which approach to take, Manticore uses a query cost-based optimizer (“CBO” also known as “query optimizer”).

The CBO may decide to replace one or more query filters with one of the following entities if it determines that it will improve performance:

  1. A docid index, which uses a special docid-only secondary index stored in files with the .spt extension. In addition to improving filters on document ids, the docid index is also used to speed up document id to row id lookups, and to speed up the application of large killlists on daemon startup.
  2. A columnar scan, which uses columnar storage and can only be used on a columnar attribute. It still scans every value and tests it against the filter, but it is heavily optimized and is usually faster than the default approach.
  3. Secondary indexes, which are generated for all attributes by default. They use the PGM index together with Manticore’s built-in inverted index to retrieve the list of row ids corresponding to a value or range of values. The secondary indexes are stored in files with the .spidx extension.

The optimizer estimates the cost of each execution path using different attribute statistics, including:

  1. Information on the data distribution within an attribute (histograms, stored in .sphi files). Histograms are generated automatically when data is indexed and are the main source of information for the CBO.
  2. Information from PGM (secondary indexes), which is used to estimate the number of document lists to read. This helps to estimate doclist merge performance and to choose the correct merge algorithm (priority queue merge or bitmap merge).
  3. Columnar encoding statistics, which are used to estimate columnar data decompression performance.
  4. A columnar min-max tree. The CBO uses histograms to estimate the number of documents left after the filter was applied, but it also needs to estimate how many documents the filter had to process. For columnar attributes, partial evaluation of the min-max tree is used for that purpose.

The optimizer calculates the execution cost for every filter used in a query. Because certain filters can be replaced with several different entities (e.g., for a document id, Manticore can use a plain scan, a docid index lookup, a columnar scan (if the document id is columnar), and a secondary index), the optimizer evaluates every available combination. Note that there is a maximum limit of 1024 combinations.

To estimate query execution costs, the optimizer calculates the estimated costs of the most significant operations that are performed when the query is executed. It uses preset constants to represent the cost of each operation.

The optimizer compares the costs of each execution path and chooses the path with the lowest cost to execute the query.

Another thing to consider is multithreaded query execution (when pseudo_sharding is enabled). The CBO knows that some queries can be executed in multiple threads and takes that into account. The CBO favors smaller query execution times (i.e., latency) over throughput. For example, if a query using a columnar scan can be executed in multiple threads (and occupy multiple CPU cores) and is faster than a query executed in a single thread using secondary indexes, multithreaded execution will be preferred.

Queries using secondary indexes and docid indexes always run in a single thread, as benchmarks show that there is little to no benefit in making them multithreaded.

Currently, the optimizer only uses CPU costs and does not consider memory or disk usage.

Updating table schema

Updating table schema in RT mode

  1. ALTER TABLE table ADD COLUMN column_name [{INTEGER|INT|BIGINT|FLOAT|BOOL|MULTI|MULTI64|JSON|STRING|TIMESTAMP|TEXT [INDEXED [ATTRIBUTE]]}] [engine='columnar']
  3. ALTER TABLE table DROP COLUMN column_name

It supports adding one field at a time for RT tables. Supported data types are:

  • int - integer attribute
  • timestamp - timestamp attribute
  • bigint - big integer attribute
  • float - float attribute
  • bool - boolean attribute
  • multi - multi-valued integer attribute
  • multi64 - multi-valued bigint attribute
  • json - json attribute
  • string / text attribute / string attribute - string attribute
  • text / text indexed stored / string indexed stored - full-text indexed field with original value stored in docstore
  • text indexed / string indexed - full-text indexed field, indexed only (the original value is not stored in docstore)
  • text indexed attribute / string indexed attribute - full text indexed field + string attribute (not storing the original value in docstore)
  • text stored / string stored - the value will be only stored in docstore, not full-text indexed, not a string attribute
  • adding engine='columnar' to any attribute (except for json) will make it stored in the columnar storage

Important notes:

  • ❗It’s recommended to backup table files before ALTERing it to avoid data corruption in case of a sudden power interruption or other similar issues.
  • Querying a table is impossible while a column is being added.
  • Newly created attribute’s values are set to 0.
  • ALTER will not work for distributed tables and tables without any attributes.
  • DROP COLUMN will fail if a table has only one field.
  • When dropping a field which is both a full-text field and a string attribute the first ALTER DROP drops the attribute, the second one drops the full-text field.
  • Adding/dropping full-text field is only supported in the RT mode.
  • Example

Example

  2. mysql> desc rt;
  3. +------------+-----------+
  4. | Field      | Type      |
  5. +------------+-----------+
  6. | id         | bigint    |
  7. | text       | field     |
  8. | group_id   | uint      |
  9. | date_added | timestamp |
  10. +------------+-----------+
  12. mysql> alter table rt add column test integer;
  14. mysql> desc rt;
  15. +------------+-----------+
  16. | Field      | Type      |
  17. +------------+-----------+
  18. | id         | bigint    |
  19. | text       | field     |
  20. | group_id   | uint      |
  21. | date_added | timestamp |
  22. | test       | uint      |
  23. +------------+-----------+
  25. mysql> alter table rt drop column group_id;
  27. mysql> desc rt;
  28. +------------+-----------+
  29. | Field      | Type      |
  30. +------------+-----------+
  31. | id         | bigint    |
  32. | text       | field     |
  33. | date_added | timestamp |
  34. | test       | uint      |
  35. +------------+-----------+
  37. mysql> alter table rt add column title text indexed;
  39. mysql> desc rt;
  40. +------------+-----------+------------+
  41. | Field      | Type      | Properties |
  42. +------------+-----------+------------+
  43. | id         | bigint    |            |
  44. | text       | text      | indexed    |
  45. | title      | text      | indexed    |
  46. | date_added | timestamp |            |
  47. | test       | uint      |            |
  48. +------------+-----------+------------+
  50. mysql> alter table rt add column title text attribute;
  52. mysql> desc rt;
  53. +------------+-----------+------------+
  54. | Field      | Type      | Properties |
  55. +------------+-----------+------------+
  56. | id         | bigint    |            |
  57. | text       | text      | indexed    |
  58. | title      | text      | indexed    |
  59. | date_added | timestamp |            |
  60. | test       | uint      |            |
  61. | title      | string    |            |
  62. +------------+-----------+------------+
  64. mysql> alter table rt drop column title;
  66. mysql> desc rt;
  67. +------------+-----------+------------+
  68. | Field      | Type      | Properties |
  69. +------------+-----------+------------+
  70. | id         | bigint    |            |
  71. | text       | text      | indexed    |
  72. | title      | text      | indexed    |
  73. | date_added | timestamp |            |
  74. | test       | uint      |            |
  75. +------------+-----------+------------+
  76. mysql> alter table rt drop column title;
  78. mysql> desc rt;
  79. +------------+-----------+------------+
  80. | Field      | Type      | Properties |
  81. +------------+-----------+------------+
  82. | id         | bigint    |            |
  83. | text       | text      | indexed    |
  84. | date_added | timestamp |            |
  85. | test       | uint      |            |
  86. +------------+-----------+------------+

Updating table FT settings in RT mode

  1. ALTER TABLE table ft_setting='value'[, ft_setting2='value']

You can also use ALTER to modify full-text settings of your table in the RT mode. Just remember that it doesn’t affect existing documents, it only affects new ones. Take a look at the example where we:

  • create a table with a full-text field and charset_table that allows only 3 searchable characters: a, b and c.
  • then we insert document ‘abcd’ and find it by query abcd, the d just gets ignored since it’s not in the charset_table array
  • then we understand, that we want d to be searchable too, so we add it with help of ALTER
  • but the same query where match('abcd') still says it searched by abc, because the existing document remembers previous contents of charset_table
  • then we add another document abcd and search by abcd again
  • now it finds the both documents and show meta says it used two keywords: abc (to find the old document) and abcd (for the new one).
  • Example

Example

  1. mysql> create table rt(title text) charset_table='a,b,c';
  3. mysql> insert into rt(title) values('abcd');
  5. mysql> select * from rt where match('abcd');
  6. +---------------------+-------+
  7. | id                  | title |
  8. +---------------------+-------+
  9. | 1514630637682688054 | abcd  |
  10. +---------------------+-------+
  12. mysql> show meta;
  13. +---------------+-------+
  14. | Variable_name | Value |
  15. +---------------+-------+
  16. | total         | 1     |
  17. | total_found   | 1     |
  18. | time          | 0.000 |
  19. | keyword[0]    | abc   |
  20. | docs[0]       | 1     |
  21. | hits[0]       | 1     |
  22. +---------------+-------+
  24. mysql> alter table rt charset_table='a,b,c,d';
  25. mysql> select * from rt where match('abcd');
  26. +---------------------+-------+
  27. | id                  | title |
  28. +---------------------+-------+
  29. | 1514630637682688054 | abcd  |
  30. +---------------------+-------+
  32. mysql> show meta
  33. +---------------+-------+
  34. | Variable_name | Value |
  35. +---------------+-------+
  36. | total         | 1     |
  37. | total_found   | 1     |
  38. | time          | 0.000 |
  39. | keyword[0]    | abc   |
  40. | docs[0]       | 1     |
  41. | hits[0]       | 1     |
  42. +---------------+-------+
  44. mysql> insert into rt(title) values('abcd');
  45. mysql> select * from rt where match('abcd');
  46. +---------------------+-------+
  47. | id                  | title |
  48. +---------------------+-------+
  49. | 1514630637682688055 | abcd  |
  50. | 1514630637682688054 | abcd  |
  51. +---------------------+-------+
  53. mysql> show meta;
  54. +---------------+-------+
  55. | Variable_name | Value |
  56. +---------------+-------+
  57. | total         | 2     |
  58. | total_found   | 2     |
  59. | time          | 0.000 |
  60. | keyword[0]    | abc   |
  61. | docs[0]       | 1     |
  62. | hits[0]       | 1     |
  63. | keyword[1]    | abcd  |
  64. | docs[1]       | 1     |
  65. | hits[1]       | 1     |
  66. +---------------+-------+

Updating table FT settings in plain mode

  1. ALTER TABLE table RECONFIGURE

ALTER can also reconfigure an RT table in the plain mode), so that new tokenization, morphology and other text processing settings from the configuration file take effect for new documents. Note, that the existing document will be left intact. Internally, it forcibly saves the current RAM chunk as a new disk chunk and adjusts the table header, so that new documents are tokenized using the updated full-text settings.

  • Example

Example

  1. mysql> show table rt settings;
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | settings      |       |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)
  9. mysql> alter table rt reconfigure;
  10. Query OK, 0 rows affected (0.00 sec)
  12. mysql> show table rt settings;
  13. +---------------+----------------------+
  14. | Variable_name | Value                |
  15. +---------------+----------------------+
  16. | settings      | morphology = stem_en |
  17. +---------------+----------------------+
  18. 1 row in set (0.00 sec)

Rebuild secondary index

  1. ALTER TABLE table REBUILD SECONDARY

ALTER can also be used to rebuild secondary indexes in a given table. Sometimes a secondary index can be disabled for the whole table or for one/multiple attributes in it:

  • On UPDATE of an attribute: in this case its secondary index gets disabled.
  • In case Manticore loads a table with old formatted secondary indexes: in this case secondary indexes will be disabled for the whole table.

ALTER TABLE table REBUILD SECONDARY rebuilds secondary indexes from attribute data and enables them again.

  • Example

Example

  1. ALTER TABLE rt REBUILD SECONDARY;

Response

  1. Query OK, 0 rows affected (0.00 sec)

Functions