Ben <midfield@xxxxxxxxx> wrote: > it seems to me that an equality join between two relations (call them A and B) > using columns in relation B with a unique constraint should yield row estimates > which are at most equal to the row estimates for relation A. my questions are > > 1 - is this correct? > > 2 - does the postgresql planner implement this when generating row estimates? That seems intuitive, but some of the estimates need to be made before all such information is available. Maybe we can do something about that some day.... > while i do have a performance > issue (i'd like for it to select > the index scan) which might be solved by better configuration, > that at the moment is a secondary question -- right now i'm > interested in why the row estimates are off. Maybe someone else will jump in here with more details than I can provide (at least without hours digging in the source code). > On Jun 25, 2013, at 6:20 AM, Kevin Grittner wrote: >> Ben <midfield@xxxxxxxxx> wrote: >> >>> PostgreSQL 9.1.1 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) >>> 4.6.2, 64-bit >> >> Consider applying the latest bug fixes for 9.1 -- which would leave >> you showing 9.1.9. > > i will bring it up with our ops people. do you have any particular fixes in > mind, or is this a (very sensible) blanket suggestion? I do recommend staying up-to-date in general (subject to roll-out procedures), We try very hard not to change any behavior that isn't a clear bug from one minor release to the next, precisely so that people can apply these critical bug fixes with confidence that things won't break. There is a fix for a pretty significant security vulnerability you are currently missing, which would be my top concern; but it wouldn't be surprising if there were a planner bug in 9.1.1 which is fixed in 9.1.9. >> Do you get a different plan if you set cpu_tuple_cost = 0.03? How >> about 0.05? You can set this just for a single connection and run >> explain on the query to do a quick check. > > setting cpu_tuple_cost to 0.03 or 0.05 has no effect on the choice of plan or > the row estimates for the un-limited query or the limited query. That wouldn't affect row estimates, but it would tend to encourage index usage, because it would increase the estimated cost of reading each row. -- Kevin Grittner EnterpriseDB: 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
