huge(?) performance gap in Counting and summing a table column values ?

[Former Member]

Hi experts

There is a huge performance gap(execution time) in Counting and summing a table column values

Why is that ?

######### test case

(DBA)> create table test11 (c1 numeric);

Execution time: 0.027 seconds

(DBA)> insert into test11 select 1 from big_table;

109520474 row(s) inserted

Execution time: 61.833 seconds

(DBA)> commit;

Execution time: 0.016 seconds

(DBA)> select count(c1) from test11

count(test11.c1)    

--------------------

109520474           

(1 rows)

Execution time: 0.007 seconds  <<<<<

(DBA)> select sum(c1) from rdwadm.test11

sum(test11.c1)                                                                                                                   --------------------------------------------------------------------------------------------------------------------------------

109520474.00000000000000000000000000000000000000                                                                                

(1 rows)

Execution time: 1.427 seconds   <<<<<

[c_baker]

Depending on the query, the optimizer only needs to look at the metadata about the column for the count - only a couple of I/Os even though you have millions of rows in the table.

The sum actually needs to access the column values to perform the sum, so does a column scan to perform the aggregation operation. Possibly many I/Os - even though there is only a column scan being performed.  Depending on the operation (sum) IQ has to resolve the FP or nBitFP to determine the value to sum.  If the index is flat, then more pages may have to be accessed for the I/O operations as well to get the values to sum.

You should see this in a query plan.

Yes, other databases might have to actually access the data to also perform a count, but IQ maintains all statistics at the time the data is loaded, so the metadata can be used if the optimizer determines that.  That is why there is no 'update statistics' command for IQ - statistics are updated as part of the load.

[Former Member]

Thanks for your explanation....

I generated sql plan but there was no read block/pages count...

On oracle database I can get block read count of a query  with 10046 event.

On IQ.... are there any ways to get block read count (or IO size statistics) ?