PostgreSQL analog of SQL Server index(include columns)

PostgreSQL 11 supports included columns. Excerpt from Waiting for PostgreSQL 11 – Indexes with INCLUDE columns and their support in B-tree:

This patch introduces INCLUDE clause to index definition. This clause specifies a list of columns which will be included as a non-key part in the index. The INCLUDE columns exist solely to allow more queries to benefit from index-only scans. Also, such columns don't need to have appropriate operator classes. Expressions are not supported as INCLUDE columns since they cannot be used in index-only scans.

For now, only B-tree indexes support INCLUDE clause.

CREATE INDEX myindex ON mytablename (col1,col2) INCLUDE (col3,col4); 

---

EDIT:

CREATE INDEX:

[ INCLUDE ( column_name [, ...] ) ]

The optional INCLUDE clause specifies a list of columns which will be included in the index as non-key columns. A non-key column cannot be used in an index scan search qualification, and it is disregarded for purposes of any uniqueness or exclusion constraint enforced by the index. However, an index-only scan can return the contents of non-key columns without having to visit the index's table, since they are available directly from the index entry. Thus, addition of non-key columns allows index-only scans to be used for queries that otherwise could not use them.

Columns listed in the INCLUDE clause don't need appropriate operator classes; the clause can include columns whose data types don't have operator classes defined for a given access method.

Expressions are not supported as included columns since they cannot be used in index-only scans.

Currently, only the B-tree index access method supports this feature. In B-tree indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation.

CREATE INDEX myindex ON mytablename (co1l, col2, col3, col4)

PostgreSQL does not support clustered or covering indexes.

Update:

For this query, you'll need to create the suggested index indeed:

SELECT  this_.id as id0_0_,   
        this_.device_id as device2_0_0_,  
        this_.time_id as time3_0_0_,  
        this_.gps_detail_id as gps4_0_0_   
FROM    DataMessage this_   
WHERE   this_.time_id = 65536
        AND this_.device_id = 32768

CREATE INDEX ix_datamessage_time_device_id_detail ON datamessage (time_id, device_id, id, gps_detail_id)

However, your tables seem to be over-normalized to me.

You can keep year, month and day in a single INT field in your table. This will save you a join.

There might be the point of keeping DataMessage and GpsDetails in separate tables if either GpsDetails are rarely linked to the DataMessage (this is, gps_details_id is often set to NULL), or a GPS details record can be shared between multiple data messages.

It it's not, it will be better to move the GPS details into the data messages table.

Support for index-only scanning has now been added to the beta version of PostgreSQL. It means that if an index contains the columns requested in a query, it likely won't need to go to the underlying data. Index-only scanning happens automatically.

Index-only scanning is the main reason for using Included Columns. I don't think postgres (beta or otherwise) supports included columns, so the desired columns will need to be added to the end of the list of columns to index, instead.