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