phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexander Batyrshin <0x62...@gmail.com>
Subject Re: Query optimization
Date Thu, 20 Jun 2019 01:18:46 GMT
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 <mailto: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 <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?


Mime
View raw message