quals get clobbered by joins

[segfault-inc]

quals get clobbered by joins

[rdunklau]

Could you please paste your execution plan (EXPLAIN) ?
Can you reduce the test case to a more simple form that would still exhibit the problem ?
Out of these tables, which ones are foreign and which ones are local ?
What does your implementation return for get_rel_size, and get_path_keys ?

Thank you.

[johnmudd]

Thanks for your reply. Here's a Google Doc containing execution plan, get_rel_size, and get_path_keys. All tables are foreign. I can try to create a more concise demo.

[rdunklau]

The problem is, your estimates for the returned number of rows are sometimes float which get rounded to the nearest integer.

So, these two both get rounded to 1:
[[u'btype', u'keykey'], 1.0022],
[[u'btype', u'keykey', u'keystoreid'], 1]

In that case, the planner picks the first one since it has no indication regarding what would be the cheapest path.

I guess those estimates do not reflect the reality of your data: if filtering on only two of those attributes would return only one row, you shouldn't care about the third, right ?

I think you should reconsider your implementation of get_path_keys and get_rel_size.

A quick and dirty hack would be to just list them from most specific to least specific, regardless of the cost.

I'm still investigating why the btype=1 qual comes twice, but it is a minor inconvenience and it is not "replacing" anything as you initially supposed.

[johnmudd]

Thanks, again!

I believe the 1.0022 value is accurate in the sense that [btype, keykey] is not a truly unique index. The average query using this key will return 1.0022 rows. The number is close to 1 (unique) but using this key is considerable less efficient than the truly unique key [btype, keykey, keystoreid]. That's just the nature of my underlying ISAM files. So the rounding is a problem for me.

You are correct, I see now that the floats are being rounded. Any chance that code can be changed to support floats? Otherwise I guess I could perform some artificial manipulation.

The easiest would be to multiple each by 10,000 and round. Turn 1 into 10000 and 1.0022 into 10022. But that gives a drastically wrong answer to the question, "How many rows would be returned?". I assume that will foul up Postgres.

I guess I could come up with something to sort the list, start with lowest number, round, and then increment by 1 until I get a unique ranking so there's no confusion. That will still distort the numbers but hopefully not too much.

The extra "btype = 1" issue is low priority for me now. They don't cause a problem for my code. I agree now it's not replacing anything. I can continue to help in that are if you're interested though.