Hello,
We have 2 tables:
Table1 - big one (2000M+ rows):
CREATE TABLE table1 (
pk varchar PRIMARY KEY,
col varchar
);
Table2 - small one (300K rows):
CREATE TABLE table2 (
pk varchar PRIMARY KEY,
other varchar
);
Query like this work fast (~ 30sec):
SELECT table1.pk, table1.col
FROM table1
WHERE table1.pk IN ( SELECT table2.pk FROM table2 )
But query like this work quite slow (>10min):
SELECT table1.pk
FROM table1
WHERE table1.col = ? AND table1.pk IN ( SELECT table2.pk FROM table2 )
Also query below work slow:
SELECT *
FROM (
SELECT table1.pk, table1.col
FROM table1
WHERE table1.pk IN ( SELECT table2.pk FROM table2 )
) AS s
WHERE s.col = ?
Is there any HINT that can optimize query?
