Re: Query performance PLEASE HELP

Tom Lane wrote:

>Dmitry Tkach <dmitry@openratings.com> writes:
>
>>>tradestyle.duns is not really unique by itself, is it?
>>>
>
>>No. The (duns,type) combination is unique (type is 0 through 5).
>>
>
>Well, if there are at most six of any duns value, then it's close enough
>to unique for planning purposes.  So that's not the problem.
>
>>- 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:
>>
>
>The plan's not very intelligible when you don't show us the query ...
>
>            regards, tom lane
>
Sorry, it was the same query as before - just had 'COMP%' instead of
'POST%':

rapidb# explain analyze
select * from tradestyle ts, managed_supplier ms where ts.duns=ms.duns and ts.name like 'COMP%' and ms.subscriber=74
orderby ts.name limit 10;  

NOTICE:  QUERY PLAN:

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.