From: | Dmitry Tkach <dmitry(at)openratings(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query performance PLEASE HELP |
Date: | 2003-01-31 22:07:43 |
Message-ID: | 3E3AF3AF.7040400@openratings.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane wrote:
>Dmitry Tkach <dmitry(at)openratings(dot)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.78 rows=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.04 rows=0 loops=41020)
Total runtime: 297528.31 msec
Dima
From | Date | Subject | |
---|---|---|---|
Next Message | Medi Montaseri | 2003-01-31 22:12:46 | Re: Basic SQL join question |
Previous Message | Robert Treat | 2003-01-31 21:41:03 | Re: grant to all tables |