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?
