From: Tom Lane <tgl@sss.pgh.pa.us>
To: Artur Zając <azajac@ang.com.pl>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates
Date: Wed, 22 Nov 2017 10:01:57 -0500
Message-ID: <12728.1511362917@sss.pgh.pa.us> (raw)
In-Reply-To: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
References: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
=?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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, azajac@ang.com.pl, pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates
In-Reply-To: <12728.1511362917@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox