Row count estimation bug in BETWEEN?

From: Щекин Ярослав <ladayaroslav(at)yandex(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Row count estimation bug in BETWEEN?
Date: 2015-06-13 09:20:22
Message-ID: 6737951434187222@web2o.yandex.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi.

I've noticed strange row count estimations in BETWEEN predicate:
------
-- I'm using this:
SELECT version();
-->> PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 32-bit
-- Prepare table:
CREATE TABLE t1 (
id bigserial NOT NULL,
f0 bigint NOT NULL,
f1 bigint NOT NULL,
CONSTRAINT t1_pkey PRIMARY KEY (id)
);

INSERT INTO t1(f0, f1)
SELECT f0.n, f1.n
FROM generate_series(1,1000) AS f0(n),
generate_series(1,1000) AS f1(n),
generate_series(1,11);
CREATE INDEX ON t1(f0);
CREATE INDEX ON t1(f1);
VACUUM ANALYZE t1;
-- Real count:
SELECT COUNT(*)
FROM t1
WHERE f1 = 42;
-->> 11000
---- Estimation 1:
EXPLAIN
SELECT *
FROM t1
WHERE f1 = 42;
-->> Bitmap Heap Scan on t1 (cost=203.07..28792.94 rows=10662 width=24) <skipped>

EXPLAIN
SELECT *
FROM t1
WHERE f1 BETWEEN 42 AND 42;
-->> Index Scan using table1_field1_idx on t1 (cost=0.44..8.46 rows=1 width=24) <skipped>

------
Why row count estimations for two logically equivalent queries are so different?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message José María Terry Jiménez 2015-06-13 10:32:13 Re: FW: PostgreSQL and iptables
Previous Message Mephysto 2015-06-13 08:58:32 Re: FW: PostgreSQL and iptables