"Brad Might" <> writes:
> Can someone help me break this down and figure out why the one query
> takes so much longer than the other?
It looks to me like there's a correlation between filename and bucket,
such that the indexscan in filename order takes much longer to run
across the first 25 rows with bucket = 3 than it does to run across
the first 25 with bucket = 7 or bucket = 8. It's not just a matter of
there being fewer rows with bucket = 3 ... the cost differential is much
larger than is explained by the count ratios. The bucket = 3 rows have
to be lurking further to the back of the filename order than the others.
> Here's the bucket distribution..i have clustered the index on the bucket
If you have an index on bucket, it's not doing you any good here anyway,
since you wrote the constraint as a crosstype operator ("3" is int4 not
int8). It might help to explicitly cast the constant to int8.
regards, tom lane