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?
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 |