> 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
> order by 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.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
... actually, after seom thinking, this plan seems to actually be WORSE
that the other one - it makes about 41000 inner loops through
managed_supplier, while there are only about 11000 entries in
managed_supplier with subscriber=74, so, if it did it the other way
around (like in the first case), that would result in only 11K inner
loops - 4 times less... (there is also an overhead of sorting, but it is
negligeable, because the intersection is only 110 rows)
So, I just want to point it out again - the query plan does not seem to
be a problem at all - whichever one it chooses, the preformnace is much,
much worse then I would expect -
according to pg_statio_user_tables, it only reads less then 20000 blocks
from disk for this query, which totals to about 5K per second... How
come it is so slow???
Dima