Re: Optimizing Query (Index)

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(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Chigoy, Byron T
Sent: Friday, March 07, 2008 3:19 PM
To: pgsql-novice(at)postgresql(dot)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

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.