Using index hints with compound indexes performs similar to full table scan

Starcounter version: <2.4.990.121>.

Issue type

[x] 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)
    )
)

Starcounter / Home