IQ Server - Re: Query performance issue with <

Author

Re: Query performance issue with <

dbmethods

2006-03-07, 8:25 pm

What indexes on Table1 and what commands to generate the query plan
here? And what is the server version?

On Tue, 16 Aug 2005 06:10:21 -0700, Alexander Sinitsin wrote:

> 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

Alsin

2006-03-09, 7:27 am

Hi

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

>and what commands to generate the query plan here?
set temporary option query_plan = 'ON';
set temporary option query_detail = 'ON';
set temporary option query_plan_after_run
= 'ON';
set temporary option query_timing = 'ON';

>And what is the server version?
IQ 12.5

Thank you

Sponsored Links