Re: Select time jump after adding filter; please help me figure out what I'm doing wrong.

Yes. The only difference between the two selects was that the index on the table in question was dropped. As far as I know, that was the only partial index on there, although since it's a test db, I could probably go in and experiment on a few more if needed.

This problem may have already been solved; I'm using an older version of Postgres; 8.1.3. My boss has requested that it not be upgraded just yet, however, so I'm stuck with it for the moment.

Richard Huxton <dev(at)archonet(dot)com> wrote:
Andrew Edson wrote:
> I apologize about the CC; I thought I had done so.

no problem

> There are fourteen (14) distinct values in rcrd_cd. And I don't know
> if this counts as something odd, but I got the following values by
> doing a vacuum full analyze, then running the set with index,
> dropping index, running set without.

Might want to do ALTER TABLE ... ALTER COLUMN rcrd_cd SET STATISTICS =
14 (or a few more than that if you think it might be useful) - won't
help you with this though.

So - are you saying that with these two queries...

>> attest=# select count(*) from ptrans where rcrd_cd = '0A';
>> 6701655

>> attest=# select count(*) from ptrans where rcrd_cd = '0A';
>> 204855

...the only difference is that you've dropped an index?

Because that's just strange - and I don't think it's anything you're doing.

Do you have other partial indexes for different values of rcrd_cd, and
do they have similar problems? If this can be reproduced it might point
to something odd with bitmap scans.

Oh, remind me what version of PostgreSQL you're running?

--
Richard Huxton
Archonet Ltd

---------------------------------
Moody friends. Drama queens. Your life? Nope! - their life, your story.
Play Sims Stories at Yahoo! Games.