Tom Lane wrote:
>Dmitry Tkach <dmitry@openratings.com> writes:
>
>>What does NEGATIVE n_distinct mean (for managed_supplier.duns)? :-)
>>
>
>Fractional estimate --- in particular, -1 means it thinks the column
>is unique. tradestyle.duns is not really unique by itself, is it?
>
No. The (duns,type) combination is unique (type is 0 through 5).
>
>>It lies about many things... For example - n_distinct for
>>tradestyle.name = 385825 is about 100 times small than the actual number
>>(which is a little over 30 million)
>>
>
>It might help to raise the statistics target for these columns and
>re-ANALYZE. Try 100 or so instead of the default 10.
>
>However, even with these stats I'd think it would pick up on the
>tradestyle.name index as a likely thing to use. What was the
>database's locale setting, again?
>
>
>
The locale is C.
And, once again, I am afraid, we are heading in the wrong direction here
- it DOES choose the name index sometimes (for some of the values for
the name in the criteria), but it just doesn't seem to make any
difference. Here is an example:
Limit (cost=0.00..16.14 rows=1 width=192) (actual time=6926.37..297527.99 rows=10 loops=1)
-> Nested Loop (cost=0.00..16.14 rows=1 width=192) (actual time=6926.36..297527.94 rows=11 loops=1)
-> Index Scan using tradestyle_name_idx on tradestyle ts (cost=0.00..7.98 rows=1 width=35) (actual
time=51.99..295646.78rows=41020 loops=1)
-> Index Scan using managed_supplier_idx on managed_supplier ms (cost=0.00..5.82 rows=1 width=157) (actual
time=0.04..0.04rows=0 loops=41020)
Total runtime: 297528.31 msec
Dima