Re: [SQL] SQL Query Performance - what gives?

Karl Denninger <karl(at)denninger(dot)net> 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