IQ Server - Query performance issue with <

Author

Query performance issue with <

Alexander Sinitsin

2005-08-16, 1:27 pm

Hello, All!

Table is huge (>18 000 000 000 rows).
This query works well (less than a second):

select Count(*) from dba.Table1 where F1 = 1234567

But this one takes a long time (~15min):

select Count(*) from dba.Table1 where F1 = 1234567 and F2 < 250

This one works well:

select Count(*) from dba.Table1 where F1 = 1234567 and F2 = 250

Execution plan looks good, fields indexed properly. Can I workaround this
issue?
------
Query Plan:
0 #03: Root
Child Node 1: #01
Generated Result Rows: 1
Estimated Result Rows: 1
Query_Name: q1_2
User Name: testuser
Est. Temp Space Used (Mb): 0.2
Act. Temp Space Used (Mb): 0.1
Effective Number of Users: 1
Number of CPUs: 4
Max_Cartesian_Result
: 200000000
Query_Temp_Space_Lim
it: 0
Time of Cursor Creation : 2005-08-16 07:36:46.434730
Time of First Prepare : 2005-08-16 07:36:46.609660
Time of First Prepare Return: 2005-08-16 07:49:22.389243
Time of First Fetch Call : 2005-08-16 07:49:22.389310
Time of First Fetch Return : 2005-08-16 07:49:22.389327
Time of Last Fetch : 2005-08-16 07:49:22.389482
Time of Last Complete : 2005-08-16 07:50:05.662502
Elapsed time : 0:13:19.052842
Output Vector: 1 entries (8 data bytes)
Output 1: COUNT(*)
Output 1 Data Type: unsigned bigint (20, 0)
Output 1 Base Distincts: 1
1 #01: Aggregation Leaf
Generated Result Rows: 1
Estimated Result Rows: 1
Table Name: Table1
Valid Grouping Algorithms: Vertical, Hash, Sort
Table Row Count: 18374401458
Time of First Prepare : 2005-08-16 07:36:46.609666
Time of First Prepare Return: 2005-08-16 07:49:22.389233
Time of First Fetch Call : 2005-08-16 07:49:22.389314
Time of First Fetch Return : 2005-08-16 07:49:22.389326
Time of Last Fetch : 2005-08-16 07:49:22.389480
Time of Last Complete : 2005-08-16 07:50:05.662423
Condition 1 (Invariant): (Table1.F1 = 1234567)
Condition 1 Selectivity: 0.00000000
Condition 1 Usefulness: 10.00000000
Condition 1 Index: HG Table1.DBA.HG_F1
Condition 2 (Invariant): (Table1.F2 < 250)
Condition 2 Selectivity: 0.11071996
Condition 2 Usefulness: 8.88928004
Condition 2 Distincts in Range: 26
Condition 2 Index: LF Table1.DBA.LF_F2
Condition 2 Range Alg.: BM
Output Vector: 1 entries (8 data bytes)
Output 1: COUNT(*)
Output 1 Data Type: unsigned bigint (20, 0)
Output 1 Base Distincts: 1
Column 1: Table1.F1
Column 1 Data Type: unsigned integer (10, 0)
Column 1 Base Distincts: 30387328
Column 1 Est. Filtered Distincts: 1
Column 1 Indexes: FP, HG, HNG
Column 2: Table1.F2
Column 2 Data Type: smallint (5, 0)
Column 2 Base Distincts: 101
Column 2 Est. Filtered Distincts: 9
Column 2 Indexes: FP, LF, HNG

With best regards, Alexander Sin. E-mail: a.sinitsin[hot dog]alvion.com.ua

dbMethods

2005-08-17, 3:24 am

What is the server version and indexes on Table1?
What commands to generate this query plan?

