Change 3D relationship functions to use &&& instead of &&

[robe]

Now that we have the new &&& nd interacts operator in place, we can change all the 3D aware functions to use that short-circuit instead of &&.

So

ST_3DIntersects, ST_3DDWithin, ST_3DFullyWithin

Nicklas — you want to tackle this one since these are your babies

[robe]

On second thought as Paul pointed out — what if they didn't build a 3D index on their 3D geometries.

We might want to do instead

&& AND &&& AND _ST_3D

That will catch both indexes and choose the cheapest one to test first

[robe]

Has anyone thought about this more? I want to make this change now and close this out. Speak now or forever hold your peace. I'm going to make this change tomorrow if there are no objections and check and compare to make sure with a 3D index it is indeed faster than the old function.

[nicklas]

Sorry Regina, I have missed this one.

Of course we should do it.

If you have time do do it please do.

Otherwise I will do it in i few days.

Thanks

Nicklas

[nicklas]

Hmm,

"&& AND &&& AND _ST_3D That will catch both indexes and choose the cheapest one to test first"

Does that mean it will use the cheapest to test or the one giving less job for the recheck?

I mean I guess && is cheaper do do but it will sort away fewer records and send more of them for recheck. How smart is the planner?

/Nicklas

[robe]

Hmm good question. I forgot that a 3D index would satisfy both && and &&&, though that is my assumption. Paul — correct me if I am mistaken.

So actually the order should be &&& AND &&.

The way PostgreSQL works — it will first process the one that can take advantage of an index all else being equal (not sure how it compares index costs). If both can utilize an index (it can't tell which is more costly), then it will process in order.

So my thinking is if a user has a 2D index, then && will be checked first and then &&& would provide additional filtering before the more costly _ST_3D regardless of if we do &&& and && or && and &&&.

If they have a 3D index, then &&& will be used first only if we put it in front (assuming that both && and &&& can take advantage of the index), followed by && which in theory would be a pointless check but shouldn't add too much overhead. This is just following precedence of operation when costs are considered even.