Message-ID: <42D84F86.8070100@arbash-meinel.com>
Date: Fri, 15 Jul 2005 19:06:30 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317)
MIME-Version: 1.0
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow joining very large table to smaller ones
References: <46AF8F35-B735-4DFA-BA0A-AC82B5DA3CCE@drivefaster.net>
	<42D687E5.7080202@arbash-meinel.com>
	<C3470621-9DB0-444A-BAD1-F9995E0142FB@drivefaster.net>
	<42D6F177.3000002@arbash-meinel.com>
	<A8A7C7A0-7DC3-449C-A081-C1557DB9E16A@drivefaster.net>
	<42D70E42.90403@arbash-meinel.com>
	<DF093C91-7FBD-4F46-A2C4-4094650B1534@drivefaster.net>
	<42D7379F.3040806@arbash-meinel.com>
	<A6D507C9-A7D9-4FC6-9AE6-7BF3976B51BC@drivefaster.net>
In-Reply-To: <A6D507C9-A7D9-4FC6-9AE6-7BF3976B51BC@drivefaster.net>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig003EECB873ACA41B72F2B48F"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig003EECB873ACA41B72F2B48F
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Dan Harris wrote:
>
> On Jul 14, 2005, at 10:12 PM, John A Meinel wrote:
>
>>
>> My biggest question is why the planner things the Nested Loop would be
>> so expensive.
>> Have you tuned any of the parameters? It seems like something is  out of
>> whack. (cpu_tuple_cost, random_page_cost, etc...)
>>
>
> here's some of my postgresql.conf.  Feel free to blast me if I did
> something idiotic here.
>
> shared_buffers = 50000
> effective_cache_size = 1348000
> random_page_cost = 3
> work_mem = 512000

Unless you are the only person connecting to this database, your
work_mem is very high. And if you haven't modified maintenance_work_mem
it is probably very low. work_mem might be causing postgres to think it
can fit all of a merge into ram, making it faster, I can't say for sure.

> max_fsm_pages = 80000

This seems high, but it depends how many updates/deletes you get
in-between vacuums. It may not be too excessive. VACUUM [FULL] VERBOSE
replies with how many free pages are left, if you didn't use that
already for tuning. Though it should be tuned based on a steady state
situation. Not a one time test.

> log_min_duration_statement = 60000
> fsync = true ( not sure if I'm daring enough to run without this )
> wal_buffers = 1000
> checkpoint_segments = 64
> checkpoint_timeout = 3000
>

These seem fine to me.

Can you include the output of EXPLAIN SELECT both with and without SET
join_collapselimit? Since your tables have grown, I can't compare the
estimated number of rows, and costs very well.

EXPLAIN without ANALYZE is fine, since I am wondering what the planner
is thinking things cost.

John
=:->

>
> #---- FOR PG_AUTOVACUUM --#
> stats_command_string = true
> stats_row_level = true
>

--------------enig003EECB873ACA41B72F2B48F
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC2E+GJdeBCYSNAAMRAmNwAJ4lc3ufVj1sI25w9rG9zKWZzuF2mgCfRJXx
WwfkpkIko3+KDaDEKAIcb50=
=eXdN
-----END PGP SIGNATURE-----

--------------enig003EECB873ACA41B72F2B48F--