> Hello, All!
>
> Table is huge (>18 000 000 000 rows).
> This query works well (less than a second):
>
> select Count(*) from dba.Table1 where F1 = 1234567
>
> But this one takes a long time (~15min):
>
> select Count(*) from dba.Table1 where F1 = 1234567 and F2
> < 250
>
> This one works well:
>
> select Count(*) from dba.Table1 where F1 = 1234567 and F2
> = 250
>
> Execution plan looks good, fields indexed properly. Can I
> workaround this issue?
> ------
> Query Plan:
> 0 #03: Root
> Child Node 1: #01
> Generated Result Rows: 1
> Estimated Result Rows: 1
> Query_Name: q1_2
> User Name: testuser
> Est. Temp Space Used (Mb): 0.2
> Act. Temp Space Used (Mb): 0.1
> Effective Number of Users: 1
> Number of CPUs: 4
> Max_Cartesian_Result
: 200000000
> Query_Temp_Space_Lim
it: 0
> Time of Cursor Creation : 2005-08-16
> 07:36:46.434730
> Time of First Prepare : 2005-08-16
> 07:36:46.609660
> Time of First Prepare Return: 2005-08-16
> 07:49:22.389243
> Time of First Fetch Call : 2005-08-16
> 07:49:22.389310
> Time of First Fetch Return : 2005-08-16
> 07:49:22.389327
> Time of Last Fetch : 2005-08-16
> 07:49:22.389482
> Time of Last Complete : 2005-08-16
> 07:50:05.662502
> Elapsed time : 0:13:19.052842
> Output Vector: 1 entries (8 data bytes)
> Output 1: COUNT(*)
> Output 1 Data Type: unsigned bigint (20, 0)
> Output 1 Base Distincts: 1
> 1 #01: Aggregation Leaf
> Generated Result Rows: 1
> Estimated Result Rows: 1
> Table Name: Table1
> Valid Grouping Algorithms: Vertical, Hash, Sort
> Table Row Count: 18374401458
> Time of First Prepare : 2005-08-16
> 07:36:46.609666
> Time of First Prepare Return: 2005-08-16
> 07:49:22.389233
> Time of First Fetch Call : 2005-08-16
> 07:49:22.389314
> Time of First Fetch Return : 2005-08-16
> 07:49:22.389326
> Time of Last Fetch : 2005-08-16
> 07:49:22.389480
> Time of Last Complete : 2005-08-16
> 07:50:05.662423
> Condition 1 (Invariant): (Table1.F1 = 1234567)
> Condition 1 Selectivity: 0.00000000
> Condition 1 Usefulness: 10.00000000
> Condition 1 Index: HG Table1.DBA.HG_F1
> Condition 2 (Invariant): (Table1.F2 < 250)
> Condition 2 Selectivity: 0.11071996
> Condition 2 Usefulness: 8.88928004
> Condition 2 Distincts in Range: 26
> Condition 2 Index: LF Table1.DBA.LF_F2
> Condition 2 Range Alg.: BM
> Output Vector: 1 entries (8 data bytes)
> Output 1: COUNT(*)
> Output 1 Data Type: unsigned bigint (20, 0)
> Output 1 Base Distincts: 1
> Column 1: Table1.F1
> Column 1 Data Type: unsigned integer (10, 0)
> Column 1 Base Distincts: 30387328
> Column 1 Est. Filtered Distincts: 1
> Column 1 Indexes: FP, HG, HNG
> Column 2: Table1.F2
> Column 2 Data Type: smallint (5, 0)
> Column 2 Base Distincts: 101
> Column 2 Est. Filtered Distincts: 9
> Column 2 Indexes: FP, LF, HNG
>
> With best regards, Alexander Sin. E-mail: a.sinitsin[hot
> dog]alvion.com.ua
>
>

Alexander Sinitsin

2005-08-18, 9:42 am

Hi
>What is the server version
12.5.0/040702/P/ESD 11/Sun_svr4/OS 5.7/64bit/2004-07-02 01:39:09

>and indexes on Table1
Column 1 Indexes: FP, HG, HNG
Column 2 Indexes: FP, LF, HNG

Column 1 - F1, Column 2 - F2

>What commands to generate this query plan?
select Count(*) from dba.Table1 where F1 = 1234567 and F2< 250

Alexander

Sponsored Links