Re: Optimizing Query (Index)

Поиск
Список
Период
Сортировка
От Obe, Regina
Тема Re: Optimizing Query (Index)
Дата
Msg-id 53F9CF533E1AA14EA1F8C5C08ABC08D20356C8FC@ZDND.DND.boston.cob
обсуждение исходный текст
Ответ на Optimizing Query (Index)  ("Chigoy, Byron T" <BTChigoy@pbsj.com>)
Список pgsql-novice
Дерево обсуждения
 
B,
 
Did you put in a compound index or do you have 2 indexes?  You may want to try a compound index and also if this is a common join, you may want to cluster both tables on that compound index.
 
Compound index looks something like this

CREATE INDEX idx_bigtable_destorig 
ON bigtable  USING btree
  (originid, destinationid);
 
Also if the compound represents a unique record, you will want to make sure you create either a unique or primary key index on that.  The
planner uses that information. For example if it is unique in your small table - you would setup the index like
CREATE UNIQUE INDEX idx_smalltable_destorig 
ON smalltable  USING btree (originid, destinationid); 
 

Which version of PostgreSQL are you using by the way?  So many changes in the planner have been made between 8 and 8.3 that it would be helpful to know this.
 
Did you vacuum analyze before hand to make sure the planner has up to date stats?
 
Hope that helps,
Regina
 
 From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Chigoy, Byron T
Sent: Friday, March 07, 2008 3:19 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Optimizing Query (Index)

Hi,

 

I have two tables; table1 has 3500 records (small table) and table2 (bigtable) has 25 million records.

 

I need to Inner Join the two tables based on two columns in each table.

 

My query reads (I have changed the literal names here for easier reading):

 

SELECT sm.recordid, sm.areaid, bg.distance

FROM smalltable sm

INNER JOIN bigtable bg ON (bg.originid=sm.originid AND bg.detinationid=sm.detinationid)

WHERE sm.somecondition>0

 

I have tried indexing the originid and destinationid columns in both tables, and indexed the somecondition column but my query is still running extraordinarily slow.  I looked at the query plan, and it seems that my query plan only references the index on the somecondition column.  I need to make this run much faster, any tips?

 

Regards,

B

В списке pgsql-novice по дате отправления: