0

I have a fact table in Infobright that has around 40 million rows. Running a query on that table, like the one shown below, takes well over 10 minutes. 

SELECT pat_key,
   COUNT(c_id)
FROM my_fact_table
GROUP BY pat_key
ORDER BY COUNT(c_id) DESC
LIMIT 50;

Duration/Fetch = 334.528 sec / 0.094 sec

Any ideas why and how to tune this?

Btw, the hardware spec is AWS m1.large. So network latency aside, this is still a significant time interval.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Kingz
  • 5,086
  • 3
  • 36
  • 25

1 Answers1

1

Firstly, can you provide the DDL especially for pat_key and c_id?

Secondly, are you running Infobright Enterprise Edition or the Community Edition?

Thirdly, can you pull the bh.err log? To do so, open brighthouse.ini (in your data directory), set ControlMessages=5, and restart the service. Then, run this query. The file bh.err should include a lot of information about the execution of this query. Once you've done so, can you paste it here.

Finally, just looking solely on the query: SELECT pat_key, COUNT(c_id) as thecnt FROM my_fact_table GROUP BY pat_key ORDER BY thecnt DESC LIMIT 50;

The bh.err will tell a lot more! Plus, you limit yourself to 2 cores on this instance; you're probably a bit processor constrained. I look forward to seeing more.

Jeff Kibler
  • 11
  • 1