pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Scott Marlowe <smarlowe@qwest.net>
To: Josué Maldonado <josue@lamundial.hn>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join slow on "large" tables
Date: Tue, 08 Jun 2004 09:27:16 -0600
Message-ID: <1086708436.27200.66.camel@localhost.localdomain> (raw )
In-Reply-To: <40C5CED2.3020002@lamundial.hn >
References: <40C4E9EA.7000708@lamundial.hn >
<1086648432.27200.37.camel@localhost.localdomain >
<40C5CED2.3020002@lamundial.hn >
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.
view thread (9+ messages)
Message-ID: <1086708436.27200.66.camel@localhost.localdomain>
Permalink: ../1086708436.27200.66.camel@localhost.localdomain/
Also on: postgresql.org/message-id/1086708436.27200.66.camel@localhost.localdomain
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@qwest.net, josue@lamundial.hn
Subject: Re: Join slow on "large" tables
In-Reply-To: <1086708436.27200.66.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox