You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Alexander Batyrshin <0x...@gmail.com> on 2019/06/19 14:43:33 UTC
Query optimization
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?
Re: Query optimization
Posted by Vincent Poon <vi...@apache.org>.
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 <0x...@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 <vi...@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 <0x...@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?
>>
>
>
Re: Query optimization
Posted by Alexander Batyrshin <0x...@gmail.com>.
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 <vi...@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 <ma...@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?
Re: Query optimization
Posted by Vincent Poon <vi...@apache.org>.
'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 <0x...@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?
>