On Thu, September 26, 2013 00:34, Erik Rijkers wrote: > On Wed, September 25, 2013 22:34, Alvaro Herrera wrote: > >> [minmax-5.patch] > > I have the impression it's not quite working correctly. > > The attached program returns different results for different values of > enable_bitmapscan (consistently). > > ( Btw, I had to make the max_locks_per_transaction higher for even > not-so-large tables -- is that expected? For a 100M row > table, max_locks_per_transaction=1024 was not enough; I set it to 2048. > Might be worth some documentation, eventually. ) > > From eyeballing the results it looks like the minmax result (i.e. the result > set with enable_bitmapscan = 1) yields only > the last part because the only 'last' rows seem to be present (see the values > in column i in table tmm in the attached > program).
Looking back at that, I realize I should have added a bit more detail on that test.sh program and its output (attached on previous mail). test.sh creates a table tmm and a minmax index on that table: testdb=# \d tmm Table "public.tmm" Column | Type | Modifiers --------+---------+----------- i | integer | r | integer | Indexes: "tmm_minmax_idx" minmax (r) The following shows the problem: the same search with minax index on versus off gives different result sets: testdb=# set enable_bitmapscan=0; select count(*) from tmm where r between symmetric 19494484 and 145288238; SET Time: 0.473 ms count ------- 1261 (1 row) Time: 7.764 ms testdb=# set enable_bitmapscan=1; select count(*) from tmm where r between symmetric 19494484 and 145288238; SET Time: 0.471 ms count ------- 3 (1 row) Time: 1.014 ms testdb=# set enable_bitmapscan =1; select * from tmm where r between symmetric 19494484 and 145288238; SET Time: 0.615 ms i | r ------+----------- 9945 | 45405603 9951 | 102552485 9966 | 63763962 (3 rows) Time: 0.984 ms testdb=# set enable_bitmapscan=0; select * from ( select * from tmm where r between symmetric 19494484 and 145288238 order by i desc limit 10) f order by i ; SET Time: 0.470 ms i | r ------+----------- 9852 | 114996906 9858 | 69907169 9875 | 43341583 9894 | 127862657 9895 | 44740033 9911 | 51797553 9916 | 58538774 9945 | 45405603 9951 | 102552485 9966 | 63763962 (10 rows) Time: 8.704 ms testdb=# If enable_bitmapscan=1 (i.e. using the minmax index), then only some values are retrieved (in this case 3 rows). It turns out those are always the last N rows of the full resultset (i.e. with enable_bitmapscan=0). Erikjan Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers