On Tue, 2004-06-08 at 08:36, Josué Maldonado wrote:
> Hello Scott,
SNIP...
> > Your numbers show that you are tossing 659M and 314M against each other,
> > but I don't know if you're harvesting the whole set at once, or just a
> > couple row of each. Indexing help, or is this always gonna be a big seq
> > scan of 90% of both tables?
>
> Generally only a small set is queried, the bigest record set expected is
> about 24,000 rows and does not exced the 10MB size, explain analyze
> shows the planner is using the index as expected but performance still poor.
If that is the case, then shared_buffers should likely only be 1000 to
10000. anything over 10000 is usually a bad idea, unless you've proven
it to be faster than <10000.
> dbmund=# explain analyze select * from vkardex where kprocode='1013';
> Nested Loop (cost=0.00..2248.19 rows=403 width=114) (actual
> time=846.318..16030.633 rows=3145 loops=1)
> -> Index Scan using pkd_pcode_idx on pkardex (cost=0.00..806.27
> rows=403 width=72) (actual time=0.054..87.393 rows=3544 loops=1)
> Index Cond: ((pkd_pcode)::text = '1013'::text)
> -> Index Scan using pdc_pk_idx on pmdoc (cost=0.00..3.55 rows=1
> width=50) (actual time=4.482..4.484 rows=1 loops=3544)
> Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
> Total runtime: 16033.807 ms
> (6 rows)
Well, it looks like your predicted versus actual rows are a bit off, and
in the final bit, the planner things that it is going to be merging 403
rows but is in fact merging 3145 rows. Try
set enable_nestloop = off;
and run the explain analyze again and see if that's faster. If so, try
upping your target stats on kprocode (see "\h alter table" in psql for
the syntax), rerun analyze, and try the query with set enable_nestloop =
on to see if the planner makes the right choice.
