From: | "Erik Rijkers" <er(at)xs4all(dot)nl> |
---|---|
To: | "Alvaro Herrera" <alvherre(at)2ndquadrant(dot)com> |
Cc: | "Pg Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Minmax indexes |
Date: | 2013-09-26 06:54:42 |
Message-ID: | 66917e7897c3a814996b6d6a5cda6d22.squirrel@webmail.xs4all.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Antonin Houska | 2013-09-26 07:11:49 | Re: [PATCH] bitmap indexes |
Previous Message | Noah Misch | 2013-09-26 04:20:18 | Re: pgbench - exclude pthread_create() from connection start timing |