From: | John A Meinel <john(at)arbash-meinel(dot)com> |
---|---|
To: | Dan Harris <fbsd(at)drivefaster(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow joining very large table to smaller ones |
Date: | 2005-07-16 00:06:30 |
Message-ID: | 42D84F86.8070100@arbash-meinel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeffrey W. Baker | 2005-07-16 08:12:27 | more filesystem benchmarks |
Previous Message | Ron Wills | 2005-07-15 22:11:39 | Re: Really bad diskio |