Maxim Boguk <maxim.boguk@gmail.com> writes:
> Seems previous test case not clear demonstrate the problem which i have
> stuck with.
> Now much better and close to reality test case:
AFAICT, these behaviors all boil down to the fact that contrib/intarray
doesn't provide a real cost estimator for its && operator. It's using
the "contsel" stub function, which provides a fixed selectivity of
0.001. In your test case, with 1000000 rows in the table, the estimate
for the number of rows satisfying "sections && '{2}'" thus comes out to
exactly 1000. Unfortunately, the true number is around 100000, and it's
that discrepancy that is leading to all of these bad cost estimates.
What I'd like to see done about this is for somebody to adapt the
work Jan Urbanski did on tsvector stats collection and estimation
so that it works for the anyarray operators. It's a bit too late
to imagine that that'll get done for 9.2, but maybe for 9.3.
regards, tom lane