PostgreSQL模拟的SQL Server索引 [英] PostgreSQL analog of SQL Server index

问题描述

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

  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结构星+ eav

只有一个查询

  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。而且我也在工作。也许这样的事情

  SELECT * FROM [scoutserver_data]。[dbo]。[TimeDimension] 
 INNER JOIN（[ [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 
  

任何提示welcome =）

解决方案

  CREATE INDEX myindex ON mytablename（co1l，col2，col3，col4）
  

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

更新： / p>

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

  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）
  / pre> 
 
 
但是，您的表似乎对我来说过度归一化。
 
 
 
表中的单个 INT 字段中的月份和日期。这将节省您的加入。
 
 
 
可能需要保留 DataMessage 和如果 GpsDetails 很少链接到 DataMessage （这是，  gps_details_id 通常设置为 NULL ），或者可以在多个数据消息之间共享GPS详细记录。 > 
 
 
它不是，将GPS详细信息移动到数据消息表中将是更好的。
 
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  
Will be very grateful for help.  

Alexey

Update:

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  
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
Any hints welcome =)
解决方案
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.

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