In this section: |
To run queries on Hyperstage tables, use standard PostgreSQL syntax like in the simple request below:
psql> select [field name] from [table name];
The Hyperstage Optimizer is the primary engine used to resolve queries. While significant additions have been made to the library of supported SQL, there are cases where the query will still be executed by the PostgreSQL query engine, instead of the Hyperstage engine. In this event, query response time tends to suffer due to the fact that the PostgreSQL engine is row-oriented and therefore cannot make use of the Knowledge Grid information. In some cases, it can be too slow to be usable. For best performance, ensure your queries (and VIEWs) contain only syntax supported by the Hyperstage Optimizer.
When a query is redirected from the Hyperstage Optimizer to the PostgreSQL query engine, a warning is reported. For example:
400 rows in set, 1 warning (0.00 sec)
This will occur when functions not optimized in Hyperstage are used. If you get poor query performance, you should execute the command below to identify if a query has been directed to the PostgreSQL query engine.
After running a query, enter the following command to view any warnings:
psql> show warnings;
The following message indicates that the query was directed to PostgreSQL for processing:
1105 | Query syntax not implemented in Brighthouse, executed by PostgreSQL engine.
Important: When queries are executed on Hyperstage tables by the standard PostgreSQL engine, performance can be significantly slower than when queries are executed by Hyperstage.
You can prevent queries from being redirected to the PostgreSQL query engine by editing the brighthouse.ini file within the data directory:
AllowMySQLQueryPath=0
If the PostgreSQL query path is disabled, then the following message will be returned if the query would have been directed to PostgreSQL for processing:
The query includes syntax that is not supported by the Hyperstage Optimizer. Hyperstage suggests either restructure the query with supported syntax, or enable the PostgreSQL Query Path in the brighthouse.ini file to execute the query with reduced performance.
If you want to terminate a query executed from a client session before the query is complete, do the following:
or
If you are using a command-line PostgreSQL client, you can also press Ctrl+C to terminate the query.
| WebFOCUS |