Re: Join slow on "large" tables - Mailing list pgsql-performance

From Scott Marlowe
Subject Re: Join slow on "large" tables
Date
Msg-id 1086708436.27200.66.camel@localhost.localdomain
Whole thread Raw
In response to Re: Join slow on "large" tables  (Josué Maldonado <josue@lamundial.hn>)
List pgsql-performance
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.




pgsql-performance by date:

Previous
From: Litao Wu
Date:
Subject: reindex and copy - deadlock?
Next
From: Tom Lane
Date:
Subject: Re: reindex and copy - deadlock?