PostgreSQL类似的SQL Server索引（包括列） [英] PostgreSQL analog of SQL Server index(include columns)

问题描述

尝试在PostgreSQL上重新创建我的SQL Server数据库。一切正常，除了我找不到如何重新创建此索引：

Trying to recreate my SQL Server database on PostgreSQL. Everything is ok except I can't find how to recreate this index:

USE [mytablename]  
GO  
CREATE NONCLUSTERED INDEX [myindex]  
ON [dbo].[mytablename] ([col1],[col2])  
INCLUDE ([col3],[col4])  
GO  

非常感谢您的帮助。

Alexey

更新：

http://img38.imageshack.us/img38/1071/89013974。 png 这是db结构star + eav

只有一个查询

http://img38.imageshack.us/img38/1071/89013974.png here is db structure star+eav
there is only one query

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 [scoutserver_data].[dbo].[DataMessage]  this_   
WHERE this_.time_id = 65536 and this_.device_id = 32768  

也许不是最佳atm。我也正在努力。也许是这样的

Maybe it is not optimal atm. And im working on it also. Maybe something like this

SELECT * FROM [scoutserver_data].[dbo].[TimeDimension]   
  INNER JOIN ([scoutserver_data].[dbo].[DeviceDimension]   
  INNER JOIN  [scoutserver_data].[dbo].[DataMessage]   
ON [DeviceDimension].[device_id] =[DataMessage].[device_id])  
ON [TimeDimension].[time_id] = [DataMessage].[time_id]  
WHERE DeviceDimension.serial_id='2' AND TimeDimension.Day=15 AND TimeDimension.Year=2009

任何提示欢迎=）

推荐答案

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

PostgreSQL 不支持聚集索引或覆盖索引。

PostgreSQL does not support clustered or covering indexes.

更新：

对于此查询，您确实需要创建建议的索引：

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.

您可以在表的单个 INT 字段中保留年，月和日。

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

可能会保留 DataMessage 和如果 GpsDetails 很少链接到 DataMessage （即， gps_details_id 通常设置为 NULL ），或者GPS细节记录可以在多条数据消息之间共享。

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.

不是，最好将GPS详细信息移到数据消息表中。

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

这篇关于PostgreSQL类似的SQL Server索引（包括列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！