Обсуждение: 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
CREATE INDEX idx_bigtable_destorig
(originid, destinationid);
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