StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
8236711
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2011-11-23T02:42:13.783
FavoriteCount
0
LastActivityDate
2013-11-22T17:05:15.127
LastEditDate
2013-11-22T17:05:15.127
LastEditorUserId
2390083
OwnerUserId
11780
ParentId
8228326
PostTypeId
2
Score
6
ViewCount
0
LastEditorDisplayName
text
Body
I'm skeptical that this has anything to do with bad statistics unless you consider the combination of database statistics and your custom data type. My guess is that PostgreSQL is picking a nested loop join because it looks at the predicates <code>(treenode.location).x >= 8000 AND (treenode.location).x <= (8000 + 4736)</code> and does something funky in the arithmetic of your comparison. A nested loop is typically going to be used when you have a small amount of data in the inner side of the join. But, once you switch the constant to 10736 you get a different plan. It's always possible that the plan is of sufficiently complexity that the Genetic Query Optimization (GEQO) is kicking in and you're seeing the side effects of non-deterministic plan building. There are enough discrepancies in the order of evaluation in the queries to make me think that's what's going on. One option would be to examine using a parameterized/prepared statement for this instead of using ad hoc code. Since you're working in a 3-dimensional space, you might also want to considering using <a href="http://postgis.refractions.net/documentation/manual-1.5/ch08.html#PostGIS_3D_Functions" rel="nofollow">PostGIS</a>. While it might be overkill, it may also be able to provide you with the performance that you need to get these queries running properly. While forcing planner behavior isn't the best choice, sometimes we do end up making better decisions than the software.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POKeep PostgreSQL from sometimes choosing a bad query plan
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USBeryllium
UserOwnerUserId
1. USJeremiah Peschka
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.