How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

Question

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.

Answer 1

I don't know what CREATE STATISTICS does, but statistics for the optimizer are collected using the ANALZYE command when autovacuum is running - which is turned on by default.

Statistics are always collected for all columns, no need to turn it on specifically.

You can control the level of details collected for the statistics on a per-column basis using ALTER TABLE ... ALTER COLUMN column SET STATISTICS integer.

An index in PostgreSQL is always non-clustered, so I'd assume that the above index maps to a regular index on the QuestionId column.

Not sure about the INCLUDE part. I assume this is to support index only retrievals if that index is chosen by the optimizer. As PostgreSQL does not yet have an index-only retrieval, there is no equivalent technique there.

• Vern D.: Excellent, this was what I after. Thank you.

Answer 2

I can't comment on your post so I guess I have to submit this as an answer. Please feel free to convert it for me. Here's a nice writeup of some of the differences between the two: https://stackoverflow.com/questions/4630891/tips-for-sql-server-developer-picking-up-postgresql

With pg, you can dictate the indexing strategy. Here is the first page to a lot of info on indexes in pg. http://www.postgresql.org/docs/9.1/static/indexes-types.html

• Vern D.: Thanks for these links, between the yours and horse's answers, I was able to get much of the performance boost I was after. Thank you.