Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows - Mailing list pgsql-bugs

with help from IRC we've found that decreasing work_mem from 1MB to 256kB
or less makes the problem go away:

data=# show work_mem;
 work_mem
----------
 256kB
(1 row)

data=# SELECT entity_id,attribute_name_ids FROM entity WHERE (
attribute_name_ids && '{7572}' ) AND NOT ( (attribute_name_ids||0) &&
'{7572}') LIMIT 100 ;
 entity_id | attribute_name_ids
-----------+--------------------
(0 rows)

data=# explain analyze SELECT entity_id,attribute_name_ids FROM entity
WHERE ( attribute_name_ids && '{7572}' ) AND NOT (
(attribute_name_ids||0) && '{7572}') LIMIT 100 ;
                                                                     QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=26.53..38.04 rows=100 width=132) (actual
time=110.013..110.015 rows=0 loops=1)
   ->  Bitmap Heap Scan on entity  (cost=26.53..3780.78 rows=32606
width=132) (actual time=110.011..110.011 rows=0 loops=1)
         Recheck Cond: (attribute_name_ids && '{7572}'::integer[])
         Rows Removed by Index Recheck: 102983
         Filter: (NOT ((attribute_name_ids || 0) && '{7572}'::integer[]))
         Rows Removed by Filter: 21501
         Heap Blocks: exact=898 lossy=13752
         ->  Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..26.53 rows=32770 width=0) (actual time=3.582..3.583
rows=21518 loops=1)
               Index Cond: (attribute_name_ids && '{7572}'::integer[])
 Planning Time: 0.173 ms
 Execution Time: 110.220 ms
(11 rows)


problem manifests again with work_mem increased to 512kB or higher:

data=# show work_mem;
 work_mem
----------
 512kB
(1 row)

data=# SELECT entity_id,attribute_name_ids FROM entity WHERE (
attribute_name_ids && '{7572}' ) AND NOT ( (attribute_name_ids||0) &&
'{7572}') LIMIT 100 ;
 entity_id |                                   attribute_name_ids
-----------+----------------------------------------------------------------------------------------
  22327791 | {1,2,3,4,6,8,9,10,11,13,14,17,19,21,35,72,366,1659,2208,2211,3270,3273,3279,5787,7650}
(1 row)

data=# explain analyze SELECT entity_id,attribute_name_ids FROM entity
WHERE ( attribute_name_ids && '{7572}' ) AND NOT (
(attribute_name_ids||0) && '{7572}') LIMIT 100 ;
                                                                     QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=26.73..38.14 rows=100 width=132) (actual
time=112.268..119.475 rows=1 loops=1)
   ->  Bitmap Heap Scan on entity  (cost=26.73..3748.28 rows=32606
width=132) (actual time=112.267..119.473 rows=1 loops=1)
         Recheck Cond: (attribute_name_ids && '{7572}'::integer[])
         Rows Removed by Index Recheck: 68905
         Filter: (NOT ((attribute_name_ids || 0) && '{7572}'::integer[]))
         Rows Removed by Filter: 21501
         Heap Blocks: exact=5630 lossy=9012
         ->  Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..26.73 rows=32770 width=0) (actual time=3.924..3.924
rows=21518 loops=1)
               Index Cond: (attribute_name_ids && '{7572}'::integer[])
 Planning Time: 0.113 ms
 Execution Time: 119.801 ms
(11 rows)


"vacuum table entity;" did not help, neither did
"select gin_clean_pending_list('entity_attribute_name_ids_gin');"