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