Morning

Morning

@ypercubeᵀᴹ No. A prefix equality seek on (a,b) is identical to (b,a).

@PaulWhite thnx. So there must be some other explanation.

like index bloat or one index being heavily used by other queries so cached in memory, etc...

Statistics and data not being in cache? (Guessing)

I always run queries twice to ensure the data and plan are in (queryplan/data) cache.

Yeah, I thought of statistics initially, too, but I can't imagine how it could affect an equality check.

does the planner consider statistics to calculate how much memory to request?

I'm not sure with that last question. But it has to estimate the amount of memory it will require for the query based on some metric. (Digging for old Query Execution Plans) I'd guess: "Statistics", but @PaulWhite would be able to give you an answer on that one.

@ypercubeᵀᴹ Actually. Thinking about it a bit more, there could be a difference in the number of comparisons needed on average. If a is much more unique than b, a b-tree index equality seek on (a, b) might need to compare b values less often than the reverse case.

@PaulWhite doesn't do a seek in a, then b?

I don't follow how it would need more comparisons

@ypercubeᵀᴹ Well imagine you need to find (7, 1234567) in a b-tree with an awful lot of 7s in the first position, but very few 1234567s in the second key position. A lot of the time, while navigating the b-tree levels using binary search, comparing the stored key value to 7 will not be enough to decide which way to go next (this, up, or down).

So you'll need to compare two keys quite often. In the reverse case, the first key comparison (at whatever level) will most likely determine which direction the binary search goes next.

Consider a page that must contain the target row if it exists. The indirection vector is sorted by (a,b). We start with the middle record and test a, finding the value 7. We are looking for 7 so we must test b as well to see if we have found what we're looking for, or if the next search should be halfway before the current position, or halfway after.

In the reverse case, our first test is very unlikely to find 1234567, so we immediately know whether we need to search halfway before or halfway after.

Make sense?

(also pretend the 7 and 1234567 aren't fixed-length fields, but placeholders for values of arbitrary type)

but I thought an index seek would do only one binary search, finding the first record of (a=7, b= 1234567). Then continue going to the next record/node on the right, until it finds a record with different b or different a.

@ypercubeᵀᴹ The point is that whether you are navigating down the b-tree to find the starting point, or deciding whether to stop when scanning forward or backward, you may need to only compare the first key if it is different at each step.

There's no point comparing the second key for equality if the first key was already different.

In the simple case of all fixed-length same-type key fields, one could in principle compare the whole key (comprised of multiple fields) all at once. I don't know if SQL Server contains that specific optimization, but it wouldn't apply generally anyway e.g. a is varchar(10), b is varbinary(700).

@PaulWhite it does make sense, you would be a good teacher

@Lamak That's a pretty low blow

@ypercubeᵀᴹ You'd need to do a lot of comparisons in a deep b-tree or lots of small seeks very quickly, to notice the difference I would think. All things being equal a more selective column first is better, but my goodness things are rarely that equal.

@PaulWhite you would be.....a great teacher?

@Lamak teachers get a bad rap, sometimes it is deserved

i.e. teachers at school

professor?

@Lamak Depends on which time period we are talking about, and which subject. The quality in academia seems highly variable in modern times.

let's settle in 1950s oxford professor

Anyway it was just a small joke (not really worthy of JEAGL)

Happy to be compared with e.g. Richard Feynman 😀

I would need the previous topic explained more for that

Heh fair enough, I was aiming high

Just don't compare me with a 2020 associate professor of intersectional grievance studies

maybe an mba professor

@PaulWhite Postgres has added an optimization recently, that is related.

@ypercubeᵀᴹ yes I saw that

@Lamak slap

Storing the values in indexes only once or something like that.

yep minimal required difference and whatnot

I know SQL Server does interpolation search sometimes instead of binary search, and compressed pages probably do some tricks with prefix skipping, but I haven't really looked into it

@PaulWhite I think richard feynman would

I doubt it has changed much. Most of the perf effort goes into things like SIMD, GPU offloading, latch-free, and batch mode processing these days

@Lamak Neat trick for a dead dude

Wouldn't put it past him

@PaulWhite in any case, as you said there has to be some huge result for the number of equality checks to make a significant difference

And I don't see how it can add minutes to a query.

Right, and no other overriding considerations for key order

@PaulWhite I mean, quantum mechanichs....everything can happen

@ypercubeᵀᴹ Maybe if the query runs for weeks 😀

@Lamak I'm not getting entangled in that

good one

I suppose it need not be so dramatic if one of the keys is especially common and expensive to compare (e.g. collation-aware long string)

But then your database has other problems so

@PaulWhite my current database is in cassandra so it doesn't have any of these silly SQL issues ;)

We’re starting a Cassandra club. It’s open to everyone but no one can join.

@ypercubeᵀᴹ hahaha

@ypercubeᵀᴹ I think that's where I was with it, too - I can imagine one index requiring more B-tree work, but not enough where it would cause a noticeable slow down - all things being equal (freshly rebuilt index, etc)

posting this for no particular reason

Lol, that's about right