Using index hints with compound indexes performs similar to full table scan #448
Description
Starcounter version: <2.4.990.121>.
Issue type
- Bug
Issue description
Compound index is not working properly. In a test where I had one million indexed (ClassA & ClassB) entities and one million non-indexed (ClassC & ClassD), a search with index hint using the compound index had similar performance of a non-indexed search, being slightly above 50 seconds for both searches.
When using an indexed search with no query hint, the search took less then one millisecond (>1ms).
This matter has been discussed with @bigwad and the solutions is to not use index hints since the QP optimizes it better.
Posting it here for documentation.
Reproduction code snippet
using System;
using System.Diagnostics;
using System.Linq;
using Starcounter;
using static CompoundIndexTest.Test;
namespace CompoundIndexTest
{
class Program
{
static Action StarcounterCompoundIndexSearch;
static Action StarcounterFullTableScanSearch;
static Action StarcounterRegularIndexSearch;
static readonly ClassA TheAReference =
Db.SQL<ClassA>("SELECT t FROM CompoundIndexTest.ClassA t").FirstOrDefault() ??
Db.Transact(() => new ClassA());
static readonly ClassC TheCReference =
Db.SQL<ClassC>("SELECT t FROM CompoundIndexTest.ClassC t").FirstOrDefault() ??
Db.Transact(() => new ClassC());
static void Main()
{
Indexes();
GenerateData();
Init();
Warmup();
var t1 = TestCase(StarcounterRegularIndexSearch);
var t2 = TestCase(StarcounterRegularIndexSearch);
var t3 = TestCase(StarcounterCompoundIndexSearch);
var t4 = TestCase(StarcounterFullTableScanSearch);
var t5 = TestCase(StarcounterFullTableScanSearch);
var t6 = TestCase(StarcounterCompoundIndexSearch);
}
private static void Indexes()
{
if (Db.SQL("SELECT i FROM Starcounter.Metadata.\"Index\" i WHERE Name = ?", "ClassB_Reference_Created")
.FirstOrDefault() == null)
Db.SQL("CREATE INDEX ClassB_Reference_Created ON CompoundIndexTest.ClassB (Reference, Created DESC)");
}
private static void GenerateData()
{
if (Db.SQL<ClassB>("SELECT t FROM CompoundIndexTest.ClassB t").Any()) return;
Db.Transact(() =>
{
for (var i = 0; i < 500000; i++)
{
new ClassB {Reference = TheAReference};
}
for ( var i = 0; i < 500000; i++ )
{
new ClassD { Reference = TheCReference };
}
});
// Set the interesting value in the middle
Db.Transact(() => new ClassB(DateTime.MaxValue) {Reference = TheAReference});
Db.Transact(() => new ClassD(DateTime.MaxValue) {Reference = TheCReference});
Db.Transact(() =>
{
for (var i = 0; i < 500000; i++)
{
new ClassB {Reference = TheAReference};
}
for ( var i = 0; i < 500000; i++ )
{
new ClassD { Reference = TheCReference };
}
});
}
private static void Init()
{
StarcounterRegularIndexSearch = () =>
Db.SQL<ClassA>(
"SELECT a FROM CompoundIndexTest.ClassA a JOIN CompoundIndexTest.ClassB b ON b.Reference=a WHERE b.Reference =? ORDER BY b.Reference, b.Created DESC",
TheAReference).FirstOrDefault();
StarcounterCompoundIndexSearch = () =>
Db.SQL<ClassA>(
"SELECT a FROM CompoundIndexTest.ClassA a JOIN CompoundIndexTest.ClassB b ON b.Reference=a WHERE b.Reference =? ORDER BY b.Reference, b.Created DESC OPTION INDEX (b ClassB_Reference_Created)",
TheAReference).FirstOrDefault();
StarcounterFullTableScanSearch = () =>
Db.SQL<ClassC>(
"SELECT c FROM CompoundIndexTest.ClassC c JOIN CompoundIndexTest.ClassD d ON d.Reference=c WHERE d.Reference =? ORDER BY d.Reference, d.Created DESC",
TheCReference).FirstOrDefault();
}
private static void Warmup()
{
for (var i = 0; i < 1; i++)
{
var t1 = TestCase(StarcounterCompoundIndexSearch);
var t2 = TestCase(StarcounterFullTableScanSearch);
var t3 = TestCase(StarcounterRegularIndexSearch);
}
}
}
[Database]
public class ClassA
{
}
[Database]
public class ClassB
{
public ClassB() : this(DateTime.Now.AddMinutes(new Random().Next()))
{
}
public ClassB(DateTime created)
{
Created = created;
}
public DateTime Created { get; set; }
public ClassA Reference { get; set; }
}
[Database]
public class ClassC
{
}
[Database]
public class ClassD
{
public ClassD() : this(DateTime.Now.AddMinutes(new Random().Next()))
{
}
public ClassD(DateTime created)
{
Created = created;
}
public DateTime Created { get; set; }
public ClassC Reference { get; set; }
}
public static class Test
{
public static readonly Stopwatch watch = new Stopwatch();
public static long TestCase(Action a)
{
watch.Restart();
a.Invoke();
return watch.ElapsedMilliseconds;
}
}
}Query Plans
Compound Index
Tables(
0 = CompoundIndexTest.ClassA
1 = CompoundIndexTest.ClassB
)
Projection(
0 =
ObjectThis(0)
)
Sort(
Join(
Inner
IndexScan(
ClassB_Reference_Created ON CompoundIndexTest.ClassB
1
Reference
ObjectDynamicRange(
)
Created
DateTimeDynamicRange(
)
ComparisonObject(
Equal
ObjectProperty(1, Reference)
ObjectVariable(1)
)
Ascending
)
ReferenceLookup(
0
ObjectProperty(1, Reference)
LogicalValue(TRUE)
)
)
MultiComparer(
ObjectComparer(
ObjectProperty(1, Reference)
Ascending
)
DateTimeComparer(
DateTimeProperty(1, Created)
Descending
)
)
)
Full Table Scan
Tables(
0 = CompoundIndexTest.ClassC
1 = CompoundIndexTest.ClassD
)
Projection(
0 =
ObjectThis(0)
)
Sort(
Join(
Inner
FullTableScan(
MotherOfAllLayouts_SetspecIndex ON CompoundIndexTest.ClassD
1
ComparisonObject(
Equal
ObjectProperty(1, Reference)
ObjectVariable(1)
)
Ascending
)
ReferenceLookup(
0
ObjectProperty(1, Reference)
LogicalValue(TRUE)
)
)
MultiComparer(
ObjectComparer(
ObjectProperty(1, Reference)
Ascending
)
DateTimeComparer(
DateTimeProperty(1, Created)
Descending
)
)
)
Regular Search
Tables(
0 = CompoundIndexTest.ClassA
1 = CompoundIndexTest.ClassB
)
Projection(
0 =
ObjectThis(0)
)
Join(
Inner
IndexScan(
ClassB_Reference_Created ON CompoundIndexTest.ClassB
1
Reference
ObjectDynamicRange(
ObjectRangePoint(
GreaterThanOrEqual
ObjectVariable(1)
)
ObjectRangePoint(
LessThanOrEqual
ObjectVariable(1)
)
)
Created
DateTimeDynamicRange(
)
LogicalValue(TRUE)
Ascending
)
ReferenceLookup(
0
ObjectProperty(1, Reference)
LogicalValue(TRUE)
)
)