Mike Glover <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> It seems unlikely that the raw row inserts and updating the single >> index could be quite that slow --- perhaps you have a foreign key >> or trigger performance problem?
> There are no foreign keys or triggers for either of the tables. Hmph. It's clear that it is the update overhead that's taking the time (since you show 292 seconds actual time in the update's top plan node --- that's the time to find the rows and compute their new values, and all the rest of the elapsed 3162 sec has to be update overhead). Maybe you just have a slow disk. Just out of curiosity, how much time does the update take if you don't have any index on the summary table? Try create temp table tsummary as select * from summary; vacuum analyze tsummary; explain analyze update tsummary set ... ; >> A quick experiment shows that if the planner does not have any reason >> to prefer one ordering over another, the current coding will put the >> last WHERE clause first: > [snip]> > It looks like the planner is already making a principled choice: After a little bit of experimentation I was reminded that the planner does account for the possibility that a merge join can stop short of full execution when the first mergejoin columns have different data ranges. In this case it's preferring to put price first because there is a greater discrepancy in the ranges of s.price_min and i.price than there is in the ranges of the isbn columns. I'm not sure that it's wrong. You could try increasing the statistics target on the price columns (and re-ANALYZing) to see if finer-grain data changes that estimate at all. In any case, the fact that the chosen plan doesn't make use of your index on isbn doesn't mean that such a plan wasn't considered. It was, but this plan was estimated to be less expensive. You could check out alternative plans and see if the estimate is accurate by fooling with enable_seqscan and enable_sort. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster