Hi! What type of index to use?

[Former Member]

Table structure:

Day_Id int, Location_Id int, Prod_Id int, Sale_Flag tinyint ...

Day_Id, Location_Id, Prod_Id - is primary key

Sale_Flag - a flag (0 or 1) indicating whether sales by Day_Id, Location_Id, Prod_Id

Table Records count - 7 465 699 304

IQ Version - 15.4 ESD1 (Windows 2008)

using measure as SUM(Sale_Flag😞

Day_Id, Sum(Sale_Flag) or Day_Id, Sum(Location_Id) ...

I have created 2 index this flag - HNG (917M) and LF (1.65G)

What type of index to use ?

What type of index is preferable to use ?

Perhaps both ?

or remove one ?

[jong-kil_park]

Hi Oleg,

In case a specific column has a value of 0 or 1,  LF(Low Fast) index is very useful to retrieve the result rows satisfying with the one out of the two values.

That is, it is effective when the column is used as a predicate in where clause.

In your case, the column Sale_Flag is used for a measure factor in the select list, not as a predicate.

It means you don't have to create LF index on the column.

But, it is applicable only when the column Sale_Flag is used in the select list.

You should also check the characteristic of the column whether it can be used as a predicate.

If then, you have to create the LF index on that column, too.

It doesn't matter you create a HNG index on a column which already has a LF index or HG index.

As you know, those indexes are helpful to get better performance in querying, but it also makes us more cost in space management as well as performance in DMLs against the table.

I also would like you to check another way of utilizing FP(1) byte instead of HNG or LF index.

I think it can show us a satisfiable performance and save lots of costs which mentioned above.

Best Regards
Jerry

[Former Member]

ok!

Thank you.

I will test the FP(1) and accomplish your goal in the next few days.

HNG - index is not needed and can be removed ?

[markmumy]

Our guidance has changed for IQ 16.  We should no longer be using LF indexes with IQ 16.  The HG index has been augmented to include the same features, and more, than the LF index.  Load speed will be the same, or faster.  Query speed should be improved.  Compression will be about the same as the low cardinality HG index is nearly identical in form to the LF index.

For IQ 15, I would use the LF over the HG for this use case.

I would strongly encourage a migration to IQ 16.  There are many improvements including the addition of the nbit index.  This replaces the default FP and will greatly improve performance and compression.  For a column with just 2 values, using an nbit should be much faster and not require any additional indexes (HG, LG, HNG).

In your case of a column with just 2 values, that would require one bit to represent them both.  For 7.4 billion rows, that's just 889 MB before any disk compression (7.4 billion / 8 bits per byte = 889mb).  Assuming a modest compression of 75% that's about 200mb on disk.  I say modest because a column with just values will compress quite well.

And with the nbit you would likely not need any other indexes on that column.

That same column in IQ 15 would be close to 8x larger!  Why?  8 bits per byte.  The best we can get to in IQ 15 for an FP is one byte.  The best we can do in IQ 16 is 1 bit.

All that aside, if that column really only stores 2 values, change the type to a bit type and don't put any indexes on the column.

The HNG index should not be used much, in my opinion, in IQ 15 or 16.  Most of the features that an HNG had have been incorporated into the other indexes so that the use of the HNG over the past 5 years has decreased.  I haven't used it in any benchmarks, POCs, or Guinness World Record testing that I've done in that time.

Mark