Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > But the other problem is that the planner considers less-than-1% > differences in cost estimates to be "in the noise", which means > that it's not going to consider cost differences of less than > 1480 units in the remaining join steps to be significant. This > is how come we end up with the apparently brain-dead decisions to > use seqscans on some of the other tables such as "pi" and "ac": > comparing the seqscan to a potential inner indexscan, the total > cost of the join is "the same" according to the 1% rule, The 1% rule itself might be something to add to the R&D list. I have seen it cause big problems in production, although the users in that case had made a mistake which significantly contributed to it being an issue. They had used the enable_seqscan = off setting for one query which they had been unable to wrestle into good performance in other ways, but accidentally neglected to turn it back on after that query. Now, seqscans were rarely a good idea with their permanent tables, but they had a couple queries which used very small temporary tables with no indexes. It chose the seqscan in spite of the setting; but, when run with seqscans off, that gave all candidate plans such a high cost that they all looked "equal" and the tie-breaker logic picked a horrible one. (The faster plans did have lower cost, but not by enough to exceed the 1% threshold.) Now, had they not made a questionable choice in disabling seqscan in production, compounded by an error in not turning it off again, they would not have had their main web application slow to unusable levels at times -- but it seems to me that it might be reasonable to have some absolute cost maximum difference test that needs to be met in addition to the percentage difference, as kind of a "safety" on this foot-gun. I'm not sold on this as being a good idea, and had not been planning on raising it without further research; but since it plays into this other scenario it seems worth mentioning as material for potential R&D. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
