Marc Cousin wrote:
>
> Temporarily I moved the problem at a bit higher sizes of batch by changing
> random_page_cost to 0.02 and seq_page_cost to 0.01, but I feel like an
> apprentice sorcerer with this, as I told postgreSQL that fetching rows from
> disk are much cheaper than they are. These values are, I think, completely
> abnormal.
They certainly don't have anything to do with reality. Try putting them
back to (say) seq_page_cost=1 and random_page_cost=2.
> So, finally, to my questions :
> - Is it normal that PostgreSQL is this off base on these queries (sorry I
> don't have the plans, if they are required I'll do my best to get some, but
> they really are the two obvious plans for this kind of query). What could
> make it choose the hash join for too small batch tables ?
No point in speculating without plans.
> - Is changing the 2 costs the way to go ?
Not the way you have.
> - Is there a way to tell postgreSQL that it's more costly to sort than it
> thinks ? (instead of telling it that fetching data from disk doesn't cost
> anything).
That's what the configuration settings do. But if you put a couple way
off from reality it'll be pure chance if it gets any estimates right.
> Here are the other non-default values from my configuration :
>
> shared_buffers = 2GB
> work_mem = 64MB
Set this *much* higher when you are running your bulk imports. You can
do it per-connection. Try 256MB, 512MB, 1GB (but keep an eye on total
memory used).
> maintenance_work_mem = 256MB
> max_fsm_pages = 15000000 # There are quite big deletes with bacula ...
> effective_cache_size = 800MB
See other emails on this one.
> default_statistics_target = 1000
Probably don't need this for all columns, but it won't cause problems
with these queries.
--
Richard Huxton
Archonet Ltd