--=-pBmDeMjfseQcffS2N+aW
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

I could not duplicate this on any identical database, but thought I
would report it here, just for completeness. I've tried reindexing,
vacuuming, etc. Explain plans all look normal.

Version is 8.1.3
\d bigtable
....
"bigtable_status_partial" btree (status) WHERE status::text <>
'shipped'::text
=20
=20
greg=3D> select (select count(*) from bigtable where status =3D 'shipped')
+ (select count(*) from bigtable where status <> 'shipped')
UNION select count(*) from bigtable;
?column?
----------
1185213
1600569
=20
greg=3D> drop index bigtable_status_partial;
=20
greg=3D> select (select count(*) from bigtable where status =3D 'shipped')
+ (select count(*) from bigtable where status <> 'shipped')
UNION select count(*) from bigtable;
?column?
----------
1185213
(1 row)

--
Greg Sabino Mullane greg (AT) turnstep (DOT) com
End Point Corporation
PGP Key: 0x14964AC8 200608290003
http://biglumber.com/x/web?pk=3D2529...9B906714964AC8



--=-pBmDeMjfseQcffS2N+aW
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----

iD8DBQBE870QvJuQZxSWSsgRAsDFAJ40yxKNRDQ7X7oeElUoK0 IRBwyU7gCg9vah
/MVtG+DjHbs6ifCkwFrU1Uc=
=p8Ry
-----END PGP SIGNATURE-----

--=-pBmDeMjfseQcffS2N+aW--

Greg Sabino Mullane <greg (AT) turnstep (DOT) com> writes:
Possibly explained by this 8.1.4 fix?

2006-05-18 14:57 tgl

* src/backend/optimizer/plan/: createplan.c (REL8_1_STABLE),
createplan.c: When a bitmap indexscan is using a partial index, it
is necessary to include the partial index predicate in the scan's
"recheck condition". Otherwise, if the scan becomes lossy for lack
of bitmap memory, we would fail to enforce that returned rows
satisfy the predicate. Noted while studying bug #2441 from Arjen
van der Meijden.

If it gives right answers after you increase work_mem sufficiently,
I'd bet on this one ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


That's a good bet. Changing it from 1024 to 3500 did the trick ... only
one row returned, even when using the index. Thanks very much!

- --
Greg Sabino Mullane greg (AT) turnstep (DOT) com
End Point Corporation
PGP Key: 0x14964AC8 200608290100
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8


-----BEGIN PGP SIGNATURE-----

iD8DBQFE88nuvJuQZxSWSsgRAkA8AJ9xKgOcEmK4swC0Dji4Wt Es4p+juwCeIHJN
xyNAvlAT5c2O6pWI0C2G03I=
=EvGR
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly