From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates
In-reply-to: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
References: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
Comments: In-reply-to =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
	message dated "Wed, 22 Nov 2017 15:29:54 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <12727.1511362917.1@sss.pgh.pa.us>
Date: Wed, 22 Nov 2017 10:01:57 -0500
Message-ID: <12728.1511362917@sss.pgh.pa.us>
Precedence: bulk

=?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl> writes:
[ poor estimates for WHERE clauses like "(gs & 1) = 1" ]

Don't hold your breath waiting for that to get better on its own.
You need to work with the planner, not expect it to perform magic.
It has no stats that would help it discover what the behavior of
that sort of WHERE clause is; nor is there a good reason for it
to think that the selectivity of such a clause is only 0.5 rather
than something more in line with the usual behavior of an equality
constraint on an integer value.

One way you could attack the problem, if you're wedded to this data
representation, is to create expression indexes on the terms "(gs & x)"
for all the values of x you use.  Not only would that result in better
estimates (after an ANALYZE) but it would also open the door to satisfying
this type of query through an index search.  A downside is that updating
all those indexes could make DML on the table pretty expensive.

If you're not wedded to this data representation, consider replacing that
integer flags column with a bunch of boolean columns.  You might or might
not want indexes on the booleans, but in any case ANALYZE would create
stats that would allow decent estimates for "WHERE boolval".

			regards, tom lane