Re: Query performance PLEASE HELP

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