From: John A Meinel <john@arbash-meinel.com>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow joining very large table to smaller ones
Date: Fri, 15 Jul 2005 19:06:30 -0500
Message-ID: <42D84F86.8070100@arbash-meinel.com> (raw)
In-Reply-To: <A6D507C9-A7D9-4FC6-9AE6-7BF3976B51BC@drivefaster.net>
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>
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
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
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: john@arbash-meinel.com, fbsd@drivefaster.net
Subject: Re: slow joining very large table to smaller ones
In-Reply-To: <42D84F86.8070100@arbash-meinel.com>
* 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