Re: Deceiding which index to use

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Title: Re: [PERFORM] Deceiding which index to use
Richard Huxton wrote:

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.

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?

> 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.

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.

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.

I'll try that one if the negative n_distinct value can be a correct one :-)

Zizi


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux