Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 26 '12 at 21:26

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
Excellent, this was what I after. Thank you. –  Vern D. Jan 26 '12 at 23:15

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: http://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

share|improve this answer
    
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. –  Vern D. Jan 26 '12 at 23:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.