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 <vincentp...@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 <0x62...@gmail.com > <mailto:0x62...@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 <http://table1.pk/>, table1.col > FROM table1 > WHERE table1.pk <http://table1.pk/> IN ( SELECT table2.pk <http://table2.pk/> > FROM table2 ) > > But query like this work quite slow (>10min): > SELECT table1.pk <http://table1.pk/> > FROM table1 > WHERE table1.col = ? AND table1.pk <http://table1.pk/> IN ( SELECT table2.pk > <http://table2.pk/> FROM table2 ) > > Also query below work slow: > SELECT * > FROM ( > SELECT table1.pk <http://table1.pk/>, table1.col > FROM table1 > WHERE table1.pk <http://table1.pk/> IN ( SELECT table2.pk > <http://table2.pk/> FROM table2 ) > ) AS s > WHERE s.col = ? > > Is there any HINT that can optimize query?
