Query Planner Hints
Last updated
Was this helpful?
Last updated
Was this helpful?
Another PostgreSQL specific feature (probably the most popular one among the ) is giving the PostgreSQL planner some hints on how you prefer the query to be executed.
E.g. if your table contains data with very different cardinality of a particular field, and you see that PostgreSQL runs a seqscan sometimes, you may try to lower the seqscan priority:
The planner settings like do not fully prevent sequential scan, but they greatly reduce its probability, in case you know what you're doing.
If your query is intentionally a sequential scan or just works with lots of rows in the table, does , then you may also want to increase work_mem
for it, to lower the chance of using temporary files:
Of course, Ent Framework changes the GUC settings during the query execution period only, and guarantees that they are restored after. Internally, SET LOCAL ... RESET
are used for that (running within the same transaction of the multi-query).
Also, batching is done with respect to the planner hints. Only the SELECT queries having the same set of hints are potentially batched together with UNION ALL
clause, as explained in Ent API: select() by Expression article.
There is a special hint with ""
(empty string) name. It allows to raw-prepend an arbitrary string in front of the query that Ent Framework sends to the database.
The resulting SQL multi-query (one transaction) sent to the server will then look like:
Since it's a multi-query, there will be only one round-trip to the server and one transaction.
The use of raw-prepend hint turns off Ent Framework's SELECT query batching. I.e. your query is guaranteed to run alone, not in a UNION ALL
construction.
See for the full list of settings you can customize per query (notice that not all of them allow per-query changing: some require a database restart).
The raw-prepend hint is useful when e.g. working with PostgreSQL extensions like . This extension enables a way greater level of planning customization. You can even tell PostgreSQL, which exact index it must use for your query: