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

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