On Mon, November 11, 2013 09:53, Erik Rijkers wrote: > On Fri, November 8, 2013 21:11, Alvaro Herrera wrote: >> >> Here's a version 7 of the patch, which fixes these bugs and adds >> opclasses for a bunch more types (timestamp, timestamptz, date, time, >> timetz), courtesy of Martín Marqués. It's also been rebased to apply >> cleanly on top of today's master branch. >> >> I have also added a selectivity function, but I'm not positive that it's >> very useful yet. >> >> [minmax-7.patch] > > The earlier errors are indeed fixed; now, I've been trying with the attached > test case but I'm unable to find a query that > improves with minmax index use. (it gets used sometimes but speedup is > negligable). >
Another issue (I think):
Attached is a program (and output as a .txt file) that gives the following
(repeatable) error:
$ ./casanova_test.sh
\timing on
drop table if exists t1;
Time: 333.159 ms
create table t1 (i int);
Time: 155.827 ms
create index t1_i_idx on t1 using minmax(i);
Time: 204.031 ms
insert into t1 select generate_series(1, 25000000);
Time: 126312.302 ms
analyze t1;
ERROR: could not truncate file base/21324/26339_vm to 41 blocks: it's only 1
blocks now
Time: 472.504 ms
[...]
Thanks,
Erik Rijkers
casanova_test.sh
Description: application/shellscript
$ ./casanova_test.sh
\timing on
drop table if exists t1;
Time: 333.159 ms
create table t1 (i int);
Time: 155.827 ms
create index t1_i_idx on t1 using minmax(i);
Time: 204.031 ms
insert into t1 select generate_series(1, 25000000);
Time: 126312.302 ms
analyze t1;
ERROR: could not truncate file base/21324/26339_vm to 41 blocks: it's only 1
blocks now
Time: 472.504 ms
\timing on
set enable_bitmapscan=1; explain analyze select i from t1 where i between
10000000 and 10001000;
Time: 0.508 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=32.25..117786.77 rows=125001 width=4) (actual
time=1640.520..4465.768 rows=1001 loops=1)
Recheck Cond: ((i >= 10000000) AND (i <= 10001000))
Rows Removed by Index Recheck: 24998999
-> Bitmap Index Scan on t1_i_idx (cost=0.00..1.00 rows=125001 width=0)
(actual time=65.322..65.322 rows=291 loops=1)
Index Cond: ((i >= 10000000) AND (i <= 10001000))
Total runtime: 4466.003 ms
(6 rows)
Time: 4468.943 ms
set enable_bitmapscan=0; explain analyze select i from t1 where i between
10000000 and 10001000;
Time: 0.146 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..485621.80 rows=125001 width=4) (actual
time=1158.065..3332.925 rows=1001 loops=1)
Filter: ((i >= 10000000) AND (i <= 10001000))
Rows Removed by Filter: 24998999
Total runtime: 3333.038 ms
(4 rows)-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
