From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Artur Zając <azajac@ang.com.pl>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates
Date: Wed, 22 Nov 2017 16:09:54 +0100
Message-ID: <1511363394.2553.68.camel@cybertec.at> (raw)
In-Reply-To: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
References: <03b401d3639e$5d7493a0$185dbae0$@ang.com.pl>
Artur Zając wrote:
> We have table created like this:
>
> CREATE TABLE xyz AS SELECT generate_series(1,10000000,1) AS gs;
>
> Now:
>
> 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)
> Filter: ((gs & 1) = 1)
> Rows Removed by Filter: 5000000
[...]
> And one more clause:
>
> 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)
> Filter: (((gs & 1) = 1) AND ((gs & 2) = 2) AND ((gs & 4) = 4))
> Rows Removed by Filter: 8750000
> As we can see estimates differs significally from the actual records count -
> only three clauses are reducing estimated number of records from 10000000 to
> 2.
>
> I noticed that each additional clause reduces the number about 200 times and
> define DEFAULT_NUM_DISTINCT is responsible for this behaviur.
>
> I think that this variable should be lower or maybe estimation using
> DEFAULT_NUM_DISTTINCT should be done once per table.
The problem is that the expression "gs & 1" is a black box for the
optimizer; it cannot estimate how selective the condition is and falls
back to a default value that is too low.
You can create an index to
a) improve the estimate
and
b) speed up the queries:
CREATE INDEX ON xyz ((gs & 1), (gs & 2), (gs & 4));
Don't forget to ANALYZE afterwards.
Yours,
Laurenz Albe
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: laurenz.albe@cybertec.at, azajac@ang.com.pl, pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates
In-Reply-To: <1511363394.2553.68.camel@cybertec.at>
* 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