n_distinct is -0.359322 and most_common_vals contains about 10 different anumbers (which are corretct), most_common_freqs are between 0.01 and 0.001. What does n_distinct exactly mean? Why is it negative?Re: [PERFORM] Deceiding which index to use And does the planner know that?
SELECT * FROM pg_stats WHERE tablename='subscriber' AND attname='anumber';
It's the n_distinct you're interested in, and perhaps most_common_freqs.
It can see through the cast - I have just tried to create the same database omitting the non-numeric anumbers and the results are the same.> Nothing, everything is the same - the problem lies on the other table's index
> usage, using this index is fine.
The planner has to guess how many matches it will have for
subscriber=5555555. Based on that choice, it will either:
a. Do the join, then find the highest crd values (sort)
b. Scan the crd values backwards and then join
It's chosen (b) because it's estimating the numbers of matches
incorrectly. I'm wondering whether the system can't see through your
function-call (the cast to numeric) to determine how many matches it's
going to get for any given value.
I'll try that one if the negative n_distinct value can be a correct one :-)If the system can't be persuaded into getting its estimates more
accurate, it might be worth trying an index on (subscriber_id,crd) and
dropping the index on (crd) - if that's reasonable for your query patterns.