Curious Partition Function Behaviour

Just another short blog today describing a curious issue I found with a query plan this week and a “workaround”.

In our core system, we have a table with two partitions. One partition contains all the work that “has been done” (which has the column WorkItem set to –1) and the other the “work   in progress” (with WorkItem to different values, all > -1).

The reason we have created just two partitions for this table (which is a heap) is that the items that are “work in progress” are often scanned, yet the work that has been done (WorkItem = –1) is the vast majority of the table. This “mini partitioning” is a nice design pattern I often apply to skewed distribution like these. It provides a significant performance boost on table scans. But this week I saw an oddity I have not run into before.

 

To repro the issue we saw on our server, let me create a simple repro with this script (the actual table has tens of millions of rows):

CREATE PARTITION FUNCTION pf_test(INT)
AS RANGE RIGHT FOR VALUES (0)

CREATE PARTITION SCHEME ps_test
AS PARTITION pf_test ALL TO ([DATA])

CREATE TABLE Foo
(
    WorkItem INT NOT NULL
    , Payload CHAR(300) NOT NULL DEFAULT REPLICATE (‘X’, 300)
) ON ps_test(WorkItem)

INSERT INTO Foo (WorkItem) VALUES (-1)
INSERT INTO Foo (WorkItem) VALUES (-1)
INSERT INTO Foo (WorkItem) VALUES (2)
INSERT INTO Foo (WorkItem) VALUES (1)
INSERT INTO Foo (WorkItem) VALUES (1)
INSERT INTO Foo (WorkItem) VALUES (2)

The query that the simple partition function is meant to serve is:

SELECT * FROM Foo
WHERE WorkItem > –1

You would expect this query to only touch the second partition above, right? Wrong! Here is the actual plan:

Both partitions are touched. What the heck is going on here?

After an initial bafflement (I know this is supposed to work), I rewrote the query to:

SELECT * FROM Foo
WHERE WorkItem >= 0

And look at this:

The elimination happens as expected with this filter. In other words: the optimiser does not know that the following is true for integers:

x > –1 <=> x >= 0

Note: This server is running 2008R2 on the latest CU.