From: Justin Pryzby <pryzby@telsasoft.com>
To: Artur Zając <azajac@ang.com.pl>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates (DEFAULT_UNK_SEL)
Date: Wed, 22 Nov 2017 08:57:13 -0600
Message-ID: <20171122145713.GU5668@telsasoft.com> (raw)
In-Reply-To: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
References: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
On Wed, Nov 22, 2017 at 03:29:54PM +0100, Artur Zając wrote:
> CREATE TABLE xyz AS SELECT generate_series(1,10000000,1) AS gs;
>
> db=# explain analyze select * from xyz where gs&1=1;
> Seq Scan on xyz (cost=0.00..260815.38 rows=68920 width=4) (actual time=0.044..2959.728 rows=5000000 loops=1)
...
> newrr=# explain analyze select * from xyz where gs&1=1 and gs&2=2 and gs&4=4;
> Seq Scan on xyz (cost=0.00..398655.62 rows=2 width=4) (actual time=0.052..3329.422 rows=1250000 loops=1)
> I noticed that each additional clause reduces the number about 200 times and
> define DEFAULT_NUM_DISTINCT is responsible for this behaviur.
I think it's actually:
src/include/utils/selfuncs.h-/* default selectivity estimate for boolean and null test nodes */
src/include/utils/selfuncs.h-#define DEFAULT_UNK_SEL 0.005
..which is 1/200.
Note, you can do this, which helps a bit by collecting stats for the index
expr:
postgres=# CREATE INDEX ON xyz((gs&1));
postgres=# ANALYZE xyz;
postgres=# explain analyze SELECT * FROM xyz WHERE gs&1=1 AND gs&2=2 AND gs&4=4;
Bitmap Heap Scan on xyz (cost=91643.59..259941.99 rows=124 width=4) (actual time=472.376..2294.035 rows=1250000 loops=1)
Recheck Cond: ((gs & 1) = 1)
Filter: (((gs & 2) = 2) AND ((gs & 4) = 4))
Rows Removed by Filter: 3750000
Heap Blocks: exact=44248
-> Bitmap Index Scan on xyz_expr_idx (cost=0.00..91643.55 rows=4962016 width=0) (actual time=463.477..463.477 rows=5000000 loops=1)
Index Cond: ((gs & 1) = 1)
Justin
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: pryzby@telsasoft.com, azajac@ang.com.pl, pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates (DEFAULT_UNK_SEL)
In-Reply-To: <20171122145713.GU5668@telsasoft.com>
* 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