index scan leads to result that is different from sec scan after upgrading to 8.3.4 - Mailing list pgsql-general

Hi all,

Well, we have migrated our server from 8.3.3 to 8.3.4. The server is
based on Red Hat and an instans it deals with insalled on RAMFS.

db_online=> select version();
version
----------------------------------------------------------------------------------------
PostgreSQL 8.3.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

There is a table:
                                    Table "public.person_online"
            Column          |           Type           |
Modifiers
----------------------------+--------------------------+---------------------------------
 obj_id                     | bigint                   | not null
 obj_status_did             | smallint                 |
...
 po_since                   | timestamp with time zone | not null default now()
 po_geo_point               | point                    | not null
Indexes:
    "pk_person_online" PRIMARY KEY, btree (obj_id)
    "i_person_online__geo_point" gist (box(po_geo_point,
po_geo_point)) WHERE obj_status_did = 1
    "i_person_online__since" btree (po_since)
Triggers:
    t_person_online_since_bu BEFORE UPDATE ON person_online FOR EACH
ROW EXECUTE PROCEDURE t_person_online_since()

Pay attention to i_person_online__geo_point index.

After migration we did initdb, installed btree_gist contrib (it was
instaled on 8.3.3 too) and created the table and the index. Later we
noticed strange behaviour of our application and that is what we have
managed to find:

db_online=> select obj_status_did, count(1) from person_online
where obj_status_did = 1 group by obj_status_did;
 obj_status_did | count
----------------+-------
              1 |   711
(1 row)

db_online=> select obj_status_did, count(1) from person_online
group by obj_status_did;
 obj_status_did | count
----------------+-------
              5 |     2
              1 |  1930
(2 rows)

explain showed that first query do index scan and second one sec scan.


db_online=>


We did reindex i_person_online__geo_point and situation seemed being solved:

db_online=> select obj_status_did, count(1) from person_online
where obj_status_did = 1 group by obj_status_did;
 obj_status_did | count
----------------+-------
              1 |  2071
(1 row)
db_online=> select obj_status_did, count(1) from person_online
group by obj_status_did;
 obj_status_did | count
----------------+-------
              5 |     2
              1 |  2071
(2 rows)

But after our application had done some inserts/updates/deletes the
queries showed different count of rows with obj_status_did = 1 again.
We tried to drop-create the index but it did not help either.

Then we created a twin-table and a twin-index and did a lot of inserts
and random deletes using different connections. Everything was
alright. We renamed original table to person_online_tmp and the
twin-table to person_online. Few minutes later we saw wrong behaviour
again.

Will somebody explain what has happened and how to solve the problem please?

--
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/