0

There are the following scenarios:

Use PG to execute the query as follows:

Select count(*) from t where DATETIME >'2018-07-27 10.12.12.000000' and DATETIME < '2018-07-28 10.12.12.000000'

It returns 22 indexes with rapid execution.

The query condition has "="

Select count(*) from t where DATETIME >='2018-07-27 10.12.12.000000' and DATETIME <= '2018-07-28 10.12.12.000000'

It return 22 indexes which cost 20s.

I find that the query without “=” choose index scan, however, the query with “=” partly choose table scan.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Evak124
  • 1

1 Answers1

0

According to your question:

  1. The current indexing mechanism is that the optimizer matches the first available index, which means that the query will first select the first index created, and the choice of index depends on the order in which the index is created. In the case of an index, the query will take the index scan first.

  2. Make sure that the nodes on each data group contain the index, otherwise the unindexed data nodes will take the table scan.

  3. Execute analyze optimization query. Analyze is a new feature of SequoiaDB v3.0. It is mainly used to analyze collections, index data, and collect statistical information, and provide an optimal query algorithm to determine either index or table scan. Analyze specific usage reference: http://doc.sequoiadb.com/cn/index-cat_id-1496923440-edition_id-300

  4. View the access plan by find.explain() to view the query cost

SequoiaDB
  • 11
  • 1