Corrupt RTREE index

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Corrupt RTREE index
Date: 2004-12-13 21:23:45
Message-ID: 87zn0hvqn2.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I have what appears to be a corrupt RTREE index.

The first query shows that of the fifteen records I'm looking at, every one of
them has the "@" based condition showing as true. The second shows one record
that really ought to be there not being listed.

I just tried the second query with enable_indexscan = off and the missing
record reappears. So I guess this is a corrupt index.

This is 7.4.6 but the database was initdb'd with an earlier 7.4.

Should I REINDEX or should I keep this around for forensic study?

slo=>
SELECT distinct store_id, geom2 @ box '(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
FROM store_location
WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) <= 60

;
slo=> slo-> slo-> slo-> slo-> store_id | ?column?
----------+----------
504 | t
597 | t
909 | t
2841 | t
2940 | t
2997 | t
3423 | t
3438 | t
3641 | t
3656 | t
4057 | t
4487 | t
4489 | t
4490 | t
4493 | t
(15 rows)

slo=>
SELECT distinct store_id, geom2 @ box '(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
FROM store_location
WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) <= 60
AND geom2 @ box '(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
;
slo=> slo-> slo-> slo-> slo-> store_id | ?column?
----------+----------
504 | t
597 | t
909 | t
2841 | t
2940 | t
2997 | t
3423 | t
3438 | t
3641 | t
3656 | t
4057 | t
4487 | t
4489 | t
4490 | t
(14 rows)

--
greg

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2004-12-13 21:39:25 Re: SELECTing on age
Previous Message Phil Endecott 2004-12-13 21:18:09 Re: Temporary tables and disk activity