I'm intersecting a point with a set of polygons. The query is indexed, and the polygons do not overlap but the query plan seems to think that instead of 1 row, i'll return 18k rows, and this results in a bad query plan.
In particular the right most nodes of the query plan seem to think that the STPointFromText function will return a cardinality of 1000, and that the intersection of this point set with the geometry index returns 30% of the 54k rows. (ran 1 million points through the table without finding a counter-example that actually returned more than 1 row)
The result isn't horrible in this abbreviated query, but when i join the output of this onto anything else, the high cardinality estimate forces the upstream table to be a tablescan+hashmap, even though the overall query returns 1 row. This extended query is running a few times per second, so i'm wondering how i can optimise this.
The spatial index is HHHH, for a highest resolution (over the approx 4000km longest side of the domain) of approx 80x50m, there are 56k polygons in the index, with expected minimum size of ~100m.
Note the difference between the est rows and the actual.
Estimated query plan.