On 10/31/2017 11:44 PM, Tomas Vondra wrote:
> ...
> Unfortunately, I think we still have a problem ... I've been wondering
> if we end up producing correct indexes, so I've done a simple test.
>
> 1) create the table as before
>
> 2) let the insert + vacuum run for some time, to see if there are
> crashes (result: no crashes after one hour, inserting ~92M rows)
>
> 3) do a bunch of random updates on the data (while still doing the
> concurrent vacuum in another session)
>
> 4) run a bunch of simple queries to compare the results, essentially
>
> -- BRIN index
> SET enable_bitmapscan = on;
> SELECT COUNT(*) FROM brin_test WHERE a = $1;
>
>
> -- seq scan
> SET enable_bitmapscan = on;
> SELECT COUNT(*) FROM brin_test WHERE a = $1;
>
> and unfortunately what I get is not particularly pleasant:
>
> test=# set enable_bitmapscan = on;
> SET
> test=# select count(*) from brin_test where a = 0;
> count
> -------
> 9062
> (1 row)
>
> test=# set enable_bitmapscan = off;
> SET
> test=# select count(*) from brin_test where a = 0;
> count
> -------
> 9175
> (1 row)
>
> Attached is a SQL script with commands I used. You'll need to copy the
> commands into multiple psql sessions, though, to simulate concurrent
> activity).
>
FWIW I can reproduce this on 9.5, and I don't even need to run the
UPDATE part. That is, INSERT + VACUUM running concurrently is enough to
produce broken BRIN indexes :-(
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
