Is it possible not to full scan table1 for ’table1.col = ?’, but do this check only on subset table1.pk IN (…)?

On 19 Jun 2019, at 23:31, Vincent Poon <vincentpoon@apache.org> wrote:

'table1.col = ?' will be a full table scan of table1 unless you have a secondary index on table.col
Check the explain plan to see if it's working as expected

On Wed, Jun 19, 2019 at 7:43 AM Alexander Batyrshin <0x62ash@gmail.com> wrote:
 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?