Karl Denninger <karl@xxxxxxxxxxxxx> writes: > The problem appearsa to lie in the "nested loop", and I don't understand > why that's happening. It looks to me like there are several issues here. One is the drastic underestimate of the number of rows satisfying the permission condition. That leads the planner to think that a nestloop join with the other table will be fast, which is only right if there are just one or a few rows coming out of "forum". With sixty-some rows you get sixty-some repetitions of the scan of the other table, which loses. Problem number two is the overeager use of a BitmapAnd to add on another index that isn't really very selective. That might be a correct decision but it looks fishy here. We rewrote choose_bitmap_and a couple of times to try to fix that problem ... what PG version is this exactly? The third thing that looks fishy is that it's using unqualified index scans for no apparent reason. Have you got enable_seqscan turned off, and if so what happens when you fix that? What other nondefault planner settings are you using? But anyway, the big problem seems to be poor selectivity estimates for conditions like "(permission & 127) = permission". I have bad news for you: there is simply no way in the world that Postgres is not going to suck at estimating that, because the planner has no knowledge whatsoever of the behavior of "&". You could consider writing and submitting a patch that would teach it something about that, but in the near term it would be a lot easier to reconsider your representation of permissions. You'd be likely to get significantly better results, not to mention have more-readable queries, if you stored them as a group of simple boolean columns. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
