Le Tuesday 14 July 2009 10:23:25, Richard Huxton a écrit : > 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.
That's the first thing I tried (it seemed more sensible), and it didn't work. I can't put them back to these values for more than one test query, the server really died before I changed the settings. > > > 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. Ok, I'll try to have them tomorrow. > > > - Is changing the 2 costs the way to go ? > > Not the way you have. That's what I thought, and the reason I posted :) > > > - 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). I'll try that. But anyhow, I've got much better performance when not doing the hash join. I'll get back with the plans as soon as possible. > > > 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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
