on 02-12-2014 11:41 AM
Dear all,
We have encountered slow performance on a simple query on a table of 5,8bn rows. It is
select *
from
where col='.....'
there is an HG index on the column mentioned in the where clause and I have attached the query plan. The query runs for about 2min 30 secs. If I ask only the column I have in Where clause, it returns instantly as expected. IQ version 15.4 ESD#2
I see that IQ table has 5.8bn rows and IQ page size is 64KB. Could this be the reason behind the problem?
Other queries where this table participates in joins are as fast as expected. Also there is no real problem while loading the table. sp_iqrowdensity shows density 1 for all FP indexes.
I have run out of ideas on this and any help is welcome.
Thank you in advance
PS I have attached plan.txt, rename it to .html to open the plan in html format
Remember IQ is a columnar database. By running a select *, you are opening 51x64K page chains, not just one 64K page. So IQ still has to put the row back together from all the columns it must access.
Also, the query plan show that this appears to be running single threaded.
Try reducing the query further to only the columns you really need.
Chris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.