I'm working on a project that must support two database engines; SQL Server and PostgreSQL.
We are using NHibernate as the ORM.
We are running into performance issues with certain queries. Using SQL Server tools we've come up with several new indexes and statistics that greatly improve performance on SQL Server. However, I'm not certain how to implement the same indexes and statistics on PostgreSQL.
Two examples are:
CREATE STATISTICS [perfStat_Answer_02] ON [dbo].[Answer] ([InclusionExpressionGroupId], [QuestionId], [AnswerId]) CREATE NONCLUSTERED INDEX [perf_Answer_01] ON [dbo].[Answer] ( [QuestionId] ASC ) INCLUDE ( [AnswerId], [InclusionExpressionGroupId], [AnswerConceptId], [Revision], [AnswerText], [AnswerOrder] ) WITH ( SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF) ON [PRIMARY]
What is the syntax for the
INCLUDEd fields in PostgreSQL, if such a feature exists?
How do we add statistics?
Reading the PostgreSQL docs, I'm not convinced that either are supported. However, I would like to know if there is any way to accomplish something similar.