phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vincent Poon <vincentp...@apache.org>
Subject Re: Query optimization
Date Fri, 21 Jun 2019 20:23:56 GMT
I wasn't able to repro this from sqlline.  The query seems to setup the
correct scan with two filters: skip-scan and the column value filter.
So I don't know why the join without the filter is fast for you, but with
the filter it's slow.
Anything else special about your tables?  e.g. indexes, stats...

On Wed, Jun 19, 2019 at 6:18 PM Alexander Batyrshin <0x62ash@gmail.com>
wrote:

> 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?
>>
>
>

Mime
View raw message